Excel: команды и функции
Команды в Excel предназначены для выполнения различных действий, таких как создание, открытие и сохранение таблицы, форматирование ячеек и диапазонов, настройка отображения данных и т.д. Команды можно найти на главной панели инструментов, в контекстных меню или с помощью горячих клавиш.
Функции – это математические, статистические или текстовые операции, которые выполняются над ячейками или диапазонами. Функции в Excel начинаются с знака «=». Примеры функций в Excel: SUM (сумма всех чисел в диапазоне), AVERAGE (среднее значение чисел в диапазоне), IF (условный оператор), и т.д. Функции имеют различные параметры и возможности настройки, чтобы обеспечить нужный результат.
Некоторые из наиболее часто используемых команд и функций в Excel включают:
— Сумма (SUM): вычисляет сумму всех чисел в диапазоне.
— Среднее значение (AVERAGE): вычисляет среднее значение чисел в диапазоне.
— Максимум (MAX): находит наибольшее число в диапазоне.
— Минимум (MIN): находит наименьшее число в диапазоне.
— Счет (COUNT): подсчитывает количество чисел или значений в диапазоне.
— Произведение (PRODUCT): вычисляет произведение всех чисел в диапазоне.
Это лишь небольшая часть команд и функций, доступных в Excel. Используя их в сочетании с другими инструментами и возможностями программы, пользователь может создавать сложные расчеты, анализировать данные, строить графики и диаграммы, и многое другое.
Знание команд и функций в Excel – необходимый инструмент для эффективной работы с программой и максимального использования ее возможностей.
ЕСЛИ
Функция ЕСЛИ является очень популярной в Excel. Она позволяет автоматически выполнять какое-либо действие, в зависимости от поставленного условия.
— Логическое выражение — выражение, которое по итогу своего вычисления должно вырнуться значение ИСТИНА или ЛОЖЬ.- Значение, если истина — устанавливаем указанное значение, если логическое выражение вернуло ИСТИНА- Значение, если ложь — устанавливает указанное значение, если логическое выражение вернуло ЛОЖЬ.
В примере выше мы хотим определить, получили ли мы за месяц выручку больше 500 рублей или нет. В формуле ЕСЛИ(B2>500;»Да»;»Нет») первый параметр (B2>500) проверяет, выручка за месяц больше 500 рублей или нет; второй параметр («Да») — функция вернет Да, если выручка больше 500 рублей и соответственно Нет (третий параметр), если выручка меньше.
Обратите внимание, что значения при истине или лжи могут быть не только текстовые, числовые, но также и функции(в том числе и ЕСЛИ), что позволяет реализовать достаточно сложные логические конструкции
ЕСЛИОШИБКА
При работе с формулами в Excel, можно время от времени сталкиваться с различными ошибками. Так в примере ниже функция ВПР вернула ошибку #Н/Д из-за того, что в базе данных по ФИО нет искомой нами фамилии (более подробно об ошибке #Н/Д вы можете прочитать в этой статье: «Как исправить ошибку #Н/Д в Excel»)
Для обработки таких ситуаций отлично подойдет функция ЕСЛИОШИБКА. Ее синтаксис следующий:
— Значение, результат которого проверяется на ошибку.- Значение, если ошибка — В случае, если в результате работы функции получаем ошибку, то выводится не ошибка, а данное значение.
В случае с нашим примером выше, мы можем предположить, что фамилия может быть некорректной, соответственно ЕСЛИОШИБКА вернет нам предупреждение, что бы мы проверили написание фамилии.
Как сделать ВПР в Excel: понятная пошаговая инструкция.
Для начала на простом примере разберем, как работает функция ВПР в Excel. Предположим, у нас есть две таблицы. Первая – это прайс-лист с наименованиями и ценами. Вторая – это заказ на покупку некоторых из этих товаров. Искать в прайс листе нужный товар и руками вписывать в заказ его цену – занятие очень утомительное. Ведь прайс с ценами может насчитывать сотни строк. Нам необходимо сделать всё автоматически.
Нам необходимо обнаружить интересующее нас наименование в первом столбце и возвратить (то есть показать в ответ на наш запрос) содержимое из желаемого столбца той же строки, где находится наименование.
Наш прайс-лист расположен в столбцах А и В. Список покупок – в E-H. Допустим, первая позиция в списке покупок – бананы. Нам нужно в столбце A, где указаны все наименования, найти этот товар, затем его цену поместить в ячейку G2.
Для этого в G2 запишем следующую формулу:
А теперь разберем подробно, как сделать ВПР.
Мы берем значение из E2.
Ищем точное совпадение (поскольку четвертым параметром указан 0) в диапазоне $A$2:$B$7 в первой его колонке (крайней левой)
Обратите внимание, что лучше сразу же использовать абсолютные ссылки на прайс-лист, чтобы при копировании этой формулы ссылка не «соскользнула».
Если товар будет найден, то нужно перейти во второй столбец диапазона (на это указывает третий параметр = 2).
Взять из него цену и вставить ее в нашу ячейку G2.
Получилось? Теперь просто скопируйте формулу из G2 в G3:G8.
Отчет о продажах готов.
Также чтобы понять, что такое точное совпадение, попробуйте в A5 или в E2 изменить наименование товара. К примеру, добавьте пробел в конце. Внешне ничего не изменилось, но вы сразу же получите ошибку #Н/Д. То есть, товар не был обнаружен. В то же время, таких случайных ошибок можно легко избежать, о чем мы поговорим отдельно.
Особо остановимся на четвертом параметре. Мы указали ноль (можно было написать ЛОЖЬ), что означает «точный поиск». А что, если забыть его указать и закончить номером столбца, из которого извлекаются нужные данные?
Давайте еще раз шаг за шагом разберем, что в этом случае будет происходить.
- Берем значение из E2.
- Начинаем его искать в крайней левой колонке диапазона $A$2:$B$7, то есть в столбце A. Поскольку в A2 совпадение не найдено, смотрим дальше: что находится ниже.
- Там обнаруживаем товар «Сливы». При этом предполагается, что наш список отсортирован по алфавиту. Ведь именно это – главное условие поиска приблизительного совпадения.
- Поскольку в сортированном списке «сливы» находятся ниже, чем «бананы», то функция принимает решение, что дальше искать слово, начинающееся на «Б» нет смысла. Процесс можно остановить. И остаться на букве «А». То есть, там и находится наиболее близкое значение.
- Поскольку поиск завершен, переходим из A2 во второй столбец, то есть в B. Вставляем данные из B2 в G2 как результат вычислений.
К сожалению, «бананы» были в нашем прайс-листе ниже, но до них просто «не дошел ход». И в список покупок теперь записана неправильная цена.
При помощи этой инструкции мы рассмотрели только основы. А как реально этим можно пользоваться?
Описание команд Excel
1. Функция SUM
Команда SUM используется для суммирования чисел в ячейках. Вы можете указать диапазон ячеек или же перечислить их в аргументе функции.
Пример использования:
=SUM(A1:A10) — суммирует числа в диапазоне ячеек от A1 до A10.
2. Функция AVERAGE
Команда AVERAGE вычисляет среднее значение чисел в указанном диапазоне ячеек.
Пример использования:
=AVERAGE(B1:B5) — вычисляет среднее значение чисел в диапазоне ячеек от B1 до B5.
3. Функция COUNT
Команда COUNT подсчитывает количество чисел в указанном диапазоне ячеек.
Пример использования:
=COUNT(C1:C10) — подсчитывает количество чисел в диапазоне ячеек от C1 до C10.
4. Команда IF
Команда IF позволяет проверять условие и выполнять определенные операции в зависимости от результата проверки.
Пример использования:
=IF(D1>10, «Больше 10», «Меньше или равно 10») — если значение в ячейке D1 больше 10, то возвращается текст «Больше 10», иначе возвращается текст «Меньше или равно 10».
5. Команда VLOOKUP
Команда VLOOKUP используется для поиска значения в указанном столбце и возврата соответствующего значения из другого столбца в этой же строке.
Пример использования:
=VLOOKUP(E1, A1:B10, 2, FALSE) — ищет значение в столбце A1:B10, возвращает значение из столбца B, соответствующее найденному значению в ячейке E1.
6. Команда CONCATENATE
Команда CONCATENATE используется для объединения строк или значений в ячейках. Вы можете указать строки или ячейки как аргументы функции.
Пример использования:
=CONCATENATE(«Hello», » «, «World») — объединяет строки «Hello», » » и «World», возвращая результат «Hello World».
Описание функций Excel
В Excel доступно множество функций, которые помогают в обработке данных и создании сложных формул. В этом разделе мы рассмотрим некоторые из наиболее популярных функций Excel.
Функция SUM: эта функция используется для суммирования значений в выбранном диапазоне ячеек. Например, если вы хотите найти сумму чисел в ячейках A1, A2 и A3, то формула будет выглядеть так: =SUM(A1:A3).
Функция AVERAGE: данная функция вычисляет среднее значение чисел в выбранном диапазоне ячеек. Например, если вы хотите найти среднее значение чисел в ячейках A1, A2 и A3, то формула будет выглядеть так: =AVERAGE(A1:A3).
Функция COUNT: эта функция подсчитывает количество ячеек в выбранном диапазоне, содержащих числовые значения. Например, если вы хотите узнать, сколько ячеек с числовыми значениями содержится в диапазоне A1:A5, то формула будет выглядеть так: =COUNT(A1:A5).
Функция MAX: данная функция находит максимальное значение в выбранном диапазоне ячеек. Например, если вы хотите найти максимальное значение в ячейках A1, A2 и A3, то формула будет выглядеть так: =MAX(A1:A3).
Функция MIN: эта функция находит минимальное значение в выбранном диапазоне ячеек. Например, если вы хотите найти минимальное значение в ячейках A1, A2 и A3, то формула будет выглядеть так: =MIN(A1:A3).
Функция IF: данная функция позволяет выполнять условные вычисления на основе заданных условий. Например, если вы хотите проверить, является ли значение в ячейке A1 больше или меньше некоторого заданного значения, и выполнить определенное действие в зависимости от этого, то формула будет выглядеть так: =IF(A1>10, «Больше 10», «Меньше или равно 10»).
Это лишь небольшой перечень функций, доступных в Excel. Вы можете использовать эти функции в сочетании с другими функциями и операторами для создания более сложных вычислительных формул.
Интерфейс
Настраиваем панель быстрого доступа
Начнем с самого простого — добавления самых часто используемых опций на панель быстрого доступа. Чтобы сделать это, заходите в параметры Excel — «Настроить ленту» — и ищите в параметрах «Панель быстрого доступа».
Опции, перенесенные на панель быстрого доступа, будут доступны при работе со всеми вашими книгами Excel (хотя можно ее настроить и отдельно для любой книги). Так что если пользуетесь какими-то командами и инструментами постоянно — добавляйте их туда.
Другой вариант — просто щелкнуть по инструменту на ленте правой кнопкой мыши и нажать «Добавить…»:
Перемещаемся по ленте без мышки
Нажмите на Alt. На ленте инструментов появились цифры и буквы — у каждого инструмента на панели быстрого доступа и у каждой вкладки на ленте соответственно:
Нажмите на клавиатуре любую из букв — попадете на соответствующую вкладку на ленте, а там каждый инструмент в свою очередь тоже будет подписан. Так можно быстро вызвать нужные опции, не трогая мышку.
Поиск и подстановка значений
Функция ВПР / VLOOKUP
Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — «подтянуть» данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции).
=ВПР (что ищем; таблица с данными, где «что ищем» должно быть в первом столбце; номер столбца таблицы, из которого нужны данные; )
У нее есть два режима работы: интервальный просмотр и точный поиск.
Интервальный просмотр — это поиск интервала, в который попадает число. Если у вас прогрессивная шкала налога или скидок, нужно конвертировать оценку из одной системы в другую и так далее — используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).
В большинстве случаев мы связываем таблицы по текстовым ключам — в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» равным нулю (или ЛОЖЬ). Только тогда функция будет корректно работать с текстовыми значениями.
Функции ПОИСКПОЗ / MATCH и ИНДЕКС / INDEX
У ВПР есть существенный недостаток: ключ (искомое значение) обязан быть в первом столбце таблицы с данными. Все, что левее этого столбца, через ВПР «подтянуть» невозможно.
В реальных условиях структура таблиц бывает разной и не всегда возможно изменить порядок столбцов
Поэтому важно уметь работать с любой структурой
Функция ПОИСКПОЗ / MATCH определяет порядковый номер значения в диапазоне. Ее синтаксис:
=ПОИСКПОЗ (что ищем; где ищем ; 0)
На выходе — число (номер строки или столбца в рамках диапазона, в котором находится искомое значение).
ИНДЕКС / INDEX выполняет другую задачу — возвращает элемент по его номеру.
=ИНДЕКС(диапазон, из которого нужны данные; порядковый номер элемента)
Соответственно, мы можем определить номер строки, в котором находится искомое значение, с помощью ПОИСКПОЗ. А затем подставить этот номер в ИНДЕКС на место второго аргумента, чтобы получить данные из любого нужного нам столбца.
Получается следующая конструкция:
=ИНДЕКС(диапазон, из которого нужны данные; ПОИСКПОЗ (что ищем; где ищем ; 0))
СУММЕСЛИМН
Функция СУММЕСЛИМН позволяет суммировать значения по определенным условиям. Условий может быть несколько. В Excel также есть функция СУММЕСЛИ, которая позволяет суммировать по одному критерию. Призываю вас использовать более универсальную формулу.
— Диапазон суммирования — область листа Эксель, из которой мы суммируем данные- Диапазон условия 1 — Диапазон ячеек, которые мы проверяем на соответствие условию- Условие 1 — Условие, которое проверяется на соответствие в Диапазоне 1.Обратите внимание, что диапазонов условий и соответственно условий может быть столько, сколько вам нужно. Для примера выше мы хотим получит выручку, которую принес нам Петров в городе Москва
Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 — диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 — диапазон с фамилиями, которые мы будем проверять; Е2 — ссылка на конкретную фамилию; B2:B13 — ссылка на диапазон с городами; F2 — ссылка на конкретный город
Для примера выше мы хотим получит выручку, которую принес нам Петров в городе Москва. Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 — диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 — диапазон с фамилиями, которые мы будем проверять; Е2 — ссылка на конкретную фамилию; B2:B13 — ссылка на диапазон с городами; F2 — ссылка на конкретный город.
Более подробно о функциях СУММЕСЛИМН и СУММЕСЛИ рассказано в статье «СУММЕСЛИ и СУММЕСЛИМН в Excel».
Проверка ошибок
Проверка данных позволяет избежать ошибок при вводе информации в ячейки.
Какие бывают типовые ошибки в Excel?
- Текст вместо чисел
- Отрицательные числа там, где их быть не может
- Числа с дробной частью там, где должны быть целые
- Текст вместо даты
- Разные варианты написания одного и того же значения. Например, сокращения («ЭБ» вместо «Электронная библиотека»), лишние пробелы в конце текстового значения или между словами — всего этого достаточно, чтобы превратить текстовые значения в разные и, соответственно, чтобы они обрабатывались Excel некорректно.
Инструмент проверки данных
Чтобы использовать инструмент проверки данных, нужно выделить ячейки, к которым хотите его применить, выбрать на ленте «Данные» → «Проверка данных» и настроить параметры проверки в диалоговом окне:
Если в графе «Сообщение об ошибке» вы выбрали вариант «Остановка», то после проверки в ячейки нельзя будет ввести значения, не соответствующие заданному правилу.
Если же вы выбрали «Предупреждение» или «Сообщение», то при попытке ввести неверные данные будет появляться предупреждение, но его можно будет проигнорировать и все равно ввести что угодно.
Еще неверные данные можно обвести, чтобы точно увидеть, где есть ошибки:
Удаление пробелов
Для удаления лишних пробелов (в начале, в конце и всех кроме одного между слов) используйте функцию СЖПРОБЕЛЫ / TRIM. Ее единственный аргумент — текст (ссылка на ячейку с текстом, как правило).
Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите:
