Памятка по формулам Excel: расширенное руководство

Шпаргалка по формулам Excel

Итак, вы прошлись по основам, разобрались с промежуточными задачами и теперь готовы сразиться с большими орудиями — продвинутыми формулами Excel! В Excel есть множество расширенных функций и формул для сложных расчетов, поэтому полезно иметь руководство, в котором указаны именно те, которые вам нужны.

Эта шпаргалка по формулам Excel охватывает расширенные формулы прогнозирования, статистический анализ, функции обработки данных, обработку ошибок и многое другое.

Этот справочник предоставит вам знания о том, как использовать эти расширенные функции. Каждая формула сопровождается четкими пояснениями, синтаксисом и практическими примерами, которые помогут пользователям Excel среднего уровня стать опытными пользователями.

Пожалуйста, скачайте и распечатайте шпаргалку и держите ее под рукой.

Хорошо, давайте начнем.

Во-первых, давайте перейдем к формулам массива.

Формулы массива

В нашей памятке для начинающих показано, как сортировать и фильтровать данные вручную. Опытные пользователи делают это программно с помощью формул массива.

Формулы массива позволяют выполнять вычисления одновременно в нескольких ячейках. Это три ключевые функции:

  1. УНИКАЛЬНЫЙ
  2. СОРТИРОВАТЬ
  3. ФИЛЬТР

Некоторые из этих функций доступны только в самых последних версиях Microsoft Excel.

1. УНИКАЛЬНАЯ функция

Функция UNIQUE принимает диапазон и возвращает список уникальных значений.

Предположим, у вас есть данные о продажах предметов одежды. Чтобы найти уникальные элементы в столбце A, используйте эту формулу:

=УНИКАЛЬНЫЙ(A2:A6)

Шпаргалка по формулам Excel

2. Функция СОРТИРОВКИ

Функция SORT сортирует содержимое диапазона. Синтаксис:

SORT(массив, [индекс_сортировки], [порядок_сортировки], [по_столбцу])

  • array : диапазон значений для сортировки.
  • sort_index : столбец для сортировки (по умолчанию 1)
  • sort_order : 1 по возрастанию (по умолчанию) или 2 по убыванию).
  • by_col : TRUE для сортировки по столбцу (по умолчанию) или FALSE для сортировки по строке.

Последние три аргумента являются необязательными, и обычно используются значения по умолчанию.

Чтобы отсортировать данные выборки по первому столбцу, используйте эту формулу:

=СОРТИРОВАТЬ(A2:C6)

Функция сортировки в таблице Excel

3. Функция ФИЛЬТР

Функция ФИЛЬТР позволяет фильтровать диапазон по определенному условию. Это синтаксис:

=ФИЛЬТР(массив, включить, [if_empty])

  • array : диапазон для фильтрации.
  • include : условие, определяющее, какие значения фильтровать.
  • if_empty : указывает, что возвращать, если ни одно значение не соответствует критериям фильтрации (по умолчанию «»).

Предположим, вы хотите отфильтровать строки в выборке данных, чтобы показать только те, где стоимость продаж превышает 15 долларов США. Используйте эту формулу:

=ФИЛЬТР(A2:C6, C2:C6>15)

Функция фильтра в таблице Excel

Рандомизация функций Excel

Наш промежуточный чит показывает, как использовать функцию RAND, которая выдает случайное число от 0 до 1.

Опытные пользователи Excel знают, как использовать функции рандомизации для быстрого создания выборочных данных.

1. Функция СЛУЧМЕЖДУ

Функция СЛУЧМЕЖДУ более гибкая, чем СЛУЧАЙ, потому что вы можете указать нижние и верхние числа как числа, отличные от 0 и 1.

Чтобы сгенерировать данные с числами от 1 до 100, введите эту формулу в ячейку A1:

=СЛУЧМЕЖДУ(1, 100)

Затем скопируйте ячейку в любое количество строк и столбцов. Создание сетки рандомизированных чисел занимает несколько секунд:

Randbetween в электронной таблице Excel

2. Функция СЛУЧАЙ

Вы можете подумать, что было бы неплохо избежать ручного копирования функции СЛУЧМЕЖДУ. Чтобы получить дополнительные возможности, вы можете использовать новую функцию СЛУЧАЙ в последней версии Microsoft Excel.

Синтаксис:

RANDARRAY([строки], [столбцы], [мин.], [макс.], [целое число])

  • ряды : количество рядов
  • столбцы : количество столбцов
  • мин : наименьшее число
  • макс : наибольшее число
  • целое число : по умолчанию TRUE, в противном случае используются десятичные числа.

Чтобы сгенерировать случайные числа от 1 до 100 в шести строках и девяти столбцах, используйте следующую формулу:

=СЛУЧАЙНО(6, 9, 1, 100, ИСТИНА)

Функция Randarray в таблице Excel

Расширенные формулы прогнозирования в Microsoft Excel

Функции прогнозирования Excel используются для прогнозирования будущих значений на основе существующих тенденций данных. Эти функции помогают выявлять закономерности и тенденции проекта на основе ваших данных.

1. ПРОГНОЗ.ETS Функция

Старая функция ПРОГНОЗ была заменена набором новых функций в Excel 2016.

Вы выбираете функцию на основе конкретной модели прогнозирования, которую вы хотите. Например, функция FORECAST.ETS использует алгоритм экспоненциального сглаживания .

Синтаксис:

FORECAST.ETS (целевая_дата, значения, временная шкала)

  • target_date : дата, для которой вы хотите вычислить значение.
  • значения : исторические данные.
  • временная шкала : диапазон дат

Предположим, у вас есть даты с 1 по 5 января в столбце A и суммы продаж в столбце B. Эта формула будет предсказывать следующую сумму продаж:

=ПРОГНОЗ.ETS(«6 января 2023», B2:B6, A2:A6)

Функция прогноза.ets в электронной таблице Excel

2. Функция ТРЕНД

Функция TREND проецирует набор значений на основе метода наименьших квадратов. Он возвращает массив. Синтаксис:

TREND(известный_y, [известный_x], [новый_x], [постоянный])

  • known_y : диапазон значений y
  • known_x : диапазон значений x
  • new_x : диапазон вычисляемых значений

Часто known_y — это точки данных, а known_x — даты.

Используя те же данные, что и в предыдущем примере, вы можете ввести приведенную ниже формулу в ячейку C2. Будет сгенерирован набор значений.

=ТЕНДЕНЦИЯ(B2:B6, A2:A6)

функция тренда в таблице excel

Расширенные статистические формулы

Расширенные статистические функции включают расчет процентилей и квартилей. Некоторые математические функции доступны для обратной совместимости, но рекомендуется использовать самые последние версии.

1. ПРОЦЕНТИЛЬ Функция

Эта функция вычисляет процент точек данных, которые находятся ниже определенного значения. Синтаксис:

ПРОЦЕНТИЛЬ.ВКЛ(массив, к)

Предположим, вы хотите вычислить 70-й процентиль данных в столбце B. Используйте эту формулу:

=ПРОЦЕНТИЛЬ.ВКЛ(B2:B6, 0,7)

процентильная функция в таблице excel

2. Функция КВАРТИЛЬ

Эта функция является разновидностью функции процентилей, но использует четверти для деления данных. Это синтаксис:

КВАРТИЛЬ.ВКЛ(массив, кварт)

  • массив : диапазон данных
  • кварта : 1 для 25-го процентиля, 2 для 50-го, 3 для 75-го и 4 для максимального.

Приведенная ниже формула рассчитает первую квартиль данных в столбце A.

=КВАРТИЛЬ.ВКЛ(A2:A5, 1)

функция квартиля в таблице excel

Расширенный анализ данных и формулы манипулирования

Несколько расширенных функций позволяют переключать формат данных, анализировать частотные распределения и извлекать данные из сводных таблиц.

  1. ТРАНСПОЗИРОВАТЬ
  2. ЧАСТОТА
  3. ПОЛУЧИТЬ ОСНОВНЫЕ ДАННЫЕ

1. Функция ТРАНСП.

Иногда вам нужно переместить данные из строк в столбцы и наоборот. Вы можете сделать это вручную или использовать вместо этого функцию ТРАНСП.

Предположим, у вас есть товары «Футболка», «Толстовка» и «Джинсы» в ячейках A2, A3 и A4. Вы хотите превратить их в заголовки столбцов. Эта функция возвращает значения в одной строке:

=ТРАНСП(A2:A4)

2. Функция ЧАСТОТА

Эта функция вычисляет частотное распределение значений в наборе данных. Это синтаксис:

ЧАСТОТА(массив_данных,массив_бинов)

Предположим, у вас есть данные о продажах в столбце B, и вы хотите проанализировать частотное распределение значений на основе количества сумм:

  • ниже 20.
  • от 20 до 80.
  • выше 80.

Это представляет собой три интервала и может быть рассчитано по следующей формуле:

=ЧАСТОТА(A2:A6, {20,80})

частотная функция в таблице excel

Чтобы узнать больше о частотных распределениях в Excel, посмотрите это видео:

3. Функция ПОЛУЧИТЬ ОСНОВНЫЕ ДАННЫЕ

Эта функция позволяет извлекать сводную информацию из сводных таблиц. Это синтаксис:

GETPIVOTDATA(поле_данных, сводная_таблица, [поле1, элемент1], [поле2, элемент2], …)

  • data_field : поле данных или значение, которое вы хотите получить из сводной таблицы.
  • pivot_table : ссылка на сводную таблицу.
  • field1, item1 и т. д. : пары поле/элемент для фильтрации.

Предположим, у вас есть сводная таблица, основанная на цвете проданных товаров. Чтобы извлечь продажи для красных предметов, используйте эту формулу:

=GETPIVOTDATA(«Продажи», A1, «Цвет», «Красный»)

Функция getpivotdata в электронной таблице Excel

Расширенная обработка ошибок

Даже самые простые формулы Excel могут вызывать ошибки. Пользователи среднего уровня должны знать, как использовать ISERROR для обработки ошибок. Опытные пользователи также должны быть знакомы с функцией ERROR.TYPE для идентификации ошибок.

Функция ОШИБКА.ТИП помогает определить конкретный тип ошибки в ячейке или формуле.

Он возвращает числовое значение, соответствующее различным типам ошибок, таким как #Н/Д, #ЗНАЧ!, #ССЫЛКА! и другие.

Предположим, у вас есть ошибка в ячейке A1, и вы хотите определить ее тип ошибки. Следующая формула вернет число, соответствующее конкретной ошибке:

=ОШИБКА.ТИП(A1)

Вы можете комбинировать это с несколькими функциями, чтобы по-разному реагировать в зависимости от типа ошибки. Вот наиболее распространенные ошибки и их значения:

  1. #НУЛЕВОЙ! (в диапазоне не найдена общая ячейка)
  2. #ДЕЛ/0! ( деление на ноль или пустая ячейка)
  3. #ЦЕНИТЬ! (неподходящий тип данных или аргумент в формуле)
  4. #ССЫЛКА! (ссылочная ячейка была удалена или есть циклическая ссылка )
  5. #ИМЯ? (Excel не распознает функцию или диапазон)
  6. #ЧИСЛО! (недопустимое числовое значение)
  7. #Н/Д (значение не может быть найдено)

Предположим, вы хотите обработать три конкретных типа ошибок. Используйте эту формулу для отображения определенного сообщения об ошибке в зависимости от типа:

=IF(ISERROR(C2), IF(ERROR.TYPE(C2)=2, «Ошибка деления на ноль», IF(ERROR.TYPE(C2)=3, «Ошибка неверного значения», IF(ERROR.TYPE(C2) )=7, «Значение не найдено», «Другая ошибка»))), C2)

Функция Error.type в таблице Excel

Расширенные формулы поиска

Наши шпаргалки для начинающих и продолжающих охватывали ряд функций поиска . Вот некоторые дополнительные параметры:

  1. XLOOKUP
  2. ХМАТЧ

1. Функция XLOOKUP

Эта функция поиска позволяет искать значение в диапазоне и возвращать соответствующее значение из другого столбца или диапазона.

Он предлагает больше гибкости, чем более простые функции поиска, такие как ВПР . Это синтаксис:

XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [режим_сопоставления], [режим_поиска], [если_не найдено])

  • lookup_value : значение, которое вы хотите найти.
  • lookup_array : диапазон поиска.
  • return_array : диапазон, в котором будет отображаться соответствующее значение.
  • match_mode : точное совпадение (0), следующее меньшее (1), следующее большее (-1) или совпадение с подстановочным знаком (2).
  • search_mode : -1 для поиска сверху вниз, 1 для поиска снизу вверх или 2 для бинарного поиска.
  • if_not_found : устанавливает возвращаемое значение, если совпадение не найдено.

Предположим, вы хотите найти диапазон данных для первого появления предмета одежды и вернуть суммы продаж. Эта формула будет искать текст «Толстовка» и возвращать значение в соседней ячейке, если оно найдено:

=XLOOKUP(«Толстовка», A2:A6, B2:B6)

Функция Xlookup в электронной таблице Excel

2. Функция ПОИСКПОЗ

Эта функция позволяет найти положение указанного значения в диапазоне или массиве. Это синтаксис:

XMATCH(искомое_значение, искомый_массив, [тип_сопоставления], [режим_поиска])

  • lookup_value : значение, которое вы хотите найти.
  • lookup_array : диапазон, который вы хотите найти.
  • match_type : точное совпадение (0), следующее наименьшее (-1), следующее наибольшее (1).
  • search_mode : бинарный поиск (1) или линейный поиск (2).

Предположим, вы хотите найти первое вхождение желтого элемента в диапазоне в столбце B. Используйте эту формулу:

=XПОИСКПОЗ(«Желтый», B2:B6, 0)

функция xmatch в таблице excel

Последние мысли

Эта шпаргалка охватывает широкий спектр функций, от статистического анализа, формул поиска, методов обработки данных и стратегий обработки ошибок.

Приведенные примеры и пояснения помогают демистифицировать эти сложные формулы, делая их доступными даже для людей с ограниченным опытом.

Когда вы начнете включать их в свои задачи Excel, вы начнете повышать свои навыки работы с Excel до продвинутого уровня.

Но помните, эта шпаргалка — лишь верхушка айсберга. По-настоящему удивительные вещи происходят, когда вы проявляете творческий подход, смешиваете и подбираете эти формулы и адаптируете их для решения своих уникальных задач. Excel подобен холсту, а эти формулы — вашей палитре — так что вперед, рисуйте свой шедевр!