17 функций и 6 хитростей Excel, которые помогут упростить работу

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

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

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

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

Таблица 1. 17 полезных функций Excel

Функция

Назначение

Пример

1

СУММ

Позволяет складывать отдельные значения, диапазоны ячеек, ссылки на ячейки или данные всех этих трёх видов.

=СУММ(Число 1;Число 2;...Число n)

или

=СУММ(А1;B1;C1) — сумма значений в ячейках

2

ПРОИЗВЕД

Перемножает все числа, переданные как аргументы, и возвращает произведение.

=ПРОИЗВЕД(Число 1; Число 2;...;Число n)

или

=ПРОИЗВЕД(А1;B1;C1) — произведение значений в ячейках

3

СРЗНАЧ

Считает среднее арифметическое числовых значений.

=СРЗНАЧ(Число 1; Число 2;...;Число n)

или

=СРЗНАЧ(А1;A2;A3)

4

ЕСЛИ

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

У функции возможны два результата (ИСТИНА и ЛОЖЬ).

Функцию ЕСЛИ можно настроить сразу по нескольким критериям.

=ЕСЛИ(логическое выражение; [функция если истина]; [функция если ложь])

Например, если в таблице нужно определить значения меньше 100, то значение 96 будет отмечено функцией как истинное, а значение 125 — как ложное.

=ЕСЛИ(A1>=100;"истина";ЕСЛИ(A1<100;"ложь«))

5

СУММЕСЛИ

Суммирует значения, удовлетворяющие заданным условиям (например, все затраты из одной категории).

=СУММЕСЛИ(ячейки которые нужно проверить на условие; условие; какие ячейки складывать при удовлетворении условию)

6

СЧЁТЕСЛИ

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

= СЧЕТЕСЛИ(ячейки которые надо подсчитывать, критерий по которым ячейку принимать в расчёт)

7

СРЗНАЧЕСЛИ

Рассчитывает условное среднее значение.

=СРЗНАЧЕСЛИ(ячейки которые нужно проверить на условие; условие; для каких ячеек рассчитывать среднее при удовлетворении условию)

8

МИН

Возвращает наименьшее значение в списке аргументов.

=МИН(Число 1; Число 2;...;Число n)

или

=МИН(A2:A6)

9

МАКС

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

=МАКС(Число 1; Число 2;...;Число n)

или

=МАКС(A2:A6)

10

НАИМЕНЬШИЙ

Используется для получения минимального значения из заданного диапазона ячеек (возвращает k-ое наименьшее значение из массива данных).

В ячейках А1;A5 находятся числа 1;3;6;5;10.

=НАИМЕНЬШИЙ (A1;A5) при разных k:

k=1; результат =1 (первое наименьшее значение)

k=2; результат=2 (второе наименьшее значение)

k=3; результат=5 (третье наименьшее значение)

11

НАИБОЛЬШИЙ

Позволяет выбрать значение по его относительному местоположению (возвращает k-ое по величине значение из множества данных).

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

В ячейках А1;A5 находятся числа 1;3;6;5;10.

= НАИБОЛЬШИЙ (A1;A5) при разных k:

k=1; результат = 10 (первое наибольшее значение)

k=2; результат = 6 (второе наибольшее значение)

k=3; результат = 5 (третье наибольшее значение)

12

СЖПРОБЕЛЫ

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

=СЖПРОБЕЛЫ(адрес ячейки)

13

ЛЕВСИМВ

Возвращает заданное количество знаков из указанной строки слева.

=ЛЕВСИМВ(адрес ячейки; количество знаков)

14

ПРАВСИМВ

Возвращает заданное количество знаков из указанной строки справа.

=ПРАВСИМВ(адрес ячейки; количество знаков)

15

ПСТР

Возвращает знаки из текстовой строки, начиная с указанной позиции.

=ПСТР(адрес ячейки; начальное число; число знаков)

16

ВПР

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

Например, по номеру товара можно найти его цену или по идентификатору найти имя сотрудника.

=ВПР(искомое значение; таблица; номер столбца; тип совпадения)

Тип совпадения может быть приблизительным или точным. Для передачи точного значения в формуле нужно прописать 0 или ЛОЖЬ, для передачи приблизительных значений указывается 1 или ИСТИНА

17

Конкатенация

Позволяет объединить в одной ячейке данные из двух и более ячеек.

Самый простой способ — вставить между адресами ячеек амперсанд (&).

=ячейка 1&ячейка 2...&ячейка n (=A1&A2&A3)

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

1. Чем полезно умное форматирование

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

Методы основаны на цветном выделении ячеек в зависимости от различных критериев:

  • ранжирования по диапазону значений с помощью гистограмм\
  • сравнения с константой
  • различных значков

Благодаря динамике, данные корректируются при каждом изменении.

Рисунок 1. Условное форматирование

Условное форматирование

2. Как создать умную таблицу

Список данных в Excel можно преобразовать в умную таблицу (рисунок 2), у которой есть масса полезных функций:

  • шапка таблицы автоматически закрепляется при прокрутке, включаются кнопки фильтра для отбора и сортировки;
  • при дописывании новых столбцов и строк таблица автоматически растягивается;
  • появляется дополнительная вкладка «Конструктор» с дополнительными инструментами анализа и настроек;
  • введённые формулы автоматом копируются на весь столбец.

Рисунок 2. Умная таблица

Умная таблица

3. Как визуализировать данные с помощью спарклайнов

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

Чтобы создать спарклайн, нажмите «Вставка» → группа «Спарклайны» → кнопка «График» или «Гистограмма» (рисунок 3).

Рисунок 3. Спарклайны

Спарклайны

4. Как перенести большую формулу

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

Замените знак «=» на «!». Формула превратится в символьную строку, которая перемещается без изменений. После того как формула полностью вписана в нужную ячейку, поменяйте знак обратно на «=».

5. Как ускорить и упростить работу с помощью Power Query

Представьте, что вам нужно составлять еженедельный отчёт. Вы готовите таблицы в Excel. А исходные вы получаете в виде CSV-файлов. Нужно каждый раз искать в них только необходимую вам информацию, вставлять данные в Excel, обновляя сводные данные и графики. Всё можно сделать намного проще, воспользовавшись Power Query.

Это технология подключения к данным. С помощью Power Query можно находить, загружать, объединять, преобразовывать, обновлять, и уточнять данные из различных источников.

Надстройка умеет собирать данные из фалов почти 40 различных форматов (например, TXT, XLSX, HTML, CSV, JSON, XML). Помогает менять регистр на правильный, приводить цифры к числовому формату, заполнять пробелы, исправлять заголовки таблиц, разделять текстовые фрагменты на столбцы и склеивать их снова в единый текст, удаляет пустые столбцы и строки, выполняет многие другие полезные операции.

Power Query представлена в двух вариантах:

  • отдельным модулем, доступным для скачивания с официального сайта Microsoft (для Excel 2010-13).
  • как сервисная функция в составе редактора (рисунок 4).

В большинстве последних версий Excel надстройка находится на вкладке «Данные» → Получить и преобразовать.

Рисунок 4. Power Query

Power Query

6. Как восстановить несохранённые файлы

Даже если вы закрыли документ, забыв «согласиться» с сохранением, есть шанс восстановить данные. Вот алгоритм для разных версий Excel:

  • Excel 2010: «Файл» → «Последние» и найдите в правом нижнем углу экрана кнопку «Восстановить несохранённые книги».
  • Excel 2013: «Файл» → «Сведения» → «Управление версиями» → «Восстановить несохранённые книги».
  • Для последующих версий Excel: «Файл» → «Сведения» → «Управление книгой».

Здесь вы найдёте временные копии созданных, изменённых, но несохраненных книг.

Смотрите также:

Заказать обратный звонок

×

Сайт УЦ Финконт использует cookies. Подробнее »

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

Отключить cookies Вы можете в настройках своего браузера.

http://uat.finkont.ru/blog/17-funktsiy-i-6-khitrostey-excel-kotorye-pomogut-uprostit-rabotu