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

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

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

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

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

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

Excel-формулы-шпаргалка-расширенный-1 Скачать

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

Оглавление

• Формулы массива
  • 1. УНИКАЛЬНАЯ функция
  • 2. Функция СОРТИРОВКИ
  • 3. Функция ФИЛЬТР
• Рандомизация функций Excel
  • 1. Функция СЛУЧМЕЖДУ
  • 2. Функция СЛУЧАЙ
• Расширенные формулы прогнозирования в Microsoft Excel
  • 1. ПРОГНОЗ.ETS Функция
  • 2. Функция ТРЕНД
• Расширенные статистические формулы
  • 1. ПРОЦЕНТИЛЬ Функция
  • 2. Функция КВАРТИЛЬ
• Расширенный анализ данных и формулы манипулирования
  • 1. Функция ТРАНСП.
  • 2. Функция ЧАСТОТА
  • 3. Функция ПО��УЧИТЬ ОСНОВНЫЕ ДАННЫЕ
• Расширенная обработка ошибок
• Расширенные формулы поиска
  • 1. Функция XLOOKUP
  • 2. Функция ПОИСКПОЗ
• Последние мысли

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

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

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

1. УНИКАЛЬНЫЙ

2. СОРТИРОВАТЬ

3. ФИЛЬТР

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

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

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

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

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

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

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

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

• array : диапазон значений для сортировки.

• sort_index : столбец для сортировки (по умолчанию 1)

• sort_order : 1 по возрастанию (по умолчанию) или 2 по убыванию).

• by_col : TRUE для сортировки по столбцу (по умолчанию) или FALSE для сортировки по строке.

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

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

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

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

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

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

• array : диапазон для фильтрации.

• include : условие, определяющее, какие значения фильтровать.

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

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

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

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

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

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

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

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

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

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

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

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

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

Синтаксис:

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

• ряды : количество рядов

• столбцы : количество столбцов

• мин : наименьшее число

• макс : наибольшее число

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

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

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

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

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

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

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

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

Синтаксис:

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

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

• значения : исторические данные.

• временная шкала : диапазон дат

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

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

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

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

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

• known_y : диапазон значений y

• known_x : диапазон значений x

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

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

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

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

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

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

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

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

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

• массив : диапазон ячеек

• k : процентиль от 0 до 1

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

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

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

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

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

• массив : диапазон данных

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

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

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

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

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

1. ТРАНСПОЗИРОВАТЬ

2. ЧАСТОТА

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

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

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

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

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

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

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

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

• data_array : диапазон значений.

• bins_array : используемые интервалы.

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

• ниже 20.

• от 20 до 80.

• выше 80.

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

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

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

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

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

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

• data_field : поле данных или значение, которое вы хотите получить из сводной таблицы.

• pivot_table : ссылка на сводную таблицу.

• field1, item1 и т. д. : пары поле/элемент для фильтрации.

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

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

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

Даже самые простые формулы 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)

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

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

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)

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

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

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

• lookup_value : значение, которое вы хотите найти.

• lookup_array : диапазон, который вы хотите найти.

• match_type : точное совпадение (0), следующее наименьшее (-1), следующее наибольшее (1).

• search_mode : бинарный поиск (1) или линейный поиск (2).

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

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

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

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

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

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

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