Шпаргалка по Excel
Последнее обновление
Основы формул
Каждая формула начинается со знака равенства. Excel вычисляет её и показывает результат в ячейке.
| Операция | Синтаксис |
|---|---|
| Начать формулу | = then the expression, e.g. =2+2 |
| Сослаться на другую ячейку | =A1 |
| Арифметика | + - * / and ^ for powers |
| Управлять порядком действий | =(A1+A2)*B1 |
| Соединить текст (конкатенация) | =A1&" "&B1 or =CONCAT(A1," ",B1) |
| Операторы сравнения | = <> > < >= <= |
| Процент от значения | =A1*15% |
| Добавить комментарий в формулу | =SUM(A1:A9)+N("monthly total") |
| Показать формулы вместо результатов | Ctrl + ` (toggle) |
| Превратить формулу в её результат | Copy, then Paste Special → Values |
Ссылки на ячейки и диапазоны
Знак $ фиксирует строку или столбец, чтобы они не сдвигались при копировании формулы - самое полезное, что можно понять в Excel.
| Ссылка | Что означает |
|---|---|
A1 | Относительная - сдвигается при копировании в любую сторону |
$A$1 | Абсолютная - никогда не сдвигается |
$A1 | Столбец закреплён, строка сдвигается |
A$1 | Строка закреплена, столбец сдвигается |
A1:A10 | Диапазон из десяти ячеек вниз по одному столбцу |
A1:C10 | Прямоугольный блок |
A:A | Весь столбец A |
1:1 | Вся строка 1 |
Sheet2!A1 | Ячейка на другом листе |
'My Sheet'!A1 | Другой лист, в имени которого есть пробел |
[Book2.xlsx]Sheet1!A1 | Ячейка в другой книге |
Toggle $ while editing | F4 (Windows), Cmd + T (Mac) |
Математические и агрегирующие функции
Повседневные итоги. Все принимают диапазон, список ячеек или их сочетание.
| Функция | Что делает |
|---|---|
=SUM(B2:B20) | Складывает все числа в диапазоне |
=AVERAGE(B2:B20) | Среднее значение чисел |
=MEDIAN(B2:B20) | Срединное значение |
=MIN(B2:B20) / =MAX(B2:B20) | Наименьшее / наибольшее значение |
=PRODUCT(B2:B5) | Перемножает значения между собой |
=SUMPRODUCT(B2:B20,C2:C20) | Перемножает попарно, затем суммирует - взвешенные итоги |
=ABS(B2) | Абсолютное значение |
=POWER(B2,3) | B2 в кубе (то же, что =B2^3) |
=SQRT(B2) | Квадратный корень |
=MOD(B2,2) | Остаток - =0 для чётных чисел |
=SUBTOTAL(109,B2:B20) | Суммирует только видимые строки (игнорирует отфильтрованные) |
=RAND() / =RANDBETWEEN(1,100) | Случайное дробное / случайное целое число |
Логические функции
IF - основная рабочая функция. IFS и IFERROR сохраняют длинные формулы читаемыми.
| Функция | Что делает |
|---|---|
=IF(B2>1000,"Over","OK") | Одно условие, два исхода |
=IF(B2>1000,"Over",IF(B2>500,"Watch","OK")) | Вложенный IF для трёх и более исходов |
=IFS(B2>1000,"Over",B2>500,"Watch",TRUE,"OK") | Плоская альтернатива вложенным IF |
=AND(B2>0,C2>0) | TRUE только когда выполняются все условия |
=OR(B2>0,C2>0) | TRUE когда выполняется любое условие |
=NOT(B2>0) | Меняет TRUE/FALSE на противоположное |
=IFERROR(A2/B2,0) | Заменяет ошибку резервным значением |
=IFNA(VLOOKUP(...),"Not found") | Перехватывает только #N/A |
=ISBLANK(B2) | TRUE для пустой ячейки |
=ISNUMBER(B2) / =ISTEXT(B2) | Проверка типа - полезно при валидации импортированных данных |
=SWITCH(B2,1,"Low",2,"Mid",3,"High","Other") | Сравнивает значение со списком вариантов |
Подсчёт и условные итоги
Семейство *IF и *IFS отвечает на вопросы «сколько штук» и «сколько всего» для строк, подходящих под правило.
| Функция | Что делает |
|---|---|
=COUNT(B2:B20) | Считает ячейки с числами |
=COUNTA(B2:B20) | Считает непустые ячейки любого типа |
=COUNTBLANK(B2:B20) | Считает пустые ячейки |
=COUNTIF(B2:B20,">100") | Считает строки, подходящие под одно условие |
=COUNTIF(B2:B20,"*north*") | Подстановочные знаки: * любые символы, ? один символ |
=COUNTIFS(B2:B20,">100",C2:C20,"Paid") | Считает строки, подходящие под несколько условий |
=SUMIF(C2:C20,"Paid",B2:B20) | Суммирует B там, где совпадает C |
=SUMIFS(B2:B20,C2:C20,"Paid",D2:D20,"EU") | Суммирует по нескольким условиям |
=AVERAGEIF(C2:C20,"Paid",B2:B20) | Условное среднее |
=MAXIFS(B2:B20,C2:C20,"Paid") | Наибольшее значение среди подходящих строк |
=COUNTIF($A$2:A2,A2)>1 | Помечает дубликат по мере движения вниз по столбцу |
=SUMPRODUCT((C2:C20="Paid")*(B2:B20)) | Условный итог без SUMIFS |
Функции поиска и ссылок
Извлечение значения из другой таблицы. XLOOKUP - современная замена VLOOKUP; INDEX/MATCH работает в любой версии Excel.
| Функция | Что делает |
|---|---|
=VLOOKUP(A2,$F$2:$H$50,3,FALSE) | Находит A2 в первом столбце и возвращает 3-й столбец. FALSE = точное совпадение |
=XLOOKUP(A2,$F$2:$F$50,$H$2:$H$50,"Not found") | Диапазон поиска и диапазон результата раздельные - может искать влево |
=INDEX($H$2:$H$50,MATCH(A2,$F$2:$F$50,0)) | Классический вариант, работающий везде |
=MATCH(A2,$F$2:$F$50,0) | Позиция A2 в диапазоне |
=HLOOKUP(A2,$F$1:$Z$4,3,FALSE) | То же, что VLOOKUP, но просматривает строку |
=INDEX(B2:D20,2,3) | Ячейка на пересечении 2-й строки и 3-го столбца блока |
=XLOOKUP(A2,F:F,H:H,,-1) | Приблизительное совпадение - ближайший меньший элемент (поиск по диапазонам) |
=OFFSET(A1,2,1) | Ячейка на 2 вниз и 1 вправо от A1 |
=INDIRECT("Sheet"&B1&"!A1") | Строит ссылку из текста |
=CHOOSE(B2,"Low","Mid","High") | Выбирает N-й элемент из списка |
=UNIQUE(A2:A100) | Уникальные значения диапазона (растекается) |
=FILTER(A2:C100,C2:C100="Paid") | Строки, подходящие под условие (растекается) |
Текстовые функции
Почти любая реальная таблица начинается с беспорядочного текста. Это инструменты для его чистки.
| Функция | Что делает |
|---|---|
=LEN(A2) | Количество символов |
=LEFT(A2,3) / =RIGHT(A2,3) | Первые / последние 3 символа |
=MID(A2,4,5) | 5 символов начиная с позиции 4 |
=TRIM(A2) | Убирает пробелы в начале, в конце и повторяющиеся |
=CLEAN(A2) | Удаляет непечатаемые символы из импортированных данных |
=UPPER(A2) / =LOWER(A2) / =PROPER(A2) | Изменить регистр |
=SUBSTITUTE(A2,"-","") | Заменяет все вхождения подстроки |
=REPLACE(A2,1,3,"NEW") | Заменяет по позиции, а не по содержимому |
=FIND("@",A2) / =SEARCH("@",A2) | Позиция подстроки (FIND учитывает регистр) |
=TEXTSPLIT(A2,",") | Разбивает текст по разделителю на ячейки |
=TEXTJOIN(", ",TRUE,A2:A9) | Соединяет диапазон разделителем, пропуская пустые |
=TEXT(A2,"0.00") | Форматирует число как текст по шаблону |
=VALUE(A2) | Превращает числовую строку в настоящее число |
=EXACT(A2,B2) | Сравнение с учётом регистра |
Функции даты и времени
Excel хранит дату как число - поэтому вычитание двух дат даёт количество дней.
| Функция | Что делает |
|---|---|
=TODAY() / =NOW() | Сегодняшняя дата / текущие дата и время |
=YEAR(A2), =MONTH(A2), =DAY(A2) | Достать одну часть из даты |
=DATE(2026,8,6) | Собирает дату из частей |
=B2-A2 | Дней между двумя датами |
=DATEDIF(A2,B2,"m") | Полных месяцев между двумя датами ("y", "m", "d") |
=EDATE(A2,3) | Тот же день через три месяца |
=EOMONTH(A2,0) | Последний день месяца из A2 |
=WEEKDAY(A2,2) | День недели; с аргументом 2 1 = понедельник |
=NETWORKDAYS(A2,B2) | Рабочих дней между двумя датами |
=WORKDAY(A2,10) | Дата через 10 рабочих дней после A2 |
=TEXT(A2,"yyyy-mm-dd") | Форматирует дату как текст |
=HOUR(A2), =MINUTE(A2) | Части времени |
Округление и числовые функции
Округление для отображения - это формат; округление для расчёта - это функция.
| Функция | Что делает |
|---|---|
=ROUND(A2,2) | Округляет до 2 знаков после запятой |
=ROUNDUP(A2,0) / =ROUNDDOWN(A2,0) | Всегда вверх / всегда вниз |
=MROUND(A2,5) | Округляет до ближайшего кратного 5 |
=CEILING(A2,1) / =FLOOR(A2,1) | Вверх / вниз до кратного |
=INT(A2) | Отбрасывает дробную часть |
=TRUNC(A2,1) | Обрезает дробную часть без округления |
=RANK(B2,$B$2:$B$20) | Позиция значения внутри диапазона |
=PERCENTILE(B2:B20,0.9) | 90-й процентиль |
=STDEV.S(B2:B20) | Стандартное отклонение по выборке |
=CORREL(B2:B20,C2:C20) | Корреляция между двумя столбцами |
Коды ошибок и что они значат
Каждая ошибка указывает на конкретный промах - умение их читать избавляет от догадок.
| Ошибка | Причина | Обычное решение |
|---|---|---|
#DIV/0! | Деление на ноль или на пустую ячейку | Обернуть в IFERROR или подстраховаться через IF(B2=0,...) |
#N/A | Поиск ничего не нашёл | Проверьте лишние пробелы (TRIM) и совпадение типов данных |
#VALUE! | Неверный тип аргумента - текст там, где ожидается число | Проверьте ячейки в ссылках; попробуйте VALUE() |
#REF! | Формула ссылается на удалённую ячейку | Пересобрать ссылку |
#NAME? | Опечатка в названии функции или текст без кавычек | Исправьте написание; заключите текст в кавычки |
#NUM! | Числовой результат, который Excel не может представить | Проверьте невозможные аргументы, например SQRT(-1) |
#NULL! | Два диапазона, которые не пересекаются | Проверьте, не пропущена ли запятая между аргументами |
#SPILL! | Динамическому массиву некуда развернуться | Очистите ячейки ниже или справа |
#### | Это не ошибка - столбец слишком узкий | Расширьте столбец |
| Circular reference | Формула включает собственную ячейку | Убрать ссылку на саму себя |
Сортировка, фильтры и работа с данными
Момент, когда набор данных перестаёт быть сеткой значений и становится читаемым.
| Задача | Как |
|---|---|
| Отсортировать диапазон | Data → Sort или Alt + A, затем S |
| Добавить раскрывающиеся фильтры | Ctrl + Shift + L |
| Отформатировать как таблицу | Ctrl + T - даёт именованные диапазоны и формулы, расширяющиеся сами |
| Удалить дубликаты | Data → Remove Duplicates |
| Разбить один столбец на несколько | Data → Text to Columns |
| Мгновенное заполнение (по шаблону) | Ctrl + E |
| Закрепить строку заголовка | View → Freeze Panes → Freeze Top Row |
| Условное форматирование | Home → Conditional Formatting - раскрасить ячейки по правилу |
| Проверка данных (раскрывающийся список) | Data → Data Validation → List |
| Дать имя диапазону | Выделите его и введите имя в поле «Имя» |
| Проследить, откуда формула берёт данные | Formulas → Trace Precedents |
| Подбор параметра (решить относительно входа) | Data → What-If Analysis → Goal Seek |
Сводная таблица в пять шагов
Самый быстрый способ свести несколько тысяч строк.
| Шаг | Действие |
|---|---|
| 1. Приведите источник в порядок | Одна строка заголовка, без пустых строк и объединённых ячеек |
| 2. Вставьте | Выделите данные → Insert → PivotTable |
| 3. Строки | Перетащите в «Строки» поле, по которому группируете |
| 4. Значения | Перетащите в «Значения» число, которое суммируете |
| 5. Сведите | Щёлкните поле значения → Summarize Values By → Sum / Count / Average |
| Добавить второе измерение | Перетащите поле в «Столбцы» |
| Отфильтровать всю таблицу | Перетащите поле в «Фильтры» или добавьте срез |
| Показать проценты | Поле значения → Show Values As → % of Grand Total |
| Обновить после изменения данных | Alt + F5 |
| Прочитать ячейку сводной таблицы в формуле | =GETPIVOTDATA("Sales",$A$3,"Region","EU") |
Горячие клавиши - самое необходимое
Дюжина, которая экономит больше всего времени.
| Действие | Windows | Mac |
|---|---|---|
| Редактировать активную ячейку | F2 | Ctrl + U |
| Подтвердить и остаться в ячейке | Ctrl + Enter | Ctrl + Enter |
| Новая строка внутри ячейки | Alt + Enter | Ctrl + Option + Enter |
| Автосумма | Alt + = | Cmd + Shift + T |
Переключить $ в ссылке | F4 | Cmd + T |
| Заполнить вниз из ячейки сверху | Ctrl + D | Cmd + D |
| Заполнить вправо | Ctrl + R | Cmd + R |
| Специальная вставка | Ctrl + Alt + V | Cmd + Ctrl + V |
| Вставить сегодняшнюю дату | Ctrl + ; | Cmd + ; |
| Повторить последнее действие | F4 | Cmd + Y |
| Отменить / вернуть | Ctrl + Z / Ctrl + Y | Cmd + Z / Cmd + Shift + Z |
| Показать формулы | Ctrl + ` | Ctrl + ` |
Горячие клавиши - перемещение и выделение
Перемещение по большому листу без мыши.
| Действие | Windows | Mac |
|---|---|---|
| Перейти к краю данных | Ctrl + arrow | Cmd + arrow |
| Выделить до края данных | Ctrl + Shift + arrow | Cmd + Shift + arrow |
| Выделить весь столбец / строку | Ctrl + Space / Shift + Space | Ctrl + Space / Shift + Space |
| Выделить текущую область | Ctrl + A | Cmd + A |
| Перейти к ячейке A1 | Ctrl + Home | Fn + Ctrl + Left |
| Перейти к конкретной ячейке | Ctrl + G | Ctrl + G |
| Следующий / предыдущий лист | Ctrl + PgDn / PgUp | Option + Right / Left |
| Вставить строки или столбцы | Ctrl + Shift + + | Cmd + Shift + + |
| Удалить строки или столбцы | Ctrl + - | Cmd + - |
| Скрыть столбец / строку | Ctrl + 0 / Ctrl + 9 | Cmd + 0 / Cmd + 9 |
| Найти / заменить | Ctrl + F / Ctrl + H | Cmd + F / Ctrl + H |
| Выделить только видимые ячейки | Alt + ; | Cmd + Shift + Z |
Горячие клавиши - форматирование
Числовые форматы стоит запомнить в первую очередь - они нужны постоянно.
| Действие | Windows | Mac |
|---|---|---|
| Диалог «Формат ячеек» | Ctrl + 1 | Cmd + 1 |
| Полужирный / курсив / подчёркнутый | Ctrl + B / I / U | Cmd + B / I / U |
| Денежный формат | Ctrl + Shift + $ | Ctrl + Shift + $ |
| Процентный формат | Ctrl + Shift + % | Ctrl + Shift + % |
| Числовой формат с 2 знаками после запятой | Ctrl + Shift + ! | Ctrl + Shift + ! |
| Формат даты | Ctrl + Shift + # | Ctrl + Shift + # |
| Общий формат (убрать форматирование) | Ctrl + Shift + ~ | Ctrl + Shift + ~ |
| Внешняя граница | Ctrl + Shift + & | Cmd + Option + 0 |
| Убрать границы | Ctrl + Shift + _ | Cmd + Option + - |
| Копировать форматирование (Формат по образцу) | Ctrl + Shift + C, then Ctrl + Shift + V | Cmd + Shift + C, then Cmd + Shift + V |
Формулы, функции и горячие клавиши Excel, которые нужны чаще всего, на одной странице. Эта шпаргалка по Excel - быстрый справочник по тому, что реально встречается в рабочей книге: как писать формулы, абсолютные и относительные ссылки на ячейки, IF и функции подсчёта, VLOOKUP и XLOOKUP, чистка текста, даты, что означает каждый код ошибки и какие горячие клавиши стоит запомнить.
Всё здесь работает в Excel для Windows и Mac, и почти всё работает без изменений в Google Sheets и LibreOffice Calc. Имена функций даны по-английски: именно так Excel хранит их внутри файла, хотя русская версия показывает их переведёнными (SUM отображается как СУММ, IF - как ЕСЛИ). Пути по меню тоже указаны для англоязычного интерфейса.
Частые вопросы о шпаргалке по Excel
Эта шпаргалка по Excel бесплатная?
Да - вся страница бесплатна, без регистрации и без загрузок. Копируйте любую формулу прямо из таблиц.
Какие формулы Excel самые важные?
Если выучить только десять: SUM, AVERAGE, IF, COUNTIF, SUMIF, XLOOKUP (или VLOOKUP), INDEX вместе с MATCH, TRIM, TEXT и IFERROR. Вместе они закрывают суммирование, условную логику, извлечение значений из другой таблицы, чистку беспорядочного текста и защиту отчёта от ошибок.
Что означает $ в формуле Excel?
Он фиксирует часть ссылки, чтобы она не сдвигалась при копировании формулы.
$A$1 всегда указывает на A1; $A1 сохраняет столбец A, но позволяет строке меняться; A$1 сохраняет строку 1, но позволяет меняться столбцу. Нажмите F4 (или Cmd + T на Mac) при редактировании ссылки, чтобы перебрать все четыре комбинации.Что использовать - VLOOKUP или XLOOKUP?
Используйте XLOOKUP, если он есть в вашем Excel (Microsoft 365 и Excel 2021 и новее). Он принимает столбец поиска и столбец результата отдельными аргументами, поэтому может искать влево, не ломается, когда кто-то вставит столбец, и имеет собственный аргумент для «не найдено». VLOOKUP всё равно стоит знать - он есть в каждой старой книге, которая вам достанется. INDEX/MATCH - вариант, который работает в любой версии Excel.
Работают ли эти формулы в Google Sheets?
Почти все без изменений: формулы, ссылки на ячейки, IF, COUNTIF/SUMIF, текстовые функции и функции даты, VLOOKUP, INDEX/MATCH, UNIQUE и FILTER. Горячие клавиши различаются сильнее, а часть функций есть только в Excel (например, некоторые новые функции динамических массивов), поэтому проверяйте всё необычное, прежде чем на это опираться.
Почему имена функций в моём Excel выглядят иначе?
Excel переводит имена функций на язык интерфейса, поэтому русская версия показывает СУММ вместо SUM и ЕСЛИ вместо IF. Сам файл хранит английское имя - поэтому документация, эта шпаргалка и общие книги используют английскую форму. Если вы наберёте переведённое имя в своём Excel, оно означает ровно то же самое.
Как убрать из отчёта ошибки вроде #N/A?
Оберните формулу в
IFERROR, например =IFERROR(VLOOKUP(A2,F:H,3,FALSE),"Не найдено"). Используйте IFNA, когда нужно перехватить только неудачный поиск и по-прежнему видеть настоящие проблемы вроде #VALUE!: скрывая все ошибки, вы делаете сломанные формулы невидимыми.Где можно потренировать эти формулы?
Бесплатный интерактивный курс Excel на Coddy запускает настоящую таблицу в браузере: каждый урок даёт небольшой набор данных и задачу и проверяет формулу, которую вы вводите. Устанавливать ничего не нужно, а в конце - бесплатный сертификат.
Связанные ресурсы
Другие шпаргалки
