Основные функции и формулы в Excel: справочник для начинающих

Для кого эта статья:

• Новички, которые хотят научиться использовать Excel для повседневных задач
• Люди, работающие с данными и желающие повысить свою продуктивность
• Специалисты различных сфер, стремящиеся улучшить навыки аналитики и управления данными

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

Что такое Excel и его базовые элементы

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

Прежде чем погрузиться в мир формул, давайте разберёмся с базовой терминологией:

• Ячейка — основной элемент таблицы, находится на пересечении столбца и строки
• Диапазон — группа ячеек (например, A1:B5)
• Лист — отдельная страница в книге Excel
• Книга — файл Excel, содержащий один или несколько листов
• Формула — выражение, которое начинается со знака равенства (=) и выполняет вычисления
• Функция — предопределенная формула с собственным названием (например, СУММ)

При работе в Excel важно понимать систему адресации ячеек. Столбцы обозначаются латинскими буквами (A, B, C...), а строки — числами (1, 2, 3...). Так, ячейка B3 находится на пересечении столбца B и строки 3.

Михаил Соколов, руководитель отдела финансового анализа

Помню свой первый день в финансовом отделе крупной компании. Руководитель положил передо мной ноутбук и сказал: "У тебя два часа, чтобы разобраться с квартальным отчётом". Я открыл Excel и увидел перед собой океан цифр. Паника! В университете мы использовали Excel лишь поверхностно, и я не знал даже базовых функций.

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

Интерфейс Excel может показаться перегруженным, но для начала работы достаточно освоить несколько основных элементов:

Для создания любой формулы в Excel необходимо начать ввод со знака равенства (=). Это сигнализирует программе, что вы собираетесь выполнить вычисление, а не просто ввести текст. После знака равенства можно вводить числа, операторы (+, -, *, /), ссылки на ячейки или функции.

Основные математические функции: от сложения до процентов

Математические функции — основа работы в Excel. Именно с них большинство пользователей начинают знакомство с формулами. Даже простое сложение или вычитание в Excel может сэкономить массу времени при работе с большими массивами данных. 🧮

Вот список самых популярных математических функций, которые должен знать каждый:

• СУММ(диапазон) — суммирует все значения в указанном диапазоне
• СРЗНАЧ(диапазон) — вычисляет среднее арифметическое значений
• МАКС(диапазон) — находит максимальное значение в диапазоне
• МИН(диапазон) — находит минимальное значение в диапазоне
• ОКРУГЛ(число; число_разрядов) — округляет число до указанного количества разрядов
• СЧЁТ(диапазон) — подсчитывает количество ячеек с числами в диапазоне
• СУММЕСЛИ(диапазон; критерий; [диапазон_суммирования]) — суммирует значения, соответствующие заданному условию

Рассмотрим примеры использования этих функций на практике:

=СУММ(A1:A10) — суммирует значения ячеек с A1 по A10 =СРЗНАЧ(B5:B15) — вычисляет среднее значение диапазона B5:B15 =СУММЕСЛИ(C1:C20;">100";D1:D20) — суммирует значения из диапазона D1:D20, для которых соответствующие значения в C1:C20 больше 100

Особое внимание стоит уделить работе с процентами, которая часто вызывает затруднения у начинающих:

=A1*10% — находит 10% от значения в ячейке A1 =A1*(1+B1) — увеличивает значение A1 на процент, указанный в B1 (B1 должен быть в формате 0,1 для 10%) =A1/B1-1 — вычисляет процентное изменение между значениями (например, рост продаж)

Елена Краснова, бухгалтер-аналитик

Когда я только начинала работать с Excel, мне казалось невозможным быстро обрабатывать большие массивы данных. Однажды мой руководитель попросил проанализировать продажи за год по 20 категориям товаров. Вручную это заняло бы недели!

Я решила использовать функцию СУММЕСЛИ. Создала простую формулу =СУММЕСЛИ(A:A;"Категория 1";B:B), которая суммировала все продажи для первой категории. Затем просто скопировала эту формулу для всех категорий, меняя только название. Задание, на которое обычно уходила неделя, я выполнила за 2 часа! С тех пор я всегда говорю новичкам: "Научитесь использовать СУММЕСЛИ и СУММЕСЛИМН — они изменят вашу жизнь".

Для более сложных расчетов пригодятся функции:

Текстовые и логические формулы для обработки данных

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

Наиболее полезные текстовые функции включают:

• СЦЕП(текст1; [текст2]; ...) — объединяет несколько текстовых строк в одну
• ЛЕВСИМВ(текст; количество_знаков) — извлекает заданное количество символов с начала строки
• ПРАВСИМВ(текст; количество_знаков) — извлекает заданное количество символов с конца строки
• ПСТР(текст; начальнаяпозиция; количествознаков) — извлекает подстроку из текста
• НАЙТИ(искомыйтекст; текст; [начальнаяпозиция]) — находит позицию одной строки в другой
• ПРОПИСН(текст) — преобразует все буквы в верхний регистр
• СТРОЧН(текст) — преобразует все буквы в нижний регистр

Примеры использования текстовых функций:

=СЦЕП(A1; " "; B1) — объединяет содержимое ячеек A1 и B1, разделяя их пробелом =ЛЕВСИМВ(A1; 3) — извлекает первые 3 символа из ячейки A1 =ПСТР(A1; НАЙТИ("@"; A1) + 1; 100) — извлекает домен из email-адреса

Логические функции позволяют создавать условные выражения и принимать решения на основе данных:

• ЕСЛИ(условие; значениееслиистина; значениееслиложь) — возвращает одно из двух значений в зависимости от результата проверки условия
• И(условие1; условие2; ...) — возвращает ИСТИНА, если все условия истинны
• ИЛИ(условие1; условие2; ...) — возвращает ИСТИНА, если хотя бы одно из условий истинно
• НЕ(условие) — инвертирует логическое значение
• ЕСЛИОШИБКА(значение; значениееслиошибка) — обрабатывает возможные ошибки в формуле

Рассмотрим несколько практических примеров:

=ЕСЛИ(A1>10; "Высокий"; "Низкий") — если значение в A1 больше 10, возвращает "Высокий", иначе "Низкий" =ЕСЛИ(И(A1>10; A1<20); "Средний"; ЕСЛИ(A1>=20; "Высокий"; "Низкий")) — более сложное условие с вложенными функциями ЕСЛИ =ЕСЛИОШИБКА(B1/A1; "Деление на ноль") — защищает от ошибки деления на ноль

Одна из самых мощных и часто используемых функций в Excel — это ВПР (вертикальный просмотр), которая позволяет искать значения в таблицах данных:

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

Например, если у вас есть таблица товаров с кодами и ценами (A1:B100), вы можете найти цену товара по его коду:

=ВПР("ABC123"; A1:B100; 2; ЛОЖЬ) — найдет цену товара с кодом ABC123

Для эффективной обработки данных используйте комбинации текстовых и логических функций:

=ЕСЛИ(ЛЕВСИМВ(A1;1)="A"; "Категория A"; "Другая категория") — проверяет, начинается ли значение в A1 с буквы A

Функции для работы с датами и временем в Excel

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

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

Основные функции для работы с датами:

• СЕГОДНЯ() — возвращает текущую дату
• ТДАТА() — возвращает текущие дату и время
• ДАТА(год; месяц; день) — создает дату из отдельных компонентов
• ГОД(дата) — извлекает год из даты
• МЕСЯЦ(дата) — извлекает месяц из даты (1-12)
• ДЕНЬ(дата) — извлекает день месяца из даты
• ДЕНЬНЕД(дата; [тип]) — определяет день недели (1-7)
• КОНМЕСЯЦА(начальная_дата; месяцы) — возвращает последний день месяца
• РАБДЕНЬ(начальная_дата; дни; [праздники]) — вычисляет дату на указанное количество рабочих дней вперед или назад

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

=ДАТА(2023;12;31) — создает дату 31 декабря 2023 года =СЕГОДНЯ()+7 — дата через неделю от текущей =B1-A1 — количество дней между датами в ячейках A1 и B1 =МЕСЯЦ(A1)=МЕСЯЦ(СЕГОДНЯ()) — проверяет, относится ли дата в A1 к текущему месяцу

Для расчета возраста или стажа работы можно использовать формулу:

=ЦЕЛОЕ((СЕГОДНЯ()-A1)/365,25) — вычисляет полное количество лет с даты в A1

Для работы со временем Excel использует десятичные дроби (1 день = 1, 1 час = 1/24). Основные функции для работы со временем:

• ВРЕМЯ(часы; минуты; секунды) — создает значение времени
• ЧАС(время) — извлекает часы из значения времени (0-23)
• МИНУТЫ(время) — извлекает минуты из значения времени (0-59)
• СЕКУНДЫ(время) — извлекает секунды из значения времени (0-59)
• РАЗНДАТ(конечнаядата; начальнаядата; единица) — вычисляет разницу между датами в указанных единицах

Примеры расчетов с временем:

=ВРЕМЯ(15;30;0) — создает значение времени 15:30:00 =B1-A1 — разница во времени между A1 и B1 (в долях дня) =(B1-A1)*24 — разница во времени в часах =РАЗНДАТ(B1;A1;"M") — разница между датами в месяцах

Для работы с рабочими днями используйте:

=РАБДЕНЬ(A1;10) — дата через 10 рабочих дней от даты в A1 =РАБДЕНЬ.МЕЖД(A1;10;1) — дата через 10 рабочих дней с учетом выходных в субботу и воскресенье

Полезные сочетания клавиш для быстрой работы в Excel

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

Вот самые полезные сочетания клавиш, которые сэкономят вам массу времени:

• Ctrl+C / Ctrl+X / Ctrl+V — копировать / вырезать / вставить
• Ctrl+Z / Ctrl+Y — отменить / повторить действие
• Ctrl+S — сохранить файл
• Ctrl+Домой — перейти в начало листа (ячейка A1)
• Ctrl+Конец — перейти в конец используемой области
• Ctrl+Стрелка — перейти к краю текущего региона данных
• Shift+Стрелка — расширить выделение на одну ячейку
• Ctrl+Shift+Стрелка — выделить все ячейки до края данных
• F2 — редактировать ячейку
• F4 — повторить последнее действие или переключить тип ссылки ($A$1)
• Alt+= — автосумма выделенного диапазона
• Ctrl+Shift+L — включить/выключить фильтры
• Ctrl+1 — открыть диалоговое окно форматирования ячеек
• Ctrl+Пробел — выделить столбец
• Shift+Пробел — выделить строку
• Ctrl+Shift+Пробел — выделить всю таблицу

Для работы с формулами особенно полезны:

• F4 (при редактировании формулы) — циклическое переключение между типами ссылок (A1, $A1, A$1, $A$1)
• Ctrl+Shift+Enter — ввод формулы массива (в старых версиях Excel)
• Alt+Enter — перенос строки внутри ячейки
• Ctrl+' (апостроф) — копировать значение из ячейки выше
• Ctrl+; — вставить текущую дату
• Ctrl+: — вставить текущее время
• Ctrl+` (обратная кавычка) — переключение между отображением формул и результатов
• F9 (при редактировании формулы) — вычислить выделенную часть формулы

Для работы с данными и таблицами:

• Ctrl+T — создать таблицу из выделенного диапазона
• Ctrl+L — создать таблицу (альтернативный вариант)
• Alt+Стрелка вниз — открыть выпадающий список фильтра
• Ctrl+Shift+L — включить/выключить фильтр
• Ctrl+Shift+« — скрыть выделенные строки
• Ctrl+Shift+» — показать скрытые строки
• Ctrl+9 — скрыть выделенные строки
• Ctrl+0 — скрыть выделенные столбцы

Для перемещения между листами:

• Ctrl+PgDn — перейти на следующий лист
• Ctrl+PgUp — перейти на предыдущий лист
• Shift+F11 — вставить новый лист
• Alt+Shift+стрелка вправо — сгруппировать выделенные листы
• Alt+Shift+стрелка влево — разгруппировать листы

Для новичков рекомендую начать с освоения самых базовых сочетаний: навигация с помощью Ctrl+Стрелки, выделение с Shift+Стрелки и Ctrl+Shift+Стрелки, а также копирование/вставка. Эти несколько приемов уже значительно ускорят вашу работу. По мере роста опыта добавляйте новые комбинации в свой арсенал.

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

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

Читайте также

• Power Pivot в Excel: 7 ключевых функций для анализа больших данных
• Диаграммы и графики в Excel: пошаговое руководство для новичков
• Анализ данных в Excel: 5 эффективных приёмов со сводными таблицами
• Использование Google Таблиц вместе с Excel: 5 способов интеграции
• Расчет коэффициента корреляции в Excel: пошаговая инструкция
• Вставка данных из других источников в Excel: полное руководство
• Работа с таблицами в Excel: пошаговое руководство с примерами
• Введение в аналитику данных: ключевые понятия и преимущества
• Форматирование данных в Excel: 15 профессиональных трюков и советов
• Создание и категоризация таблиц в Excel: структура данных