Список excel формул: Все формулы эксель
Содержание
Связанные выпадающие списки и формула массива в Excel
Итак, как сделать два связанных списка в Excel: категория, подкатегория и категория более нижнего уровня. Своими словами в данном случае нижний уровень — это «подподкатегория» если она вообще существует… Но для лучшего понимания данного обучающего материала, предположим, что существует.
Два связанных выпадающих списка с формулой массива
В любом случае, с самого начала напишем, что этот учебный материал является продолжением материала: Как сделать зависимые выпадающие списки в ячейках Excel, в котором подробно описали логику и способ создания одного из таких списков. Рекомендуем вам ознакомиться с ним, потому что здесь подробно описывается только то, как сделать тот другой связанный выпадающий список 🙂 А это то, что мы хотим получить:
Итак, мы имеем:
- тип автомобиля: Легковой, Фургон и Внедорожник (Категория)
- производитель: Fiat, Volkswagen i Suzuki (Подкатегория) и
- модель: .
.. немножечко их есть 🙂 (Подподкатегория)
.. немножечко их есть 🙂 (Подподкатегория)В то же время мы имеем следующие данные:
Этот список должен быть отсортирован в следующей очередности:
- Тип.
- Производитель.
- Модель.
Он может быть любой длины. Что еще важно: стоит добавить к нему еще два меньших списка, необходимых для Типа и Производителя, то есть к категории (первый список) и подкатегории (второй список). Эти дополнительные списки списки выглядят следующим образом:
Дело в том, что эти списки не должны иметь дубликатов записей по Типу и Производителю, находящихся в списке Моделей. Вы можете создать их с помощью инструмента «Удалить дубликаты» (например, это показано в этом видео продолжительностью около 2 минут). Когда мы это сделали, тогда …
Первый и второй связанный выпадающий список: Тип и Производитель
Для ячеек, которые должны стать раскрывающимися списками в меню «Данные» выбираем «Проверка данных» и как тип данных выбираем «Список».
Для Типа как источник данных мы просто указываем диапазон B7:B9.
Для Производителя мы уже используем формулу, которая подробно описана здесь. Она выглядит так:
Модель — описание для этой записи сделаем таким же самым образом.
Третий связывающий выпадающий список: Модель
Теперь рассмотрим, как связать выпадающий список в Excel. Поскольку Модель зависит как от Типа, так и от Производителя — значит мы будем использовать сложную формулу. После поместим ее не в проверку данных, а в именной диапазон. Соответственно проверка данных будет содержать ссылку на это имя. Предположим, мы хотим отобразить в нем легковые модели Fiat. В первом списке мы выбрали Легковой, во втором — Fiat.
Мы будем перемещать ячейку h5 на столько строк, пока не найдем позицию первого легкового Fiatа. Поэтому в колонке Тип мы должны иметь значение Легковой, а в колонке Производитель должен быть Fiat. Если бы мы использовали промежуточный столбец (это было бы отличным решением, но хотели бы показать вам что-то более крутое ;-), то мы бы искали комбинацию этих данных: Легковой Fiat.
Однако у нас нет такого столбца, но мы можем создать его «на лету», другими словами, используя формулу. Набирая эту формулу, вы можете себе представить, что такой промежуточный столбец существует, и вы увидите, что будет проще 😉
Для определения положения Легковой Fiat, мы, конечно, будем использовать функцию ПОИСКПОЗ. Смотрите:
ПОИСКПОЗ(B4&C4;F5:F39&G5:G39;0)
Вышеописанное означает, что мы хотим знать позицию Легкового Fiatа (отсюда и связь B4&C4). Где? В нашем воображаемом вспомогательном столбце, то есть: F5:F39&G5:G39. И здесь самая большая сложность всей формулы.
Остальное уже проще, а наибольшего внимания требует функция СЧЁТЕСЛИМН, которая проверяет, сколько есть Легковых Fiatов. В частности, она проверяет, сколько раз в списке встречаются такие записи, которые в столбце F5:F39 имеют значение Легковой, а в столбце G5:G39 — Fiat. Функция выглядит так:
СЧЁТЕСЛИМН(F5:F39;B4;G5:G39;C4)
А вся формула для именного диапазона раскрывающегося списка это:
Если вы планируете использовать эту формулу в нескольких ячейках — не забудьте обозначить ячейки как абсолютные ссылки!
Теперь чтобы правильно использовать данную формулу по назначению в третьем выпадающем списке нам необходимо выполнить рад последовательных действий:
- Создаем новое имя. Для этого выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Диспетчер имен»-«Создать».
- При создании имени в поле «Имя:» вводим слово – модель, а в поле «Диапазон:» вводим выше указанную формулу и нажимаем на всех открытых диалоговых окнах ОК:
- Перейдите на ячейку D4 чтобы там создать выпадающий список, в котором на этот раз в поле ввода «Источник:» следует указать ссылку на выше созданное имя с формулой =модель.
Для этого выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Диспетчер имен»-«Создать».Скачать зависимые выпадающие списки в Excel
Когда вы перейдете в меню «Данные», «Проверка данных» и выберите как Тип данных «список», а в поле «Источник» вставьте не саму формулу, а ссылку на имя «=модель» именного диапазона с этой формулой. Такой подход обеспечит стабильность работы третьего выпадающего списка.
Поиск отличий в двух списках
9779
06.11.2012
Скачать пример
Типовая задача, возникающая периодически перед каждым пользователем Excel — сравнить между собой два диапазона с данными и найти различия между ними.
Способ решения, в данном случае, определяется типом исходных данных.
Вариант 1. Синхронные списки
Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант — используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE):
Число несовпадений можно посчитать формулой:
=СУММПРОИЗВ(—(A2:A20<>B2:B20))
или в английском варианте =SUMPRODUCT(—(A2:A20<>B2:B20))
Если в результате получаем ноль — списки идентичны. В противном случае — в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.
Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) — Отличия по строкам (Row differences).
В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) — Выделение группы ячеек (Go to Special) на вкладке Главная (Home)
Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:
- залить цветом или как-то еще визуально отформатировать
- очистить клавишей Delete
- заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
- удалить все строки с выделенными ячейками, используя команду Главная — Удалить — Удалить строки с листа (Home — Delete — Delete Rows)
- и т.д.
Вариант 2. Перемешанные списки
Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.
Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование.
Выделите оба диапазона с данными и выберите на вкладке Главная — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения (Home — Conditional formatting — Highlight cell rules — Duplicate Values):
Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные — различия.
Цветовое выделение, однако, не всегда удобно, особенно для больших таблиц. Также, если внутри самих списков элементы могут повторяться, то этот способ не подойдет.
В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:
Полученный в результате ноль и говорит об отличиях.
И, наконец, «высший пилотаж» — можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:
Выглядит страшновато, но свою работу выполняет отлично 😉
Ссылки по теме
- Выделение дубликатов в списке цветом
- Сравнение двух диапазонов с помощью надстройки PLEX
- Запрет ввода повторяющихся значений
| Новые функции | |||
| XLOOKUP | Заменяет VLOOKUP, HLOOKUP и INDEX / MATCH | XLOOKUP(lookup_value,lookup_array,return_array,[match_mode],[search_mode]) | |
| Логический | да | ||
| И | Проверяет выполнение всех условий. ИСТИНА/ЛОЖЬ | И(лог1,лог2) | |
| IF | Если условие выполнено, сделайте что-нибудь, если нет, сделайте что-нибудь другое. | ЕСЛИ(логическая_проверка,значение_если_истина,значение_если_ложь) | |
| ЕСЛИОШИБКА | Если результатом является ошибка, сделайте что-нибудь другое. | ЕСЛИОШИБКА(ЗНАЧЕНИЕ,значение_если_ошибка) | |
| НЕ | Изменяет ИСТИНА на ЛОЖЬ и ЛОЖЬ на ИСТИНА. | НЕ(логическое) | |
| ИЛИ | Проверяет, выполняются ли какие-либо условия. ИСТИНА/ЛОЖЬ | ИЛИ(лог1,лог2) | |
| XOR | Проверяет, выполняется ли одно и только одно условие. ИСТИНА/ЛОЖЬ | Исключающее ИЛИ(лог1,лог2) | |
| Поиск и ссылка | да | ||
| ЛОЖЬ | Логическое значение: ЛОЖЬ. | ЛОЖЬ | |
| ИСТИНА | Логическое значение: ИСТИНА. | ИСТИНА | |
| АДРЕС | Возвращает адрес ячейки в виде текста. | АДРЕС(номер_строки,номер_столбца,номер_абс,C1,текст_листа) | |
| ОБЛАСТИ | Возвращает количество областей в ссылке. | ОБЛАСТИ (ссылка) | |
| ВЫБОР | Выбирает значение из списка на основе его номера позиции. | ВЫБОР(номер_индекса,значение1,значение2) | |
| КОЛОННА | Возвращает номер столбца ссылки на ячейку. | КОЛОННА (ссылка) | |
| СТОЛБЦЫ | Возвращает количество столбцов в массиве. | СТОЛБЦЫ (массив) | |
| HLOOKUP | Поиск значения в первой строке и возврат значения. | HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) | |
| ГИПЕРССЫЛКА | Создает интерактивную ссылку. | ГИПЕРССЫЛКА(link_location,friendly_name) | |
| ИНДЕКС | Возвращает значение на основе номеров столбца и строки. | ИНДЕКС(массив,номер_строки,номер_столбца) | |
| НЕПРЯМАЯ | Создает ссылку на ячейку из текста. | НЕПРЯМАЯ(ref_text,C1) | |
| ПРОСМОТР | Поиск значений по горизонтали или по вертикали. | ПРОСМОТР(искомое_значение,искомый_вектор,результирующий_вектор) | |
| ПОИСКПОЗ | Ищет значение в списке и возвращает его позицию. | ПОИСКПОЗ (искомое_значение, искомый_массив, тип_соответствия) | |
| СМЕЩЕНИЕ | Создает смещение привязки от начальной точки. | СМЕЩЕНИЕ(ссылка,строки,столбцы,высота,ширина) | |
| ROW | Возвращает номер строки ссылки на ячейку. | РЯД (ссылка) | |
| ROWS | Возвращает количество строк в массиве. | РЯДЫ (массив) | |
| ТРАНСП | Меняет ориентацию диапазона ячеек. | ТРАНСП(массив) | |
| ВПР | Поиск значения в первом столбце и возврат значения. | ВПР(искомое_значение,массив_таблиц,номер_индекса_столбца,диапазон_просмотра) | |
| Дата и время | да | ||
| ДАТА | Возвращает дату из года, месяца и дня. | ДАТА(год,месяц,день) | |
| DATEDIF | Количество дней, месяцев или лет между двумя датами. | РАЗНДАТ | |
| ДАТАЗНАЧ | Преобразует дату, сохраненную в виде текста, в действительную дату | ДАТАЗНАЧ(дата_текст) | |
| ДЕНЬ | Возвращает день в виде числа (1-31). | ДЕНЬ(серийный_номер) | |
| ДНЕЙ | Возвращает количество дней между двумя датами. | ДНЕЙ(дата_конца,дата_начала) | |
| DAYS360 | Возвращает количество дней между двумя датами в году из 360 дней. | ДНЕЙ360(дата_начала,дата_окончания,метод) | |
| EDATE | Возвращает дату, отстоящую от начальной даты на n месяцев. | ЭДАТА(дата_начала,месяцы) | |
| КОНМЕСЯЦ | Возвращает последний день месяца, n месяцев до даты. | КОНМЕСЯЦА(дата_начала,месяцы) | |
| ЧАС | Возвращает час в виде числа (0-23). | ЧАС(серийный_номер) | |
| МИНУТЫ | Возвращает минуты в виде числа (0-59). | МИНУТА(серийный_номер) | |
| МЕСЯЦ | Возвращает месяц в виде числа (1-12). | МЕСЯЦ(серийный_номер) | |
| ЧИСТРАБДНИ | Количество рабочих дней между двумя датами. | ЧИСТРАБДНИ(дата_начала,дата_окончания,праздники) | |
| NETWORKDAYS.INTL | Рабочие дни между двумя датами, настраиваемые выходные. | ЧИСТРАБДНИ.МЕЖД(дата_начала,дата_окончания,выходные,праздники) | |
| СЕЙЧАС | Возвращает текущую дату и время. | СЕЙЧАС() | |
| ВТОРАЯ | Возвращает секунды в виде числа (0-59) | SECOND(serial_number) | |
| ВРЕМЯ | Возвращает время из часов, минут и секунд. | ВРЕМЯ(час,минута,секунда) | |
| TIMEVALUE | Преобразует время, сохраненное в виде текста, в действительное время. | ВРЕМЗНАЧ(время_текст) | |
| СЕГОДНЯ | Возвращает текущую дату. | СЕГОДНЯ() | |
| ДЕНЬ НЕД | Возвращает день недели в виде числа (1-7). | ДЕНЬ НЕДЕЛИ(serial_number,return_type) | |
| WEEKNUM | Возвращает номер недели в году (1-52). | НОМЕР НЕДЕЛИ (серийный_номер, тип_возврата) | |
| РАБДЕНЬ | Дата n рабочих дней от даты. | РАБДЕНЬ(дата_начала,дни,праздники) | |
| WORKDAY.INTL | Дата n рабочих дней от даты, пользовательские выходные. | РАБДЕНЬ.МЕЖД(дата_начала,дни,выходные,праздники) | |
| ГОД | Возвращает год. | ГОД(серийный_номер) | |
| YEARFRAC | Возвращает долю года между двумя датами. | YEARFRAC(start_date,end_date,basic) | |
| Машиностроение | да | ||
| ПРЕОБРАЗОВАТЬ | Преобразование числа из одной единицы в другую. | ПРЕОБРАЗОВАТЬ(число,из_единицы,в_единицу) | |
| Финансовый | да | ||
| FV | Расчет будущей стоимости. | БС(коэффициент,кпер,тыс.т,пс,тип) | |
| PV | Вычисляет текущую стоимость. | PV(ставка,кпер,тысяча на тонну,б.с.,тип) | |
| КПЕР | Вычисляет общее количество периодов платежей. | КПЕР(ставка,pmt,pv,fv,тип) | |
| PMT | Расчет суммы платежа. | PMT(коэффициент,кпер,pv,fv,тип) | |
| СТАВКА | Расчет процентной ставки. | СТАВКА(nper,pmt,pv,fv,тип,предположение) | |
| NPV | Расчет чистой приведенной стоимости. | NPV(ставка,значение1,значение2) | |
| IRR | Внутренняя норма доходности для набора периодических CF. | IRR(значения,предположения) | |
| XIRR | Внутренняя норма доходности для набора непериодических КФ. | ЧИСТНДОХД(значения,даты,предположение) | |
| ЦЕНА | Рассчитывает цену облигации. | ЦЕНА(расчет,срок,ставка,лет,погашение,ЧАСТОТА,база) | |
| ДОХОД | Рассчитывает доходность облигации. | ДОХОД(расчет,срок,ставка,процент,погашение,ЧАСТОТА,база) | |
| INTRATE | Процентная ставка полностью инвестированной ценной бумаги. | ВНУТРЕННЯЯ (расчет, срок погашения, инвестиции, погашение, база) | |
| Информация | да | ||
| CELL | Возвращает информацию о ячейке. | ЯЧЕЙКА (тип_информации, ссылка) | |
ERROR. TYPE | Возвращает значение, представляющее ошибку ячейки. | ТИП ОШИБКИ(значение_ошибки) | |
| IПУСТО | Проверить, пуста ли ячейка. ИСТИНА/ЛОЖЬ | ЕПУСТО (ЗНАЧЕНИЕ) | |
| ISERR | Проверяет, является ли значение ячейки ошибкой, игнорирует #N/A. ИСТИНА/ЛОЖЬ | ЕОШИБКА(ЗНАЧЕНИЕ) | |
| ЕОШИБКА | Проверить, является ли значение ячейки ошибкой. ИСТИНА/ЛОЖЬ | ОШИБКА(ЗНАЧЕНИЕ) | |
| ISEVEN | Проверка четности значения ячейки. ИСТИНА/ЛОЖЬ | ISEVEN(число) | |
| ISFORMULA | Проверить, является ли ячейка формулой. ИСТИНА/ЛОЖЬ | ИФОРМУЛА (ссылка) | |
| НЕЛОГИЧНОСТЬ | Проверка логичности ячейки (ИСТИНА или ЛОЖЬ). ИСТИНА/ЛОЖЬ | НЕЛОГИЧНОЕ(ЗНАЧЕНИЕ) | |
| ISNA | Проверить, является ли значение ячейки #N/A. ИСТИНА/ЛОЖЬ | ISNA(ЗНАЧЕНИЕ) | |
| ISNONTEXT | Проверить, не является ли ячейка текстом (пустые ячейки не являются текстом). ИСТИНА/ЛОЖЬ | НЕТЕКСТ(ЗНАЧЕНИЕ) | |
| ISNUMBER | Проверить, является ли ячейка числом. ИСТИНА/ЛОЖЬ | IНОМЕР(ЗНАЧЕНИЕ) | |
| ISODD | Проверить, является ли значение ячейки нечетным. ИСТИНА/ЛОЖЬ | ISODD(число) | |
| ISREF | Проверить, является ли значение ячейки ссылкой. ИСТИНА/ЛОЖЬ | ISREF(ЗНАЧЕНИЕ) | |
| ISTEXT | Проверить, является ли ячейка текстовой. ИСТИНА/ЛОЖЬ | ИСТЕКСТ(ЗНАЧ) | |
| N | Преобразует значение в число. | N(ЗНАЧЕНИЕ) | |
| NA | Возвращает ошибку: #N/A. | нет данных() | |
| TYPE | Возвращает тип значения в ячейке. | ТИП(ЗНАЧЕНИЕ) | |
| Математика | да | ||
| ABS | Вычисляет абсолютное значение числа. | АБС(номер) | |
| АГРЕГАТ | Определение и выполнение вычислений для базы данных или списка. | АГРЕГАТ(номер_функции,параметры,массив,k) | |
| CEILING | Округляет число до ближайшего указанного кратного. | ПОТОЛОК(номер,значение) | |
| COS | Возвращает косинус угла. | COS(номер) | |
| ГРАДУСЫ | Преобразует радианы в градусы. | ГРАДУСЫ(угол) | |
| DSUM | Суммирует записи базы данных, соответствующие определенным критериям. | DSUM(база данных,поле,критерий) | |
| ЧЕТНЫЙ | Округляет до ближайшего четного целого числа. | ЧЕТНЫЙ(число) | |
| EXP | Вычисляет экспоненциальное значение для заданного числа. | EXP(число) | |
| ФАКТ | Возвращает факториал. | ФАКТ(номер) | |
| ЭТАЖ | Округляет число в меньшую сторону до ближайшего указанного кратного. | ЭТАЖ(номер,значение) | |
| НОД | Возвращает наибольший общий делитель. | НОД(число1,число2) | |
| INT | Округляет число в меньшую сторону до ближайшего целого числа. | INT(число) | |
| LCM | Возвращает наименьшее общее кратное. | LCM(номер1,номер2) | |
| LN | Возвращает натуральный логарифм числа. | ЛН(номер) | |
| LOG | Возвращает логарифм числа по указанному основанию. | LOG(номер,база) | |
| LOG10 | Возвращает логарифм числа по основанию 10. | LOG10(число) | |
| MOD | Возвращает остаток после деления. | MOD(число,делитель) | |
| MROUND | Округляет число до указанного кратного. | КРУГЛЫЙ(число,несколько) | |
| НЕЧЕТНОЕ | Округляет до ближайшего нечетного целого числа. | НЕЧЕТНЫЙ (число) | |
| PI | Значение PI. | ПИ() | |
| POWER | Вычисляет число, возведенное в степень. | МОЩНОСТЬ(число,мощность) | |
| ПРОИЗВЕД | Умножает массив чисел. | ПРОДУКТ(номер1,номер2) | |
| ЧАСТНОЕ | Возвращает целочисленный результат деления. | ЧАСТНОЕ(числитель,знаменатель) | |
| РАДИАНЫ | Преобразует угол в радианы. | РАДИАН(угол) | |
| СЛУЧАЙ | Вычисляет случайное число от 0 до 1. | СЛУЧАЙ() | |
| СЛУЧМЕЖДУ | Вычисляет случайное число между двумя числами. | СЛУЧАЙ МЕЖДУ(нижний,верхний) | |
| ОКРУГЛ | Округляет число до указанного количества цифр. | ОКРУГЛ(число,число_цифр) | |
| ОКРУГЛ ВНИЗ | Округляет число в меньшую сторону (к нулю). | ОКРУГЛВНИЗ(число,число_разрядов) | |
| ОКРУГЛВВЕРХ | Округляет число в большую сторону (от нуля). | ОКРУГЛВВЕРХ(число,число_разрядов) | |
| ЗНАК | Возвращает знак числа. | ЗНАК(номер) | |
| SIN | Возвращает синус угла. | SIN(номер) | |
| SQRT | Вычисляет квадратный корень из числа. | SQRT(номер) | |
| ПРОМЕЖУТОЧНЫЙ ИТОГ | Возвращает сводную статистику для серии данных. | ПРОМЕЖУТОЧНЫЙ ИТОГ(номер_функции,REh2) | |
| СУММ | Складывает числа. | СУММ(число1,число2) | |
| СУММЕСЛИ | Суммирует числа, соответствующие критерию. | СУММЕСЛИ(диапазон,критерий,сумма_диапазон) | |
| СУММЕСЛИМН | Суммирует числа, соответствующие нескольким критериям. | СУММЕСЛИМН(сумма_диапазон,критерий_диапазон,критерий) | |
| СУММПРОИЗВ | Умножает массивы чисел и суммирует полученный массив. | СУММПРОИЗВ(массив1,массив2,массив3) | |
| TAN | Возвращает тангенс угла. | TAN(номер) | |
| TRUNC | Усекает число до определенного количества цифр. | TRUNC(число,число_цифр) | |
| Статистика | да | ||
| СРЕДНИЙ | Средние числа. | СРЕДНЕЕ(число1,число2) | |
| AVERAGEA | Средние числа. Включает текст и FALSE = 0, TRUE = 1. | СРЗНАЧА(значение1,значение2) | |
| СРЗНАЧЕСЛИ | Усредняет числа, соответствующие критерию. | СРЗНАЧЕСЛИ(диапазон,критерий,средний_диапазон) | |
| AVERAGEIFS | Усредняет числа, соответствующие нескольким критериям. | СРЗНАЧЕСЛИМН(средний_диапазон,критерий_диапазон,критерий) | |
| КОРРЕЛ | Вычисляет корреляцию двух рядов. | КОРРЕЛ(массив1,массив2) | |
| COUNT | Подсчет ячеек, содержащих число. | СЧЕТ(значение1,значение2) | |
| COUNTA | Подсчет непустых ячеек. | СЧЁТ(значение1,значение2) | |
| COUNTB004 COUNTB005 | Подсчет пустых ячеек. | СЧИТАТЬ ПУСТО (диапазон) | |
| СЧЁТЕСЛИ | Подсчитывает ячейки, соответствующие критерию. | СЧЁТЕСЛИ(диапазон,критерий) | |
| СЧЁТЕСЛИМН | Подсчитывает ячейки, соответствующие нескольким критериям. | СЧЁТЕСЛИМН(диапазон_критериев,критерий) | |
| ПРОГНОЗ | Прогноз будущих значений y на основе линейной линии тренда. | ПРОГНОЗ(x,известные_y,известные_x) | |
| ЧАСТОТА | Подсчитывает значения, попадающие в указанные диапазоны. | ЧАСТОТА(массив_данных,массив_бинов) | |
| РОСТ | Вычисляет значения Y на основе экспоненциального роста. | РОСТ(известный_ys,известный_x,новый_x,const) | |
| INTERCEPT | Вычисляет точку пересечения Y для линии наилучшего соответствия. | ПЕРЕХВАТ (известный_ys,известный_xs) | |
| НАИБОЛЬШИЙ | Возвращает k-е наибольшее значение. | БОЛЬШОЙ(массив,k) | |
| ЛИНЕЙН | Возвращает статистику по линии тренда. | ЛИНЕЙН(известный_ys,известный_xs,константа,статистика) | |
| MAX | Возвращает наибольшее число. | МАКС(число1,число2) | |
| МЕДИАНА | Возвращает медианное число. | МЕДИАНА(число1,число2) | |
| МИН | Возвращает наименьшее число. | МИН(число1,число2) | |
| РЕЖИМ | Возвращает наиболее распространенный номер. | РЕЖИМ(номер1,номер2) | |
| ПРОЦЕНТИЛЬ | Возвращает k-й процентиль. | ПРОЦЕНТИЛЬ(массив,k) | |
| PERCENTILE.INC | Возвращает k-й процентиль. Где k включительно. | ПРОЦЕНТИЛЬ.ВКЛ(массив,k) | |
| PERCENTILE.EXC | Возвращает k-й процентиль. Где k является исключительным. | ПРОЦЕНТИЛЬ.ИСКЛ(массив,k) | |
| КВАРТИЛЬ | Возвращает указанное значение квартиля. | КВАРТИЛЬ(массив,квартиль) | |
| QUARTILE.INC | Возвращает указанное значение квартиля. включительно. | КВАРТИЛЬ.ВКЛ(массив,кварта) | |
| КВАРТИЛЬ.ИСКЛ | Возвращает указанное значение квартиля. Эксклюзив. | КВАРТИЛЬ.ИСКЛ(массив,кварта) | |
| RANK | Ранг числа в серии. | РАНГ(номер,ссылка,порядок) | |
| RANK.AVG | Ранг числа в серии. Средние. | RANK.AVG(номер,ссылка,порядок) | |
| RANK.EQ | Ранг числа в серии. Высший ранг. | RANK.EQ(номер,ссылка,порядок) | |
| НАКЛОН | Вычисляет наклон на основе линейной регрессии. | НАКЛОН(известный_ys,известный_xs) | |
| МАЛЕНЬКИЙ | Возвращает k-е наименьшее значение. | МАЛЕНЬКИЙ(массив,k) | |
| СТАНДОТКЛОН | Вычисляет стандартное отклонение. | СТАНДОТКЛОН(номер1,номер2) | |
| STDEV.P | Вычисляет стандартное отклонение всей совокупности. | СТАНДОТКЛОН.П(число1,число2) | |
| STDEV.S | Вычисляет SD образца. | СТАНДОТКЛ.С(номер1,номер2) | |
| СТАНДОТКЛОН | Вычисляет стандартное отклонение всей совокупности | СТАНДОТКЛОН(число1,число2) | |
| TREND | Вычисляет значения Y на основе линии тренда. | TREND(известный_ys,известный_xs,новый_xs,постоянный) | |
| Текст | да | ||
| CHAR | Возвращает символ, заданный кодом. | СИМВОЛ(число) | |
| CLEAN | Удаляет все непечатаемые символы. | ОЧИСТКА(текст) | |
| КОД | Возвращает числовой код символа. | КОД(текст) | |
| СЦЕПИТЬ | Объединяет текст вместе. | СЦЕПИТЬ(текст1,текст2) | |
| ДОЛЛАР | Преобразует число в текст в денежном формате. | ДОЛЛАР(число,десятичные) | |
| EXACT | Проверить, равны ли ячейки. Деликатный случай. ИСТИНА/ЛОЖЬ | ТОЧНО(текст1,текст2) | |
| НАЙТИ | Находит положение текста в ячейке. С учетом регистра. | НАЙТИ(найти_текст,внутри_текста,начальный_номер) | |
| LEFT | Обрезает текст на несколько символов слева. | ЛЕВЫЙ(текст,число_знаков) | |
| LEN | Подсчитывает количество символов в тексте. | ДЛСТР(текст) | |
| LOWER | Преобразует текст в нижний регистр. | НИЖНИЙ (текст) | |
| MID | Извлекает текст из середины ячейки. | СРЕДНИЙ (текст, начальный_номер, число_знаков) | |
| PROPER | Преобразует текст в правильный регистр. | ПРАВИЛЬНЫЙ(текст) | |
| REPLACE | Заменяет текст в зависимости от его местоположения. | ЗАМЕНИТЬ (старый_текст, начальный_номер, число_символов, новый_текст) | |
| REPT | Повторяет текст несколько раз. | ПОВТОР(текст,количество_раз) | |
| ПРАВО | Усекает текст на несколько символов справа. | ПРАВО(текст,число_знаков) | |
| ПОИСК | Определяет положение текста в ячейке. Регистр не учитывается. | ПОИСК(найти_текст,внутри_текста,начальный_номер) | |
| ПОДСТАВИТЬ | Находит и заменяет текст. Деликатный случай. | ЗАМЕНИТЬ(текст,старый_текст,новый_текст,номер_экземпляра) | |
| ТЕКСТ | Преобразует значение в текст в определенном числовом формате. | ТЕКСТ(ЗНАЧЕНИЕ,format_text) | |
| ТРИМ | Удаляет все лишние пробелы из текста. | ОТДЕЛКА (текст) | |
| ПРОПИСНЫЕ | Преобразует текст в верхний регистр. | ВЕРХНИЙ (текст) | |
| ЗНАЧЕНИЕ | Преобразует число, сохраненное как текст, в число. | ЗНАЧЕНИЕ(текст) |
Памятка по формулам Excel для начинающих
Excel является одним из самых мощных программных инструментов для бухгалтерского учета, и большая часть его возможностей заключается в возможности использовать формулы Excel для автоматизации вашей работы. Когда вы впервые учитесь их использовать, легко немного растеряться — к счастью, эта шпаргалка по формулам Excel здесь, чтобы помочь вам разобраться во всем этом!
Давайте рассмотрим основы формул, некоторые общие функции, которые вы будете использовать с ними, и как организовать их, какими бы сложными они ни были.
В чем разница между функциями и формулами?
Многие люди используют термины «функция» и «формула» как синонимы, но на самом деле это две разные вещи. Формула начинается со знака равенства, за которым следует выражение, которое может включать одну или несколько функций. Функция начинается с имени и обычно сопровождается аргументами, заключенными в круглые скобки.
Вот пример формулы , в которой используется функция СЧЕТ :
=СЧЕТ(A1:A15)
Вы можете увидеть, как формула использует функцию СЧЁТ для вычисления результата, который представляет собой количество чисел в диапазоне ячеек от A1 до A15.
В приведенном ниже списке показан небольшой пример функций Excel, которые вы, вероятно, будете часто использовать.
| SUM | Вычисляет сумму полученных значений | СУММЕСЛИМН | Вычисляет сумму набора значений, где выполняется одно или несколько условий |
| СРЗНАЧ | Находит среднее значение в группе значений | МЕДИАНА | Находит медианное значение 90 в группе значений |
| MIN | Вычисляет наименьшее число в диапазоне | MAX | Вычисляет наибольшее число в диапазоне |
| COUNT | Подсчитывает количество числовых ячеек в диапазоне | СЧЁТЕСЛИМН | Подсчитывает количество ячеек в диапазоне, где выполняется одно или несколько условий |
При работе с именами, адресами, списками клиентов или любыми другими текстовыми данными текстовые функции Excel пригодятся.
Некоторые из часто используемых текстовых функций включают:
| ЛЕВЫЙ | Возвращает один или несколько символов с левой стороны текстовой строки | ПРАВО | Возвращает один или несколько символов с правой стороны текстовой строки |
| Середина | Возвращает один или несколько символов из середины текстовой строки | Concat | объединяет две (или более) строки текста |
| Верхний | Конвертирует строку текста во все верхние регистрации | 4 LOWER | Преобразует строку текста в нижний регистр |
| ТЕКСТ | Преобразует число в текст | ЗНАЧЕНИЕ | Преобразует текст в число |
1783=Функции поиска полезны для поиска в больших объемах данных. Хотя у большинства пользователей Excel есть своя любимая функция поиска (или пара функций), у каждой из них есть свои преимущества, которые лучше всего работают в разных ситуациях.
| Vlookup | Вертикально ищет значение в таблице | Hlookup | Горизонтально ищет значение в таблице |
| A Function, который ищет таблицу.0005 | ПОИСКПОЗ | Возвращает местоположение значения в последовательности | |
| XLOOKUP | Обновленная версия VLOOKUP доступна в новых версиях Excel |
Хотя это всего лишь несколько функций, которые вы часто будете использовать в своих формулах, это далеко не весь список. Если вам нужен полный список функций Excel, Microsoft удобно отсортировала их по категориям!
Теперь, когда вы знаете общие функции, в каких формулах они используются?
Формулы, по большей части, полностью настраиваются в зависимости от потребностей пользователя. Какие из них вы используете чаще всего, зависит от типа работы, которую вам нужно выполнить.
Однако вы можете попрактиковаться, работая с некоторыми простыми формулами.
Как только вы освоите основы, создавать индивидуальные сложные формулы станет намного проще и даже увлекательнее!
Формулы могут содержать:
- Операторы – такие как +, -, *, /
- Функции – такие как SUM
- Вложенные функции – такие как INDEX(MATCH())
- Имена – такие как CompanyName (названные с помощью менеджера имен)
- Константы – например, 10 или 20
- Ссылки на ячейки – например, A1
Чтобы попрактиковаться в работе с базовыми формулами Excel, ознакомьтесь со следующими сообщениями:
- Сумма последних N столбцов. позже включает функцию ИНДЕКС, так что вы попрактикуетесь с обеими!
- Список месяцев Excel — формула в этом посте использует общие функции даты и времени для создания адаптивного календаря.
- Счет между датами. Этот пост поможет вам ознакомиться с формулами, содержащими функцию СЧЁТЕСЛИМН.
Когда вы освоитесь с созданием простых формул, вы захотите начать использовать два инструмента, которые пригодятся, когда они станут более продвинутыми: ссылки на диапазоны и функцию LET.
Памятка по формулам Excel — ссылки на диапазон
Вы можете ссылаться на данные в других частях рабочего листа, указав ссылки на диапазоны в аргументах формулы или функции. Вы можете ссылаться на ячейки (или диапазон ячеек) при создании базовой формулы или формулы, включающей функцию. Например, предположим, что вы выбрали ссылку на ячейку B2 — формула вычислит результат, используя значение этой ячейки. Формула автоматически пересчитывает результат при изменении значения в этой ячейке.
В Excel можно использовать два типа ссылок на ячейки:
- Относительный — ссылка на ячейку, которая корректируется при копировании или заполнении ячейки.
- Абсолютный — ссылка на ячейку, которая не изменяется при копировании или заполнении.
При написании формулы можно смешивать относительные и абсолютные ссылки, чтобы изменялась строка, но не столбец, или наоборот.
Для назначения абсолютных ссылок используйте знак доллара ($).
То, где вы вставляете его в ссылку на ячейку, определяет, являются ли столбец, строка или и то, и другое абсолютными. Это может показаться немного запутанным, но научиться этому не так уж сложно, если вы увидите пару примеров!
Допустим, вам нужно снова сослаться на ячейку B2. Вот как можно назначить тип ссылки:
=B2 — ссылка полностью относительная
=$B2 — столбец (B) является абсолютным, но строка (2) является относительной
= B$2 – Столбец (B) относительный, строка (2) абсолютная
=$B$2 – Ссылка полностью абсолютная
Когда вам нужно повторить одни и те же вычисления для нескольких строк или столбцов, относительные ссылки пригодятся. Абсолютные ссылки полезны, когда вам нужно сохранить ссылку постоянной, например, если вам нужно рассчитать единый процент налога с продаж для диапазона ячеек.
Используйте LET, чтобы длинные формулы не выходили из-под контроля.
Никакая шпаргалка по формулам Excel не будет полной без упоминания полезной функции LET! Формулы Excel обычно довольно короткие и приятные. В результате их легко понять и поддерживать с течением времени. Пример короткой формулы выглядит примерно так:
=СУММ(B2:B8)
Эта формула использует функцию СУММ для сложения значений из ячейки B2 в ячейку B8. Довольно просто, правда?
По мере усложнения электронных таблиц усложняются и формулы. Вы можете столкнуться с громоздкими формулами, которые трудно читать и поддерживать, часто из-за дублирования. Например, формула с несколькими повторениями может выглядеть так:
=ЕСЛИ(ВПР(B8,Таблица1,2,0)>100, ВПР(B8,Таблица1,2,0), ВПР(B8,Таблица1,2,0)*C8)
Вы видите, что ВПР (B8, Table1, 2, 0) используется три раза, что делает формулу длинной, повторяющейся и немного запутанной. Функция LET позволяет присваивать имена значениям в формулах, а затем использовать их по мере необходимости в остальной части формулы.
Он может взять приведенную выше запутанную формулу и превратить ее во что-то гораздо более легкое для чтения:
=LET(Rate, VLOOKUP(B8,Table1,2,0), IF(Rate>100,Rate,Rate*C8))
Использование функции LET считается более продвинутым навыком, но новички могут начать использовать его в более простых функциях, чтобы ознакомиться с тем, как это работает.
В Excel формулы можно использовать для расчета всего, о чем вы только можете подумать, — вам не нужно бояться проявить творческий подход. Хотя эта шпаргалка по формулам Excel охватывает основы формул Excel, лучший способ по-настоящему познакомиться с ними — это попрактиковаться, заставив Excel вычислить как можно больше значений. Другими словами, когда вы обнаружите, что вручную вводите значение на лист, посмотрите, сможете ли вы преобразовать его в формулу. Эта привычка не только позволит вам увидеть, что возможно, но и устранит шаг ручного ввода (повысит эффективность и уменьшит количество ошибок, совершаемых вручную).
