Итак, вы прошлись по основам, разобрались с промежуточными задачами и теперь готовы сразиться с большими орудиями — продвинутыми формулами Excel! В Excel есть множество расширенных функций и формул для сложных расчетов, поэтому полезно иметь руководство, в котором указаны именно те, которые вам нужны.
Эта шпаргалка по формулам Excel охватывает расширенные формулы прогнозирования, статистический анализ, функции обработки данных, обработку ошибок и многое другое.
Этот справочник предоставит вам знания о том, как использовать эти расширенные функции. Каждая формула сопровождается четкими пояснениями, синтаксисом и практическими примерами, которые помогут пользователям Excel среднего уровня стать опытными пользователями.
Пожалуйста, скачайте и распечатайте шпаргалку и держите ее под рукой.
Хорошо, давайте начнем.
Во-первых, давайте перейдем к формулам массива.
Содержание
Формулы массива
В нашей памятке для начинающих показано, как сортировать и фильтровать данные вручную. Опытные пользователи делают это программно с помощью формул массива.
Формулы массива позволяют выполнять вычисления одновременно в нескольких ячейках. Это три ключевые функции:
- УНИКАЛЬНЫЙ
- СОРТИРОВАТЬ
- ФИЛЬТР
Некоторые из этих функций доступны только в самых последних версиях 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. ПРОЦЕНТИЛЬ Функция
Эта функция вычисляет процент точек данных, которые находятся ниже определенного значения. Синтаксис:
ПРОЦЕНТИЛЬ.ВКЛ(массив, к)
Предположим, вы хотите вычислить 70-й процентиль данных в столбце B. Используйте эту формулу:
=ПРОЦЕНТИЛЬ.ВКЛ(B2:B6, 0,7)
2. Функция КВАРТИЛЬ
Эта функция является разновидностью функции процентилей, но использует четверти для деления данных. Это синтаксис:
КВАРТИЛЬ.ВКЛ(массив, кварт)
- массив : диапазон данных
- кварта : 1 для 25-го процентиля, 2 для 50-го, 3 для 75-го и 4 для максимального.
Приведенная ниже формула рассчитает первую квартиль данных в столбце A.
=КВАРТИЛЬ.ВКЛ(A2:A5, 1)
Расширенный анализ данных и формулы манипулирования
Несколько расширенных функций позволяют переключать формат данных, анализировать частотные распределения и извлекать данные из сводных таблиц.
- ТРАНСПОЗИРОВАТЬ
- ЧАСТОТА
- ПОЛУЧИТЬ ОСНОВНЫЕ ДАННЫЕ
1. Функция ТРАНСП.
Иногда вам нужно переместить данные из строк в столбцы и наоборот. Вы можете сделать это вручную или использовать вместо этого функцию ТРАНСП.
Предположим, у вас есть товары «Футболка», «Толстовка» и «Джинсы» в ячейках A2, A3 и A4. Вы хотите превратить их в заголовки столбцов. Эта функция возвращает значения в одной строке:
=ТРАНСП(A2:A4)
2. Функция ЧАСТОТА
Эта функция вычисляет частотное распределение значений в наборе данных. Это синтаксис:
ЧАСТОТА(массив_данных,массив_бинов)
Предположим, у вас есть данные о продажах в столбце 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)
Вы можете комбинировать это с несколькими функциями, чтобы по-разному реагировать в зависимости от типа ошибки. Вот наиболее распространенные ошибки и их значения:
- #НУЛЕВОЙ! (в диапазоне не найдена общая ячейка)
- #ДЕЛ/0! ( деление на ноль или пустая ячейка)
- #ЦЕНИТЬ! (неподходящий тип данных или аргумент в формуле)
- #ССЫЛКА! (ссылочная ячейка была удалена или есть циклическая ссылка )
- #ИМЯ? (Excel не распознает функцию или диапазон)
- #ЧИСЛО! (недопустимое числовое значение)
- #Н/Д (значение не может быть найдено)
Предположим, вы хотите обработать три конкретных типа ошибок. Используйте эту формулу для отображения определенного сообщения об ошибке в зависимости от типа:
=IF(ISERROR(C2), IF(ERROR.TYPE(C2)=2, «Ошибка деления на ноль», IF(ERROR.TYPE(C2)=3, «Ошибка неверного значения», IF(ERROR.TYPE(C2) )=7, «Значение не найдено», «Другая ошибка»))), C2)
Расширенные формулы поиска
Наши шпаргалки для начинающих и продолжающих охватывали ряд функций поиска . Вот некоторые дополнительные параметры:
- XLOOKUP
- ХМАТЧ
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 подобен холсту, а эти формулы — вашей палитре — так что вперед, рисуйте свой шедевр!
