Уроки Excel: главные функции, о которых должен знать каждый
Собрали в одном месте 15 статей и видео об инструментах Excel, которые ускорят и упростят работу с электронными таблицами.
Иллюстрация: Meery Mary для Skillbox Media
Excel — универсальный софт для работы с электронными таблицами. Он одинаково хорош как для составления примитивных отчётов или графиков, так и для глубокого анализа больших объёмов информации.
Функции Excel позволяют делать всё, что может понадобиться в работе с таблицами: объединять ячейки, переносить данные с одного листа на другой, закреплять строки и столбцы, делать выпадающие списки и так далее. Они значительно упрощают работу с данными, поэтому применять их должны уметь все.
В Skillbox Media есть серия инструкций по работе с Excel. В этом материале — подборка главных возможностей программы и ссылки на подробные руководства с примерами и скриншотами.
- Как ввести и оформить данные
- Как работать с формулами и функциями
- Как объединить ячейки и данные в них
- Как округлить числа
- Как закрепить строки и столбцы
- Как создать и настроить диаграммы
- Как посчитать проценты
- Как установить обычный и расширенный фильтр
- Как сделать сортировку
- Как сделать выпадающий список
- Как пользоваться массивами
- Как использовать функцию ЕСЛИ
- Как использовать поисковые функции
- Как делать сводные таблицы
- Как делать макросы
- Как узнать больше о работе в Excel
Как ввести и оформить данные
С этого видеоурока стоит начать знакомство с Excel. В нём сертифицированный тренер по Microsoft Office Ренат Шагабутдинов показывает:
- какие есть способы и инструменты для ввода данных в Excel;
- как копировать, переносить и удалять данные;
- как настраивать форматы и стили таблиц;
- как создавать пользовательские форматы, чтобы таблицы становились нагляднее.
Как работать с формулами и функциями
Формулы в Excel — выражения, с помощью которых проводят расчёты со значениями на листе. Пользователи вводят их в ячейки таблицы вручную. Чаще всего их используют для простых вычислений.
Функции в Excel — заранее созданные формулы, которые проводят вычисления по заданным значениям и в указанном порядке. Они позволяют выполнять как простые, так и сложные расчёты.
На тему работы с формулами и таблицами тоже есть видеоурок. В нём Ренат Шагабутдинов показывает:
- как проводить расчёты с помощью стандартных формул и функций;
- как создавать формулы с абсолютными и относительными ссылками;
- как находить ошибки в формулах.
Как объединить ячейки и данные в них
Функция объединения позволяет из нескольких ячеек сделать одну. Она пригодится в двух случаях:
- когда нужно отформатировать таблицу — например, оформить шапку или убрать лишние пустые ячейки;
- когда нужно объединить данные таблицы — например, сделать одну ячейку из нескольких и при этом сохранить всю информацию в них.
- Кнопка «Объединить» — когда нужно сделать шапку в таблице.
- Функция СЦЕПИТЬ — когда нужно собрать данные из нескольких ячеек в одну.
- Функция СЦЕП — когда нужно собрать данные из большого диапазона.
- Функция ОБЪЕДИНИТЬ — когда нужно собрать данные из большого диапазона и автоматически разделить их пробелами.
Как округлить числа
Округление необходимо, когда точность чисел не важна, а в округлённом виде они воспринимаются проще.
В Excel округлить числа можно четырьмя способами:
- Округление через изменение формата ячейки — когда нужно округлить число только визуально.
- Функция ОКРУГЛ — когда нужно округлить число по правилам математики.
- Функции ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ — когда нужно самостоятельно выбрать, в какую сторону округлить число.
- Функция ОКРУГЛТ — когда нужно округлить число с заданной точностью.
В статье показали, как применять эти способы округления.
Как закрепить строки и столбцы
Закрепление областей таблицы полезно, когда все данные не помещаются на экране, а при прокрутке теряются названия столбцов и строк. После закрепления необходимые области всегда остаются на виду.
Опция «замораживает» первую строку таблицы, первый столбец или несколько столбцов и строк одновременно. В этой статье Skillbox Media мы подробно разбирали, как это сделать.
Как создать и настроить диаграммы
Диаграммы используют для графического отображения данных таблиц. Также с помощью них показывают зависимости между этими данными. Сложная информация, представленная в виде диаграмм, воспринимается проще: можно расставлять нужные акценты и дополнительно детализировать данные.
- для чего подойдёт круговая диаграмма и как её построить;
- как показать данные круговой диаграммы в процентах;
- для чего подойдут линейчатая диаграмма и гистограмма, как их построить и как поменять в них акценты;
- как форматировать готовую диаграмму — добавить оси, название, дополнительные элементы;
- как изменить данные диаграммы.
Обучение в Skillbox
- Курс «Excel + Google Таблицы с нуля до PRO»
- Бесплатный онлайн-интенсив «Экспресс-курс по Excel: осваиваем таблицы с нуля за 3 дня»
- Бесплатные видеоуроки по Excel и «Google Таблицам»
Как посчитать проценты
В этой статье Skillbox Media подробно рассказывали о четырёх популярных способах расчёта процентов в Excel:
- как рассчитать процент от числа — когда нужно найти процент одного значения в общей сумме;
- как отнять процент от числа или прибавить процент к числу — когда нужно рассчитать, как изменятся числа после уменьшения или увеличения на заданный процент;
- как рассчитать разницу между числами в процентах — когда нужно понять, на сколько процентов увеличилось или уменьшилось число;
- как рассчитать число по проценту и значению — когда нужно определить, какое значение будет у процента от заданного числа.
Как установить обычный и расширенный фильтр
Фильтры в таблицах используют, чтобы из большого количества строк отобразить только нужные. В отфильтрованной таблице показаны данные, которые соответствуют критериям, заданным пользователем. Ненужная информация скрыта.
В этой статье Skillbox Media на примерах показали:
- как установить фильтр по одному критерию;
- как установить несколько фильтров одновременно и отфильтровать таблицу по заданному условию;
- для чего нужен расширенный фильтр и как им пользоваться;
- как очистить фильтры таблицы.
Как сделать сортировку
Сортировку в Excel настраивают, когда информацию нужно отобразить в определённом порядке. Например, по возрастанию или убыванию чисел, по алфавиту или по любым пользовательским критериям.
- как сделать сортировку данных по одному критерию;
- как сделать сортировку по нескольким критериям;
- как настроить пользовательскую сортировку.
Как сделать выпадающий список
Выпадающий список в Excel позволяет выбирать значение ячейки таблицы из перечня, подготовленного заранее. Эта функция пригодится, когда нужно много раз вводить повторяющиеся параметры — например, фамилии сотрудников или наименования товаров.
В статье дали пошаговую инструкцию по созданию выпадающих списков — на примере каталога авто.
Как пользоваться массивами
Массивы в Excel — данные из двух и более смежных ячеек таблицы, которые используют в расчётах как единую группу, одновременно. Это делает работу с большими диапазонами значений более удобной и быстрой.
С помощью массивов можно проводить расчёты не поочерёдно с каждой ячейкой диапазона, а со всем диапазоном одновременно. Или создать формулу, которая одним действием выполнит сразу несколько расчётов с любым количеством ячеек.
В статье показали, как выполнить базовые операции с помощью формул массивов и операторов Excel:
- построчно перемножить значения двух столбцов;
- умножить одно значение сразу на весь столбец;
- выполнить сразу два действия одной формулой;
- поменять местами столбцы и строки таблицы.
Как использовать функцию ЕСЛИ
ЕСЛИ — логическая функция Excel. С помощью неё проверяют, выполняются ли заданные условия в выбранном диапазоне таблицы.
Это может быть удобно, например, при работе с каталогами. Пользователь указывает критерий, который нужно проверить, — функция сравнивает этот критерий с данными в ячейках таблицы и выдаёт результат.
В статье Skillbox Media подробнее рассказали о том, как работает и для чего нужна функция ЕСЛИ в Excel. На примерах показали, как запустить функцию ЕСЛИ с одним или несколькими условиями.
Как использовать поисковые функции и функции для расчётов с условиями
Поисковые функции в Excel нужны, чтобы ускорить работу с большими объёмами данных. С их помощью значения находят в одной таблице и переносят в другую. Не нужно, например, самостоятельно сопоставлять и переносить сотни наименований, функция делает это автоматически.
В этой статье Skillbox Media разобрали, для чего нужна функция ВПР и когда её используют. Также показали на примере, как её применять пошагово.
В видеоуроке ниже Ренат Шагабутдинов показывает, как работают другие поисковые функции Excel — ПОИСКПОЗ и ПРОСМОТРX. А также учит пользоваться функциями для расчётов с условиями — СЧЁТ, СУММ, СРЗНАЧ, ИНДЕКС.
Как сделать сводные таблицы
Сводные таблицы — инструмент для анализа данных в Excel. Сводные таблицы собирают информацию из обычных таблиц, обрабатывают её, группируют в блоки, проводят необходимые вычисления и показывают итог в виде наглядного отчёта.
С помощью сводных таблиц можно систематизировать тысячи строк и преобразовать их в отчёт за несколько минут. Все параметры этого отчёта пользователь настраивает под себя и свои потребности.
В статье дали пошаговую инструкцию по созданию сводных таблиц с примером и скриншотами. Также на эту тему есть бесплатный видеоурок.
Как делать макросы
Макрос в Excel — алгоритм действий, записанный в одну команду. С помощью макросов можно выполнить несколько шагов в Excel, нажав на одну кнопку в меню или на сочетание клавиш.
Макросы используют для автоматизации рутинных задач. Вместо того чтобы совершать несколько повторяющихся действий, пользователь записывает ход их выполнения в одну команду — и запускает её, когда нужно выполнить все эти действия снова.
В статье дали инструкцию по работе с макросами для новичков. В ней подробно рассказали, для чего нужны макросы и как они работают. А также показали, как записать и запустить макрос.
Excel для начинающих: 10 базовых функций программы
В Excel есть тысячи встроенных функций. Если знать хотя бы часть из них, можно здорово экономить время при обработке данных и построении отчетов.
В этой статье собрали топ-10 базовых функций, которые чаще всего используются в Эксель.
#1. СУММ
число1 — обязательный аргумент.
Функция дает возможность найти сумму отдельных числовых значений, диапазонов, ссылок на ячейки с числовыми значениями или сумму всех этих 3-х видов. Часто используется в подбивании итоговых значений строк или столбцов при формировании отчетов.
Примеры использования
Допустим, у вас есть массив числовых значений и вам нужно посчитать сумму некоторых из них. Используя функцию СУММ, в области аргументов функции мы подставляем нужные ссылки на ячейки и получаем ответ — 312.
В кое-каких случаях в массивах не находятся значения, которые нужно так же просуммировать, и вместо ссылок можем добавить свои числа. Ответ в этом случае — 419.
Так как функция работает не только с числовыми значениями, а и с целыми диапазонами, можно найти сумму всего диапазона.
Можно не ограничивать себя в количестве значений, которые нужно просуммировать, а посчитать все значения в столбцах или строках. К примеру, просуммируем все значения в первых двух столбцах.
Если в одной или нескольких ячейках диапазона окажется не числовое выражение, а текстовое, Excel будет приравнивать эти значения к нулю.
#2. СЧЁТ
значение1 — обязательный аргумент.
С помощью этой функции можно подсчитать ячейки, которые содержат только числовые значения в списке аргументов. Зачастую используется в расчетах средних значений, когда использование функции СРЗНАЧ в Excel нецелесообразно.
Следующая формула возвращает количество ячеек в диапазоне A1:E4, которые содержат числа.
Как мы видим, в нашем списке аргументов диапазон из пяти значений, но функция вернет три, ведь числовые значения содержатся только в столбцах A, B, C. В столбце D — текстовое выражение, а Е — незаполненная ячейка.
Близкие по применению функции:
=СЧЁТЗ(значение1;[значение2];…) — считает количество непустых значений в перечне аргументов.
=СЧИТАТЬПУСТОТЫ(диапазон) — считает количество пустых значений в указанном диапазоне.
#3. МИН
число1 — обязательный аргумент.
Функция позволяет найти минимальное числовое значение в указанном списке аргументов. Часто используется при построении финансовой отчетности, когда нужно определить дату начала периода отчета, минимальный чек покупки и другие параметры.
Пример
Допустим, у нас есть диапазон чисел и текстовых выражений, и нужно найти минимальное значение.
Например, минимальное значение среди двух выбранных диапазонов A1:D2, A4:D4 и числа 54 будет 21. Пустые поля и текстовые выражения функцией исключаются и в расчетах не используются.
Близкие по назначению функции
=МИНА(значение1;[значение2];…) — находит минимальное значение в списке аргументов, при этом текстовые и ложные логические выражения равняются к нулю, а логическое выражение "ИСТИНА" в ячейке равняется 1.
=МАКС(число1;[число2];…) — находит максимальное значение в списке аргументов, при этом текстовые и пустые выражения игнорируются.
=МАКСА(значение1;[значение2];…) — находит максимальное значение в списке аргументов, при этом текстовые и ложные логические выражения приравниваются к нулю, а логическое выражение "ИСТИНА" в ячейке равняется 1.
#4. СРЗНАЧ
число1 — обязательный аргумент.
С помощью этой функции можно найти среднее арифметическое отдельных числовых значений, диапазонов, ссылок на ячейки с числовыми значениями или же среднее этих 3-х видов. Вычисляется путем суммирования всех чисел и делением суммы на количество этих же чисел. Текстовые и логические значения в диапазоне игнорируются.
Допустим, у нас есть диапазон из 6 ячеек: 4 из них заполнены числами, включая 0, одно значение — текстовое и еще одно — пустое. Функция просуммирует только числовые и поделит сумму на общее количество числовых — 4.
В результате мы получим среднее, равное 4. Давайте проверим формулой:
(4 + 5 + ТЕКСТ + 7 + 0 + ПУСТОЕ) / 4 = 16 / 4 = 4
ТЕКСТ и ПУСТОЕ игнорируются.
#5. ОКРУГЛ
Синтаксис: =ОКРУГЛ(число; число_разрядов)
число — аргумент.
число_разрядов — до какого разряда округляется число.
Функция ОКРУГЛ применяется для округления действительных чисел до требуемого количества знаков после запятой и возвращает округленное значение согласно математическому правилу округления.
К примеру, для округления числа 2,57525 до 2-х символов после запятой можно ввести формулу =ОКРУГЛ(2,57525;2), которая вернет значение 2,58. Эта функция часто используется при построении балансовых и других видов отчетности.
«Excel для финансов»
Алексей Вощак
Ex-Partner at Bridges Consulting
«Excel для бизнеса»
Алексей Вощак
Ex-Partner at Bridges Consulting
#6. ЕСЛИ
Синтаксис: =ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)
логическое_выражение — условие, выполнение которого проверяет оператор.
значение_если_истина — в случае, если условие окажется правдивым, будет возвращено это значение.
значение_если_ложь — в случае, если условие окажется неправдивым, будет возвращено это значение.
Эта функция одна из самых известных в работе с Эксель. В ней проверяются числа и/или текст, функции, формулы. Когда значения отвечают заданному условию, появляется запись из поля "значение_если_истина", не отвечают — "значение_если_ложь". Часто используется для распределения выражений на категории, группы.
Функция поддерживает использование операторов сравнения: = (равно), < (меньше), <= (меньше или равно), > (больше), >= (больше или равно), <> (не равно). Также часто используют эту функцию в связке с логическими операторами И, ИЛИ. Рассмотрим несколько примеров.
Пример 1
Предположим, что у нас есть таблица стоимости продуктов питания за 1 кг (л), и задача заключается в том, чтобы разбить товары на две группы в зависимости от цены:
— до 500
— больше 500
Для решения этой задачи используется функция ЕСЛИ.
В ячейке E2 следующая формула: =ЕСЛИ(D2<=500;"до 500";"больше 500"). Она расшифровывается так: если в указанной ячейке число будет меньше или равно 500 (1 на скриншоте), тогда функция должна вернуть текстовое выражение "до 500" (2 на скриншоте), если оно окажется другим — "больше 500" (3 на скриншоте).
Протянем эту формулу вниз и проверим работоспособность.
Пример 2
Предположим, что в таблице выше нам необходимо каждому товару присвоить категорию по цене: "высокая стоимость" и "низкая стоимость".
В ячейке F2 была прописана следующая формула: =ЕСЛИ(E2="до 500";"низкая стоимость";"высокая стоимость"). Расшифровка: если в указанной ячейке текстовое значение равняется "до 500", тогда функция должна вернуть текстовое выражение "низкая стоимость", если оно окажется другим — "высокая стоимость".
Обратите внимание: когда проверяете условия на текстовое значение, это значение в кавычках необходимо брать в еще одни кавычки. Посмотрим на результат, протянув формулу до конца таблицы.
#7. ВПР
Синтаксис: =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
искомое_значение — значение, которое необходимо найти в столбце с данными. Аргументы бывают числовыми и текстовыми. Искомое значение должно находиться в крайнем левом столбце диапазона ячеек указанной таблицы.
таблица — ссылка на диапазон ячеек. В левом столбце выполняется поиск искомого значения, а из столбцов правее выводится соответствующее значение. Левый столбец еще называют ключевым. Если в таблице не окажется искомого значения, вернется ошибка #Н/Д.
номер_столбца — номер столбца таблицы, согласно которому нужно вывести результат.
[интервальный_просмотр] — необязательный аргумент. Принимает два значения: ИСТИНА и ЛОЖЬ. ИСТИНА устанавливается по умолчанию, и функция предполагает, что левый столбец таблицы отсортирован по возрастанию в алфавитном порядке. Если в этом аргументе — ИСТИНА, функция ищет ближайшее к искомому или совпадающее с ним значение, ЛОЖЬ — ищет стопроцентное совпадение с искомым значением.
Это функция, которая упростит работу с большими массивами данных и несколькими таблицами. Будет полезной, если нужно подтянуть соответствующий критерию столбец из другой таблицы (например, группа, категория).
Пример
Вернемся к нашей таблице из пункта 6 (ЕСЛИ). Предположим, что каждому предмету покупки присвоили группу, к которой он относится (овощи, фрукты, кондитерские изделия и др.), и у нас есть справочник соответствий, но в другой таблице. В массивах данных, состоящих из тысяч строк, делать это вручную очень долго, а ВПР справится за долю секунды.
В ячейке G2 пишем формулу: =ВПР(A2;$L$2:$M$78;2;ЛОЖЬ). Обязательно ставим знаки фиксации диапазона $ — в случае их отсутствия при протягивании формулы вниз диапазон ячеек таблицы также будет смещаться вниз вместе с искомым значением.
Как прочитать формулу простым языком: найти значение из ячейки A2 в левом столбце таблицы L2:M78 и вывести соответствующее найденному искомому_значению выражение из столбца 2 этой таблицы, при этом искомое значение должно совпадать с данными в левом столбце таблицы (аргумент ЛОЖЬ).
Протягиваем формулу вниз — и выводится результат.
#8. ЕСЛИОШИБКА
Синтаксис: =ЕСЛИОШИБКА(значение;значение_если_ошибка)
значение — аргумент, который проверяется на наличие ошибки
значение_если_ошибка — значение, которое возвращается, если произошла ошибка.
Эта функция проверяет аргумент на равенство значениям ошибок #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0, #ЧИСЛО!, #ИМЯ? или #ПУСТО!. Если выражение в ячейке, которая проверяется, содержит ошибку, функция вернет значение, которое определено в этом случае. Если ошибки нет — результат расчетов или данные ячейки. Часто используется при делении на ноль.
Пример
Возьмем таблицу со значениями выручки и количеством проданных единиц. Нужно найти выручку за одну единицу продукции. Для решения этой задачи выручку делим на количество единиц.
Excel ругается, потому что в одном выражении выполняется деление на ноль. Поэтому проверяем на ошибку в соседнем столбце.
#9. СЖПРОБЕЛЫ
Синтаксис: =СЖПРОБЕЛЫ(текст)
текст — текстовое значение, из которого необходимо удалить лишние пробелы.
Эту функцию используют для обработки текстов из разных источников. Когда в этих текстах есть лишние пробелы, они удаляются.
Пример
Формула =СЖПРОБЕЛЫ(" Выручка с начала года ") вернет "Выручка с начала года", убрав из выражения лишние пробелы.
С помощью функции можно удалить из текста лишние знаки пробела (код символа — 32). В некоторых случаях в тексте может присутствовать знак неразрывного пробела (код — 160). Чтобы удалить эти лишние знаки, нужно использовать следующую формулу: =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A1;СИМВОЛ(160);СИМВОЛ(32))). В таком случае функция сначала заменяет все знаки неразрывного пробела на пробел, а после этого удаляет лишние пробелы.
#10. СЦЕПИТЬ
Синтаксис: =СЦЕПИТЬ(текст1;[текст2];[текст3];…)
текст1 — обязательный аргумент.
Для объединения значений из разных ячеек в одну используется функция СЦЕПИТЬ. Также можно применять аналог — & (амперсанд). Функция часто используется для объединения данных с нескольких столбцов.
Пример
Допустим, у нас есть таблица сотрудников с разделенными ФИО, должностью и возрастом, а в отчете нужно отобразить все эти значения в одной ячейке. Для выполнения этой задачи используем функцию так:
Полезные функции в Microsoft Excel
Microsoft Excel способен в значительной мере облегчить пользователю работу с таблицами и числовыми выражениями, автоматизировав ее. Этого удается достичь с помощью инструментов приложения и различных его функций. Давайте рассмотрим наиболее полезные из них.
Работа с функциями в Excel
Ввиду широкого функционального предназначения программы пользователь далеко не всегда знает о возможностях, которые упрощают взаимодействие со многими инструментами Экселя. Далее в статье мы расскажем о 10 лучших функциях, которые могут пригодиться в разных целях, а также предоставим ссылки на подробные уроки по работе с каждой из них.
Функция «ВПР»
Одной из самых востребованных функций в Microsoft Excel является «ВПР» («VLOOKUP)». Задействовав ее, можно перетягивать значения одной или нескольких таблиц в другую. При этом поиск производится только в первом столбце таблицы, тем самым при изменении данных в таблице-источнике автоматически формируются данные и в производной таблице, в которой могут выполняться отдельные расчеты. Например, сведения из таблицы, в которой находятся прейскуранты на товары, могут использоваться для расчета показателей в таблице об объеме закупок в денежном выражении.
ВПР запускается путем вставки оператора «ВПР» из «Мастера функций» в ту ячейку, где данные должны отображаться.
В появившемся окне после запуска этой функции нужно указать адрес ячейки или диапазона ячеек, откуда данные будут подтягиваться.
Сводные таблицы
Еще одной важной функцией программы Excel является создание сводных таблиц, обеспечивающее группировку данных из других таблиц по различным критериям, выполнение различных расчетов с ними (суммирование, умножение, деление и т.д.), а результаты выводятся в отдельную таблицу. При этом существуют очень широкие возможности по настройке полей сводной таблицы.
Создается она на вкладке «Вставка» нажатием на кнопку, которая так и называется — «Сводная таблица».
Создание диаграмм
Для визуального отображения данных, размещенных в таблице, удобно использовать диаграммы. Их часто применяют для создания презентаций, написания научных работ, в исследовательских целях и т.д. Excel предоставляет широкий набор инструментов по созданию различного типа диаграмм.
Чтобы создать диаграмму, нужно выделить набор ячеек с данными, которые вы хотите визуально отобразить. Затем, находясь на вкладке «Вставка», выбрать на ленте тот тип диаграммы, который считаете наиболее подходящим для достижения поставленных целей.
Более точная настройка диаграмм, включая установку ее наименования и наименования осей, производится в группе вкладок «Работа с диаграммами».
Одним из видов диаграмм являются графики. Принцип построения их тот же, что и у остальных типов диаграмм.
Формулы в Excel
Для работы с числовыми данными в программе позволяется использовать специальные формулы. С их помощью можно производить различные арифметические действия с данными в таблицах: сложение, вычитание, умножение, деление, возведение в степень извлечение корня и т.д. Чтобы применить формулу, нужно в ячейке, куда планируется выводить результат, поставить знак «=». После этого вводится сама формула, которая может состоять из математических знаков, чисел и адресов ячеек. Для указания адреса ячейки, из которой берутся данные для расчета, достаточно кликнуть по ней мышкой, и ее координаты появится в ячейке для вывода результата.
Excel также удобно использовать и в качестве обычного калькулятора. Для этого в строке формул или в любой ячейке просто вводятся математические выражения после знака «=».
Функция «ЕСЛИ»
Одной из самых популярных функций, которые используются в Excel, является «ЕСЛИ». Она дает возможность задать в ячейке вывод одного результата при выполнении конкретного условия и другого результата в случае его невыполнения. Ее синтаксис выглядит следующим образом: ЕСЛИ(логическое выражение; [результат если истина]; [результат если ложь]) .
Операторами «И», «ИЛИ» и вложенной функцией «ЕСЛИ» задается соответствие нескольким условиям или одному из нескольких условий.
Макросы
С помощью макросов в программе записывается выполнение определенных действий, а потом они воспроизводятся автоматически. Это существенно экономит время на выполнении большого количества однотипной работы. Макросы записываются путем включения захвата своих действий в программе через соответствующую кнопку на ленте.
Запись макросов также можно производить, используя язык разметки Visual Basic в специальном редакторе.
Условное форматирование
Чтобы выделить определенные данные, в таблице применяется функция условного форматирования, позволяющая настроить правила выделения ячеек. Само условное форматирование позволяется выполнить в виде гистограммы, цветовой шкалы или набора значков. Переход к ней осуществляется через вкладку «Главная» с выделением диапазона ячеек, который вы собираетесь отформатировать. Далее в группе инструментов «Стили» нажмите кнопку, имеющая название «Условное форматирование». После этого останется выбрать тот вариант форматирования, который считаете наиболее подходящим.
Форматирование будет выполнено.
«Умная» таблица
Не все пользователи знают, что таблицу, просто начерченную карандашом или границами, Excel воспринимает как простую область ячеек. Заставить программу видеть этот набор данных как таблицу можно через переформатирование. Делается это просто: для начала выделяем нужный диапазон с данными, а затем, находясь на вкладке «Главная», кликаем по кнопке «Форматировать как таблицу». Появится список с различными вариантами стилей оформления, где укажите подходящий.
Таблица также создается нажатием на кнопку «Таблица», которая расположена на вкладке «Вставка», предварительно выделив определенную область листа с данными.
Выделенный набор ячеек редактор будет воспринимать как таблицу. Вследствие этого, к примеру, если вы введете в расположенные у границ таблицы ячейки какие-то данные, они будут автоматически включены в нее. Вдобавок к этому при прокрутке вниз шапка будет постоянно в пределах области зрения.
Подбор параметра
С помощью функции подбора параметров можно подобрать исходные данные, руководствуясь желаемым для вас результатом. Перейдите на вкладку «Данные» и нажмите кнопку «Анализ «что если»», расположенную в блоке инструментов «Работа с данными». В появившемся списке укажите пункт «Подбор параметра…».
Откроется окно подбора параметра. В поле «Установить в ячейке» вы должны указать ссылку на ячейку, которая содержит нужную формулу. В поле «Значение» должен быть указан конечный результат, который вы хотите получить. В поле «Изменяя значения ячейки» вставьте координаты ячейки с корректируемым значением.
Функция «ИНДЕКС»
Возможности, которые предоставляет функция «ИНДЕКС», в чем-то близки к возможностям функции «ВПР». Она также позволяет искать данные в массиве значений и возвращать их в указанную ячейку. Синтаксис выглядит следующим образом: ИНДЕКС(диапазон_ячеек;номер_строки;номер_столбца) .
Это далеко не полный перечень всех функций, которые доступны в программе Microsoft Excel. Мы остановили внимание только на самых популярных и наиболее важных из них.
10 формул в Excel, которые облегчат вам жизнь
Чтобы применить любую из перечисленных функций, поставьте знак равенства в ячейке, в которой вы хотите видеть результат. Затем введите название формулы (например, МИН или МАКС), откройте круглые скобки и добавьте необходимые аргументы. Excel подскажет синтаксис, чтобы вы не допустили ошибку.
Аргументами называют данные, с которыми работает функция. Чтобы добавить их, нужно выделить соответствующие ячейки или ввести необходимые значения в скобках вручную.
Есть и альтернативный способ указать аргументы. Если после названия функции добавить пустые скобки и нажать на кнопку «Вставить функцию» (fx), появится окно ввода с дополнительными подсказками. Можете использовать его, если вам так удобнее.
1. МАКС
- Синтаксис: =МАКС(число1; [число2]; …).
Формула «МАКС» отображает наибольшее из чисел в выбранных ячейках. Аргументами функции могут выступать как отдельные ячейки, так и диапазоны. Обязательно вводить только первый аргумент.
2. МИН
- Синтаксис: =МИН(число1; [число2]; …).
Функция «МИН» противоположна предыдущей: отображает наименьшее число в выбранных ячейках. В остальном принцип действия такой же.
3. СРЗНАЧ
- Синтаксис: =СРЗНАЧ(число1; [число2]; …).
«СРЗНАЧ» отображает среднее арифметическое всех чисел в выбранных ячейках. Другими словами, функция складывает указанные пользователем значения, делит получившуюся сумму на их количество и выдаёт результат. Аргументами могут быть отдельные ячейки и диапазоны. Для работы функции нужно добавить хотя бы один аргумент.
4. СУММ
- Синтаксис: =СУММ(число1; [число2]; …).
Эта простая, но очень востребованная функция подсчитывает сумму чисел в выбранных ячейках. Вы можете складывать как отдельные значения, так и диапазоны ячеек. Обязательным в формуле является лишь первый аргумент.
5. ЕСЛИ
- Синтаксис: =ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь]).
Формула «ЕСЛИ» проверяет, выполняется ли заданное условие, и в зависимости от результата отображает одно из двух указанных пользователем значений. С её помощью удобно сравнивать данные.
В качестве первого аргумента функции можно использовать любое логическое выражение. Вторым вносят значение, которое таблица отобразит, если это выражение окажется истинным. И третий (необязательный) аргумент — значение, которое появляется при ложном результате. Если его не указать, отобразится слово «ложь».
6. СУММЕСЛИ
- Синтаксис: =СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]).
Усовершенствованная функция «СУММ», складывающая только те числа в выбранных ячейках, что соответствуют заданному критерию. С её помощью можно прибавлять цифры, которые, к примеру, больше или меньше определённого значения. Первым аргументом является диапазон ячеек, вторым — условие, при котором из них будут отбираться элементы для сложения.
Если вам нужно посчитать сумму чисел не в диапазоне, выбранном для проверки, а в соседнем столбце, выделите этот столбец в качестве третьего аргумента. В таком случае функция сложит цифры, расположенные рядом с каждой ячейкой, которая пройдёт проверку.
7. СЧЁТ
- Синтаксис: =СЧЁТ(значение1; [значение2]; …).
Эта функция подсчитывает количество выбранных ячеек, которые содержат числа. Аргументами могут выступать отдельные клетки и диапазоны. Для работы функции необходим как минимум один аргумент. Будьте внимательны: «СЧЁТ» учитывает ячейки с датами.
8. ДНИ
- Синтаксис: =ДНИ(конечная дата; начальная дата).
Всё просто: функция «ДНИ» отображает количество дней между двумя датами. В аргументы сначала добавляют конечную, а затем начальную дату — если их перепутать, результат получится отрицательным.
9. КОРРЕЛ
- Синтаксис: =КОРРЕЛ(диапазон1; диапазон2).
«КОРРЕЛ» определяет коэффициент корреляции между двумя диапазонами ячеек. Иными словами, функция подсчитывает статистическую взаимосвязь между разными данными: курсами доллара и рубля, расходами и прибылью и так далее. Чем больше изменения в одном диапазоне совпадают с изменениями в другом, тем корреляция выше. Максимальное возможное значение — +1, минимальное — −1.
10. СЦЕП
- Синтаксис: =СЦЕП(текст1; [текст2]; …).
Эта функция объединяет текст из выбранных ячеек. Аргументами могут быть как отдельные клетки, так и диапазоны. Порядок текста в ячейке с результатом зависит от порядка аргументов. Если хотите, чтобы функция расставляла между текстовыми фрагментами пробелы, добавьте их в качестве аргументов, как на скриншоте выше.