Все функции в эксель описание: Функции Excel (по категориям) — Служба поддержки Майкрософт

Статистические функции в Excel. Описание всех функций, как их использовать

Автор Амина С. На чтение 9 мин Опубликовано 29.01.2021

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

Содержание

1. Как пользоваться статистическими функциями
2. Перечень статистических функций
3. Функция СРГЕОМ
4. Функция СТАНДОТКЛОН
5. Функция МОДА.ОДН
6. Функция НАИМЕНЬШИЙ
7. Функция НАИБОЛЬШИЙ
8. Функция МЕДИАНА
9. Функция СРЗНАЧЕСЛИ
10. Функция МИН
11. Функция МАКС
12. Функции СРЗНАЧ и СРЗНАЧА
13. Функция РАНГ. СР

Есть несколько способов ввода любой функции, и статистические не являются исключением:

1. Ввести непосредственно в ячейке, предварительно нажав клавишу =. Это касается самых простых функций, несложных для запоминания и содержащих один или два аргумента. Например, так можно делать для операции умножения, сложения, вычитания и деления. А вот если функция сложная, то можно воспользоваться помощником. Это уже второй способ.
2. Помощник по использованию функций. Он не только подсказывает, какая формула что означает, а и помогает ввести правильные аргументы применительно к конкретной функции.

Вызвать помощник можно несколькими способами:

1. Воспользоваться кнопкой «Вставить функцию», расположенной слева от строки формул.
2. Вызвать мастер ввода функций через кнопку «Вставить функцию», которая находится в левой части панели, которая открывается по клику на вкладку «Формулы».
3. Воспользовавшись горячими клавишами Shift+F3.

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

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

Чтобы подтвердить выбор функции, которая будет вводиться, нужно нажать клавишу ОК. После этого появится такое окно, в котором можно ввести параметры функции (или, как их еще называют, аргументы).

Интересный факт. Можно выбрать функцию еще одним способом. Для этого нужно перейти на вкладку «Формулы» и нажать на кнопку «Другие функции», расположенной на ленте.

Далее будет пункт «Другие функции» – «Статистические» и в появившемся списке ищем подходящую функцию и выбираем ее. Этот перечень может прокручиваться.

А теперь давайте перейдем непосредственно к рассмотрению статистических функций.

Функция СРГЕОМ

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

Формула очень простая: =СРГЕОМ(число1;число2;…). Кроме чисел также можно указать диапазон значений, которые учитываются этой функцией. Что же такое среднее геометрическое? Это число, которое может заменять любое из чисел в последовательности таким образом, чтобы не менялось произведение этих значений. Еще один часто используемый термин – среднее пропорциональное. Это синоним к среднему геометрическому. Такой второй термин используется, потому что среднее геометрическое пропорционально к первому и второму числам.

Функция СТАНДОТКЛОН

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

Вообще, стандартное отклонение рассчитывается, как квадратный корень из дисперсии. Но в Эксель есть специальная формула, которая сразу вычисляет степень дисперсии, после чего на основе полученного значения получает стандартное (или среднеквадратическое) отклонение.

Сама эта формула довольно старая, но знать о ней надо, потому что время от времени ее можно найти в готовых таблицах. Сейчас уже есть более новые версии этой функции – СТАНДОТКЛОН.В и СТАНДОТКЛОН.Г. Последняя функция находит среднеквадратическое отклонение по генеральной совокупности, в то время как первая ориентируется исключительно на выборку.

В остальном, синтаксис обеих функций такой же, как и для вычисления среднего арифметического (об этом мы поговорим позже) – числа, которые перечислены через скобку.

Функция МОДА.ОДН

Мода выборки абсолютно не связана с одеждой или популярными машинами. Но при этом она связана со словом «популярный». Если говорить о статистике, то это значение в выборке, которое встречается наиболее часто. Соответственно, функция МОДА.ОДН дает возможность определить это значение.

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

Эта функция подходит для горизонтальных массивов. Если же нужно определить моду выборки для вертикального массива, используется похожая функция МОДА.НСК. Общий внешний вид функции следующий: =МОДА.ОДН(аргумент 1, аргумент 2; аргумент …).

Функция НАИМЕНЬШИЙ

Задача этой функции – выполнение поиска из того набора значений, который был указан пользователем. Принцип ее работы такой же, как и следующий, только поиск осуществляется по направлению снизу вверх, от наименьшего числа к самому большому. Синтаксис этой функции предельно простой: =НАИМЕНЬШИЙ(массив;k).

Функция имеет два основных аргумента: массив данных, по которым будет осуществляться поиск и порядковый номер элемента, который надо найти. Далее функция работает следующим образом: сначала она ищет самое маленькое значение, потом начинает перебирать цифры снизу вверх. Первое значение считается 1. То есть, если использовать число 1 во втором аргументе, то результат будет эквивалентным функции МИН, о которой мы поговорим немного позже.

Функция НАИБОЛЬШИЙ

Функция НАИБОЛЬШИЙ является аналогичной, только отсчет выполняет, начиная с самого большого значения. После того, как передать ей коэффициент, она ищет в порядковом ряду с большего в меньший число, занимающее соответствующее место и возвращает его. Работают обе функции аналогичным образом. Предположим, у нас есть числовой ряд. Если в нем в качестве числа k указать 2, то в результате получится число 15, поскольку оно является вторым по величине в диапазоне, который прописан в первом аргументе.

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

Функция МЕДИАНА

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

На этом примере видно, как на практике осуществляется работа с функцией. В диалоговом окне «Аргументы функции» можно вводить большое количество чисел, ячеек и диапазонов. На картинке мы попробовали ввести число в первую строку, ячейку во вторую и диапазон значений в третью. Получили в результате число 12. Максимальное количество аргументов этой функции – 255, что более, чем достаточно для полноценного использования этой функции.

Функция СРЗНАЧЕСЛИ

Это улучшенная версия функции СРЗНАЧ, задача которой – находить среднее арифметическое, но лишь при условии, что определенное условие выполняется. Эта функция уже несколько сложнее тех, которые приводились выше: =СРЗНАЧЕСЛИ(диапазон;условие;диапазон_усреднения). Давайте рассмотрим каждый аргумент более подробно:

1. Диапазон. Это ячейки, которые проверяются на предмет соответствия определенному условию.
2. Условие. Это критерий, на предмет соответствия которому проверяется диапазон.
3. Диапазон усреднения. Это тот диапазон, из которого будет доставаться среднее арифметическое. Этот аргумент вводить необязательно, поскольку диапазон ячеек и диапазон усреднения могут совпадать.

Функция МИН

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

1. На рынке акций для определения времени, когда цела была наиболее низкой.
2. Для определения слабых мест в годовом бюджете (например, в каком месяце доходы компании были минимальными) с целью их дальнейшего исправления. Например, можно определить наименее доходный месяц и проанализировать факторы, которые этому способствовали.

Существует огромное количество других ситуаций, когда можно использовать функцию МИН. В самом общем виде она выглядит следующим образом: =МИН(число1;число2;…). Принцип заполнения аргументов этой функции аналогичен функции МАКС.

Функция

МАКС

Как становится понятно из названия, эта функция ищет максимальное значение в определенной числовой выборке. Ситуации, в которых она может использоваться, в принципе, те же за тем лишь исключением, что все в противоположную сторону. Например, компания может с помощью функции МАКС определить самый доходный месяц и понять, каковы причины этого успеха.

Функции СРЗНАЧ и СРЗНАЧА

Стандартная функция СРЗНАЧ определяет среднее арифметическое в числовой выборке. Общий вид формулы такой же, как и для любой другой выборки значений. Сначала пишется название функции, после чего в скобках приводятся числа и диапазоны, которые необходимо обработать с помощью этой функции. То есть, общий вид формулы следующий: =СРЗНАЧ(число1;число2;…).

Как мы поняли, можно использовать как обычные числа (очень полезно для использования значений, которые не будут меняться в течение ближайшего времени), ссылки на ячейку (они применяются для тех значений, которые в будущем изменятся) и на диапазон (в этом случае будет использоваться целый набор чисел за один раз). Чтобы после ввода одного аргумента начать записывать другой, достаточно нажать на соответствующее поле в мастере функций или просто нажать на клавишу Tab.

Максимальное количество аргументов, которые можно использовать в этой функции – 255. При этом обязательным аргументом является только первое число. В качестве аргументов не могут использоваться текстовые и логические значения. Они просто не учитываются формулой, в которой используется указанный оператор. Основное отличие функции СРЗНАЧА от СРЗНАЧ заключается в том, что текстовые значения и «ЛОЖЬ» считаются нулевыми, а значение «Истина» приравнивается к единице.

Функция РАНГ.СР

С помощью функции РАНГ.СР пользователь может вернуть ранг числа. Если несколько чисел в одном диапазоне относятся к одному рангу, то возвращается среднее. Имеет три аргумента, два из которых – обязательные:

1. Число. Это то число, для которого осуществляется определение ранга.
2. Ссылка. Это массив чисел, или ссылка на этот массив.
3. Порядок. Это число, которое влияет на способ, в который значения будут упорядочиваться.

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

Оцените качество статьи. Нам важно ваше мнение:

Функция COUNTIF | Справочные статьи Smartsheet

Подсчитывает число ячеек в диапазоне, удовлетворяющих критерию.

Пример использования

COUNTIF(Количество:Количество; >25)

Синтаксис

COUNTIF(

• диапазон

• критерий

)

• диапазон

  —

  Группа ячеек для подсчёта.

• критерий

  —

  Значение, определяющее, какие ячейки в диапазоне будут подсчитываться (например: 15, «Здравствуй, Мир!», >25).

Примечания об использовании

• Допустимые операторы для критерия: = (равно), <> (не равно), > (больше), < (меньше), >= (больше или равно), <= (меньше или равно).
• Пустые ячейки не подсчитываются при использовании оператора <> (не равно). Например, формула =COUNTIF(Позиция:Позиция; <> «Рубашка») не будет подсчитывать пустые ячейки в столбце «Позиция».

Примеры

В этом примере используется информация из следующей таблицы:

Вот несколько примеров использования функции COUNTIF с данными из приведённой выше таблицы.

Функции Excel (в простых шагах)

Ниже вы можете найти 40 самых популярных страниц в этом разделе. Приятного обучения!

1 ВПР: функция ВПР — одна из самых популярных функций в Excel. Эта страница содержит много простых примеров VLOOKUP.

2 Изменение в процентах: формула изменения в процентах очень часто используется в Excel. Например, для расчета ежемесячного изменения и общего изменения.

3 График погашения кредита: В этом примере показано, как создать график погашения кредита в Excel.

4 Случайные числа. В Excel есть две очень полезные функции для генерации случайных чисел. РЭНД и СЛУЧМЕЖДУ.

5 Если: функция ЕСЛИ — одна из наиболее часто используемых функций в Excel. Эта страница содержит много простых примеров IF.

6 Стандартное отклонение: на этой странице объясняется, как рассчитать стандартное отклонение на основе всей совокупности с помощью функции СТАНДОТКЛОН.П в Excel и как оценить стандартное отклонение на основе выборки с помощью функции СТАНДОТКЛОН.С в Excel.

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

8 Countif: мощная функция COUNTIF в Excel подсчитывает ячейки на основе одного критерия. Эта страница содержит много простых примеров COUNTIF.

9 Смещение: функция СМЕЩ в Excel возвращает ячейку или диапазон ячеек, который представляет собой заданное количество строк и столбцов из ячейки или диапазона ячеек.

10 Сравнить два столбца: чтобы сравнить два столбца, используйте ЕСЛИ, ЕОШИБКА и ПОИСКПОЗ в Excel. Вы можете отображать дубликаты или уникальные значения.

11 Сложные проценты: что такое сложные проценты и какова формула сложных процентов в Excel? Этот пример дает вам ответы на эти вопросы.

12 СУММЕСЛИ: Мощная функция СУММЕСЛИ в Excel суммирует ячейки на основе одного критерия. Эта страница содержит множество простых примеров SUMIF.

13 Объединить: Используйте СЦЕПИТЬ, СЦЕПИТЬ, СОЕДИНЕНИЕ ТЕКСТОВ или оператор & в Excel для объединения (объединения) двух или более текстовых строк.

14 Средневзвешенное значение: Чтобы вычислить средневзвешенное значение в Excel, просто используйте СУММПРОИЗВ и СУММ.

15 Индекс и сопоставление: Используйте ИНДЕКС и ПОИСКПОЗ в Excel и произведите впечатление на своего босса. Вместо ВПР используйте ИНДЕКС и ПОИСКПОЗ. Для расширенного поиска вам понадобятся ИНДЕКС и ПОИСКПОЗ.

16 СУММПРОИЗВ: Чтобы вычислить сумму произведений соответствующих чисел в одном или нескольких диапазонах, используйте мощную функцию СУММПРОИЗВ Excel.

17 Содержит определенный текст: Чтобы проверить, содержит ли ячейка определенный текст, используйте ISNUMBER и ПОИСК в Excel. В Excel нет функции СОДЕРЖИТ.

18 PMT: функция PMT в Excel рассчитывает платеж по кредиту на основе постоянных платежей и постоянной процентной ставки. Эта страница содержит много простых примеров PMT.

19 Вычислить возраст: Чтобы вычислить возраст человека в Excel, используйте РАЗНДАТ и СЕГОДНЯ. Функция DATEDIF имеет три аргумента.

20 Косвенный: используйте функцию ДВССЫЛ в Excel для преобразования текстовой строки в допустимую ссылку. Вы можете использовать оператор & для создания текстовых строк.

21 CAGR: в Excel нет функции CAGR. Однако просто используйте функцию RRI в Excel, чтобы рассчитать совокупный годовой темп роста (CAGR) инвестиций за период лет.

22 Если ячейка пуста: используйте функцию ЕСЛИ и пустую строку в Excel, чтобы проверить, пуста ли ячейка. Используйте IF и ISBLANK для получения точно такого же результата.

23 Среднее: Функция СРЗНАЧ в Excel вычисляет среднее (среднее арифметическое) группы чисел.

24 Подстрока: в Excel нет функции ПОДСТРОКА. Используйте MID, LEFT, RIGHT, FIND, LEN, SUBSTITUTE, REPT, TRIM и MAX в Excel для извлечения подстрок.

25 Сумма: используйте функцию СУММ в Excel для суммирования диапазона ячеек, всего столбца или несмежных ячеек.

26 Удалить пробелы: Функция TRIM в Excel удаляет начальные, лишние и конечные пробелы. Используйте функцию ПОДСТАВИТЬ, чтобы удалить все пробелы или неразрывные пробелы.

27 Операторы сравнения: используйте операторы сравнения в Excel, чтобы проверить, равны ли два значения друг другу, если одно значение больше другого значения и т. д.

28 ЧПС: правильная формула ЧПС в Excel использует функцию ЧПС для расчета текущую стоимость ряда будущих денежных потоков и вычитает первоначальные инвестиции.

29 Среднееесли: Функция СРЗНАЧЕСЛИ в Excel вычисляет среднее значение ячеек, соответствующих одному критерию. СРЗНАЧЕСЛИМН вычисляет среднее значение ячеек, соответствующих нескольким критериям.

30 XLOOKUP: если у вас Excel 365 или Excel 2021, используйте XLOOKUP вместо VLOOKUP. Функция XLOOKUP проще в использовании и имеет некоторые дополнительные преимущества.

31 Гиперссылки: Используйте диалоговое окно «Вставить гиперссылку» в Excel, чтобы создать гиперссылку на существующий файл, веб-страницу или место в этом документе. Вы также можете использовать функцию ГИПЕРССЫЛКА.

32 Прогноз: функция ПРОГНОЗ.ЛИНЕЙНЫЙ в Excel прогнозирует будущее значение по линейному тренду. Функция FORECAST.ETS в Excel прогнозирует будущее значение с помощью экспоненциального тройного сглаживания, которое учитывает сезонность.

33 Добавить или вычесть время: Используйте функцию ВРЕМЯ в Excel, чтобы добавить или вычесть часы, минуты и секунды. Чтобы сложить время в Excel, просто используйте функцию СУММ.

34 Копирование формулы: Просто используйте CTRL + c и CTRL + v, чтобы скопировать и вставить формулу в Excel. Используйте маркер заполнения в Excel, чтобы быстро скопировать формулу в другие ячейки.

35 Разница во времени: Вычисление разницы между двумя значениями времени в Excel может оказаться сложной задачей. Время обрабатывается внутри как числа от 0 до 1.

36 IRR: используйте функцию IRR в Excel для расчета внутренней нормы прибыли проекта. Внутренняя норма доходности – это ставка дисконтирования, при которой чистая текущая стоимость равна нулю.

37 Не равно: В Excel означает не равно. Оператор в Excel проверяет, не равны ли два значения друг другу. Давайте рассмотрим несколько примеров.

38 Сегодняшняя дата: Чтобы ввести сегодняшнюю дату в Excel, используйте функцию СЕГОДНЯ. Чтобы ввести текущую дату и время, используйте функцию СЕЙЧАС.

39 Абсолютная ссылка: Абсолютная ссылка в Excel относится к фиксированному местоположению на рабочем листе. При копировании формулы абсолютная ссылка никогда не меняется.

40 Промежуточный итог: В этом примере показано, как создать промежуточный итог (кумулятивную сумму) в Excel. Промежуточный итог изменяется каждый раз, когда в список добавляются новые данные.

Посмотреть все 300 примеров.

10 функций Excel, которые должен знать каждый

Узнать на испанском языке

Ler em português

За последние 10 лет появилось множество инновационных программ для работы с большими данными, предназначенных для анализа, обработки и визуализации данных. Тем не менее, для обычного работника умственного труда Microsoft Excel спустя 30 лет остается незаменимым продуктом для тех, кто хочет разобраться в данных. Сатья Наделла, генеральный директор Microsoft, утверждает, что Excel по-прежнему является единственным продуктом Microsoft, который стоит выше остальных, и 750 миллионов работников умственного труда во всем мире ежедневно поддерживают это утверждение.

Мы обучаем и тестируем Microsoft Excel уже десять лет, и проведенный нами опрос нескольких сотен офисных сотрудников показал, что мы тратим более 10% своей рабочей жизни на работу с электронными таблицами; для тех, кто занимается исследованиями и разработками или финансами, это больше похоже на 30% или 2,5 часа в день.

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

Масштабирование навыков работы с данными вашей команды

Помогите своим сотрудникам лучше разбираться в данных.

В прошлом году мы составили «100 самых полезных советов по Excel», для которых мы проконсультировались с экспертами по Excel и изучили десятки тысяч результатов тестов и данных об использовании курсов. Хотя у каждой функции Excel есть вариант использования, ни один человек не использует каждую функцию Excel. Отбросьте более 500 функций, и у вас останется около 100 действительно полезных функций и функций для большинства современных работников умственного труда. Мы выбрали 10 из этого списка, которые особенно легко освоить (всего около двух часов) и которые могут существенно повлиять на производительность. Эти 10 перечислены в порядке убывания полезности из нашего списка 100 лучших. Если, как и многие, вы застряли на том, что дальше изучать в Excel, вы можете взглянуть на эту матрицу 2 × 2, которая учитывает полезность и время, необходимое для изучения навыка.

[  1  ]

Специальная паста (10 минут на изучение)

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

На устранение этих мелких неприятностей может потребоваться время, поэтому Специальная паста такая… особенная. Специальная вставка позволяет вам выбрать, какие элементы скопированной ячейки вы переносите. После того, как вы скопировали свою ячейку ( Ctrl+C ), нажмите Ctrl+Alt+V (или перейдите в раздел Буфер обмена на главной ленте или Правка > Специальная вставка ), чтобы вызвать Специальную вставку и сделать выбор. Alt+E+S+V — это ярлык для простой вставки значений — вероятно, наиболее распространенное использование специальной вставки.

[  2  ]

Добавить несколько строк (2 минуты на обучение)

Нам часто нужно добавлять новые строки между существующими строками. Ярлык ( Ctrl+Shift++ [знак плюс]) очень удобен, тем более что вы можете переключать + для добавления нескольких строк. Часто просто выделяя количество строк, которые вы хотите добавить (скажем, пять), и используя щелчок правой кнопкой мыши + вставка быстрее при массовом добавлении, так как добавляется количество выделенных вами строк.

[  3  ]

Быстрое заполнение (30 минут на изучение)

С помощью этой функции в 2013 году Excel разработал собственный ум. Flash Fill автоматически заполняет ваши данные, когда обнаруживает закономерность.

Предположим, у вас есть список номеров продуктов в первых 10 ячейках столбца A, например, от «ABC-00001» до «ABC-00010», и вам нужны только номера после «-». Вы можете легко отказаться от «ABC», используя Flash Fill. До 2013 года это было возможно, но зависело от комбинации функций (НАЙТИ, ВЛЕВО, & и т. д.). Теперь это намного быстрее и произведет впечатление на людей.

Установите шаблон, введя «00001» в первую пустую ячейку. Если функция Flash Fill включена ( File Options, Advanced ), просто начните вводить следующий номер продукта в ячейку ниже, и Flash Fill распознает шаблон и заполнит оставшиеся номера продуктов для вас. Просто нажмите клавишу Enter, чтобы принять. Или запустите его вручную, нажав Data > Flash Fill или Ctrl+E .

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

[  4  ]

ИНДЕКС/ПОИСКПОЗ (45 минут на изучение)

Помимо ВПР (которая ищет значение в одном столбце и возвращает соответствующее значение из другого столбца), ИНДЕКС и ПОИСКПОЗ являются наиболее широко используемыми и наиболее мощными инструментами в Excel для выполнения поиска. Используемые по отдельности, эти функции бесценны, но когда вы объединяете их, высвобождается их истинная сила. ИНДЕКС и ПОИСКПОЗ, используемые в сочетании, помогают эффективно и точно извлекать нужные данные из большого набора данных. Освоение этих функций не только сделает вас профессионалом Excel в глазах коллег и руководителя, но и сделает утомительные, рутинные задачи быстрыми и простыми. Вот как работают эти функции:

ВПР — отличная функция, но у нее есть свои ограничения. Он может искать значения только слева направо. Значение поиска должно быть слева в таблице поиска. ИНДЕКС и ПОИСКПОЗ позволяют вам искать значение в любом месте таблицы поиска независимо от его положения.

Допустим, у вас есть электронная таблица со списком продуктов. У вас есть столбцы с заголовками «Номер продукта», «Прибыль», «Название продукта» и «Доход». В другой электронной таблице у вас есть список названий продуктов, и вы хотите посмотреть, сколько прибыли принес каждый продукт. В этом сценарии мы используем название продукта (наше значение поиска) для поиска прибыли. Название продукта находится справа от прибыли, поэтому функция ВПР не работает. Это идеальный сценарий для ИНДЕКС и ПОИСКПОЗ.

Синтаксис:

= ИНДЕКС (столбец «Прибыль»), ПОИСКПОЗ (искомое значение, столбец «Название продукта»), 0

Вот хороший способ запомнить, как это работает:

= ИНДЕКС (столбец, из которого я хочу получить возвращаемое значение), ПОИСКПОЗ (мое значение поиска, столбец, который я хочу найти), введите «0» (ноль дает вам точное совпадение; вы можете сопоставить менее [- 1] или больше [1].)

На первый и даже второй взгляд ИНДЕКС и ПОИСКПОЗ выглядят сложными. Это, безусловно, требует некоторой практики, но стоит подумать, поскольку оно более гибкое и мощное, чем ВПР. Это одно из самых важных применений Excel, и точка.

[  5  ]

СУММ (2 минуты на обучение)

Это одна из первых функций, которую вы, вероятно, изучите в Excel — как суммировать строку или столбец. Но знаете ли вы, что вы можете выбрать ячейку в конце строки или столбца и нажать Alt++ [знак плюс], чтобы выполнить эту функцию за секунды?

[  6  ]

Ctrl+Z и Ctrl+Y (1 минута на изучение)

Это ваша карточка Excel для выхода из тюрьмы. Если вы не используете Ctrl+Z для отмены ошибок в Excel, то вы должны это сделать. Чего многие не знают, так это того, что Ctrl+Y делает наоборот — повторяет. Оба работают в тандеме очень хорошо, и вы можете циклически повторять свою работу, пока не найдете правильный.

[  7  ]

Удаление дубликатов (10 минут на изучение)

Это так просто и быстро в использовании. Remove Duplicates делает именно то, что вы ожидаете — удаляет дубликаты в любом заданном диапазоне данных. Наш совет — удалить значения, которые вы хотите дедупликации, и поместить их на другой лист. Это найдено на 9Вкладка 0110 Data в разделе Data Tools на ленте.

Если вы просто хотите выделить дубликаты, вы можете сделать это с помощью условного форматирования . Ярлык Alt+H+L (или найдите его на ленте Главная в разделе Стили ).

[  8  ]

Freeze Panes (15 минут на изучение)

Вы когда-нибудь прокручивали большую таблицу данных только для того, чтобы забыть, какие столбцы какие? Freeze Panes — вот ответ. Вы можете закрепить только верхнюю строку, первый столбец или любое их количество. Определите столбцы и строки области, которую вы хотите заморозить. Затем выберите ячейку сразу справа от этих столбцов и под этими строками. Перейти к Вкладка Вид и Закрепить области в разделе Окно . Alt+W+F — это ярлык.

[  9  ]

F4 (10 минут на обучение)

Есть два особенно удобных способа использования F4 в Excel. Первый — при создании абсолютной ссылки: F4 переключает различные параметры.