Excel для повседневных задач. Практическое руководство для начинающих

Марк Системов

Excel для повседневных задач. Практическое руководство для начинающих

Как работать с этой книгой

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

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

Основной маршрут рассчитан на настольный Excel для Windows с русским интерфейсом, прежде всего Microsoft 365, Excel 2021 и Excel 2024. Большинство базовых приёмов работает и в других версиях, но названия команд и их расположение могут различаться. В Excel для браузера и на Mac некоторые действия выполняются иначе. Функция ПРОСМОТРX отдельно помечена как возможность новых версий; для неё приведена замена через ВПР.

В примерах используются десятичная запятая и точка с запятой между аргументами функций. Это распространённые настройки русскоязычного Excel. Если программа ожидает другой разделитель, ориентируйтесь на подсказку при вводе формулы. Названия столбцов A, B, C и адреса ячеек всегда набирайте латинскими буквами. Кавычки внутри формул должны быть прямыми: ".

Формулы напечатаны отдельными строками. Их можно перепечатать или скопировать в строку формул. Если при копировании из читалки появились лишние пробелы или переносы, удалите их. Сравнивайте не только вид формулы, но и полученный ответ. После каждой темы есть небольшое упражнение. Ответы собраны в конце книги.

Сохраните учебный файл как «Практика Excel.xlsx». Перед экспериментами с сортировкой, удалением или заменой данных делайте копию листа. Название копии может содержать номер главы. Не переносите в учебный файл реальные персональные данные клиентов или сотрудников: для освоения приёмов они не нужны.

Excel и Microsoft 365 — названия продуктов Microsoft. Это самостоятельное учебное руководство. Оно не является официальным изданием Microsoft.

1 Создаём удобную рабочую книгу

Книга лист ячейка и диапазон

Файл Excel называется книгой. Внутри книги находятся листы, а каждый лист состоит из строк и столбцов. Строки обозначены числами, столбцы — буквами. На пересечении столбца B и строки 3 расположена ячейка B3. Диапазон B3:B8 означает шесть ячеек одного столбца, а A1:F11 — прямоугольник из шести столбцов и одиннадцати строк.

Выделенная ячейка называется активной. Её адрес виден в поле имени слева от строки формул. Чтобы быстро перейти к нужному месту, щёлкните это поле, введите A1 и нажмите Enter. Так же можно выделить диапазон: введите A1:F11. Это удобнее прокрутки, когда таблица становится длинной.

Создайте новую пустую книгу. Дважды щёлкните ярлычок первого листа внизу окна и назовите его «Заказы». Сохраните файл в формате XLSX. Название листа должно объяснять его назначение: «Заказы» понятнее, чем «Лист1». Позже добавьте листы «Справочник», «Итоги» и «Практика». Пока они могут оставаться пустыми.

Ввод и исправление

Введите в A1 слово «Дата» и нажмите Tab: выделение перейдёт вправо. Продолжайте ввод заголовков в первой строке. Enter завершает ввод и обычно перемещает выделение вниз. Чтобы исправить часть содержимого ячейки, дважды щёлкните её или нажмите F2. Если просто начать печатать поверх выделенной ячейки, прежнее содержимое будет заменено целиком.

Delete удаляет содержимое выделенных ячеек. Команда удаления строк или столбцов меняет структуру листа и сдвигает соседние данные. Это разные действия. Если вместо исправления цены случайно удалили весь столбец, сразу нажмите Ctrl+Z. Сохраняйте файл сочетанием Ctrl+S после завершённого шага.

Когда заголовок не помещается, увеличьте ширину столбца: потяните правую границу его буквенного обозначения. Двойной щелчок по этой границе подбирает ширину по содержимому. Для длинного пояснения можно включить «Перенос текста». Не добавляйте пробелы, чтобы имитировать выравнивание: используйте команды выравнивания на вкладке «Главная».

Устройство хорошего списка

У каждой строки списка должен быть один смысл: один заказ, один платёж или одна задача. У каждого столбца — один вид данных. Дату и сумму записывайте в разные столбцы. Заголовки размещайте в одной строке, без пустых ячеек между ними. Внутри списка не вставляйте пустые строки для красоты и не объединяйте ячейки.

Если у заказа несколько товаров, заранее выберите правило: одна строка на товарную позицию. Тогда номер заказа может повторяться, и количество строк уже не равно количеству заказов. В нашем учебном журнале для простоты одна строка соответствует одному заказу с одним видом товара. В реальной работе такое соглашение нужно записать до начала расчётов.

Итоговые значения лучше размещать на другом листе или использовать специальную строку итогов таблицы. Если вставить строку «Всего» среди исходных заказов, позже легко суммировать её повторно и завысить результат в два раза.

Упражнение 1

На листе «Практика» введите в A1 «Количество», в B1 «Цена», в A2 число 3 и в B2 число 150. Сохраните книгу. Затем исправьте цену на 160, отмените исправление и убедитесь, что в B2 снова стоит 150. Перейдите к D20 через поле имени.

2 Вводим данные так чтобы Excel их понимал

Значение и оформление

Ячейка хранит значение, а формат определяет, как оно выглядит на экране. Число 1250 можно показать как 1 250,00 или как денежную сумму. От этого исходное число не меняется. Если же ввести «1250 рублей» одним текстом, Excel не обязан распознать это как число для расчёта. Вводите 1250, а денежный формат выбирайте отдельно.

Число, сохранённое как текст, часто выравнивается влево и может сопровождаться зелёным индикатором. Но выравнивание можно поменять вручную, поэтому оно служит подсказкой, а не доказательством. Для проверки используйте функцию ЕЧИСЛО: она возвращает ИСТИНА, если ячейка содержит число, и ЛОЖЬ — в остальных случаях.

=ЕЧИСЛО(A2)

Если число импортировано текстом, простая смена формата на «Числовой» может не преобразовать его. Иногда помогает предупреждение «Преобразовать в число», иногда требуется команда «Текст по столбцам» или повторный импорт с правильными настройками. Сначала проверьте результат на копии нескольких строк.

Артикулы, телефоны и номера, начинающиеся с нуля, часто нужно хранить текстом. Перед вводом кода 00125 назначьте ячейке формат «Текстовый». Можно также ввести апостроф перед кодом: '00125. Апостроф указывает Excel воспринимать запись как текст; в обычном отображении ячейки его не будет. Не применяйте такое преобразование к ценам и количествам.

Даты и проценты

Дата в Excel обычно хранится как число, которому назначен формат даты. Это позволяет прибавлять дни и сравнивать даты. В этой книге вводите даты полностью, например 01.10.2026. Если запись не распознаётся в ваших региональных настройках, используйте функцию ДАТА. Она принимает год, месяц и день именно в таком порядке.

=ДАТА(2026;10;1)

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

Процент — это доля единицы. Значение 15% хранится как 0,15. Если сначала ввести 15, а потом нажать кнопку процентного формата, на экране получится 1500%. Для ставки скидки вводите сразу 15% либо число 0,15 с последующим процентным форматированием.

Уменьшение количества знаков после запятой меняет только отображение. Число 12,345 может выглядеть как 12,35, но в следующем расчёте сохранится исходная точность. Если нужно изменить само число по правилу округления, используйте ОКРУГЛ. Этому посвящена глава о процентах.

Учебный журнал заказов

На листе «Заказы» задайте заголовки: A1 — Дата; B1 — Категория; C1 — Товар; D1 — Количество; E1 — Цена; F1 — Оплата; G1 — Сумма. В столбец G пока ничего не вводите. В числовых столбцах используйте обычные числа без единиц измерения. Все цены приведены в рублях за одну штуку.

Внесите следующие записи. Номер перед записью — номер строки Excel. Каждая запись занимает ячейки от A до F, в порядке заголовков.

Строка 2: 01.10.2026; Канцелярия; Блокнот; 3; 150; Да.

Строка 3: 01.10.2026; Аксессуары; Кабель; 2; 400; Нет.

Строка 4: 02.10.2026; Канцелярия; Ручка; 10; 50; Да.

Строка 5: 02.10.2026; Аксессуары; Мышь; 1; 1200; Да.

Строка 6: 03.10.2026; Канцелярия; Блокнот; 4; 150; Нет.

Строка 7: 03.10.2026; Аксессуары; Кабель; 3; 400; Да.

Строка 8: 04.10.2026; Канцелярия; Папка; 5; 80; Да.

Строка 9: 04.10.2026; Аксессуары; Мышь; 2; 1200; Нет.

Строка 10: 05.10.2026; Канцелярия; Ручка; 6; 50; Да.

Строка 11: 05.10.2026; Аксессуары; Кабель; 1; 400; Да.

После ввода выделите E2:E11 и задайте денежный или числовой формат с двумя знаками после запятой. В D2:D11 оставьте целые числа. Для A2:A11 используйте формат даты. Прочитайте журнал сверху вниз и убедитесь, что значения не съехали на соседний столбец.

Упражнение 2

На листе «Практика» введите в A5 код 00125 так, чтобы первый ноль сохранился. В B5 введите 15% и временно переключите ячейку в числовой формат. Какое значение должно появиться? Верните процентное оформление.

3 Считаем формулами и копируем их без ошибок

Первая формула

Формула начинается со знака равенства. В G2 листа «Заказы» введите умножение количества на цену. Используйте звёздочку, а не букву x.

=D2*E2

После Enter вы увидите 450. Щёлкните G2: в строке формул останется выражение, а в самой ячейке — его результат. Измените D2 с 3 на 4: сумма станет 600. Затем верните 3. Такая связь и делает таблицу полезной: результат обновляется при изменении исходных значений.

Чтобы заполнить G3:G11, выделите G2 и потяните маленький квадрат в правом нижнем углу выделения вниз до G11. Можно также скопировать G2, выделить G3:G11 и вставить. При копировании вниз Excel изменяет номера строк: в G3 получится =D3*E3, в G4 — =D4*E4.

Проверьте первую, одну среднюю и последнюю формулу. В G11 должна быть =D11*E11. Если в каждой строке повторилось 450, вероятно, вы вставили только значения или использовали закреплённые ссылки. Если во всём диапазоне видны формулы вместо результатов, проверьте режим «Показать формулы» на вкладке «Формулы» и формат ячеек.

Порядок действий

Excel сначала выполняет умножение и деление, затем сложение и вычитание. Скобки меняют порядок. Например, =100+20*3 даёт 160, а =(100+20)*3 даёт 360. Если формула должна сначала уменьшить цену на долю скидки, выделите эту часть скобками.

=E2*(1-10%)

Для цены 150 результат равен 135. Такая запись означает, что после скидки остаётся 90% исходной цены. Выражение =E2-10% даёт 149,9: оно вычитает число 0,1, а не десять процентов от цены. Сходный внешний вид формул не гарантирует одинакового смысла.

Относительная и абсолютная ссылка

Ссылки D2 и E2 относительные: при копировании они сдвигаются. Абсолютная ссылка $J$2 закрепляет и столбец J, и строку 2. Она нужна, когда много строк используют одну общую ставку или параметр.

В J1 листа «Заказы» напишите «Скидка», в J2 введите 10%. В H1 напишите «После скидки», а в H2 введите:

=G2*(1-$J$2)

Скопируйте формулу до H11. Ссылка G2 будет меняться на G3, G4 и далее, а $J$2 останется прежней. Первая сумма после скидки равна 405. Без знаков доллара ссылка на ставку сползла бы в пустые J3, J4 и последующие ячейки.

Смешанные ссылки закрепляют только одну координату: $A2 фиксирует столбец, A$2 фиксирует строку. Они полезны для расчётных сеток. Во время редактирования формулы клавиша F4 обычно переключает варианты ссылки. На некоторых ноутбуках требуется Fn+F4. Знаки доллара всегда можно ввести вручную.

Ссылки на другой лист

На листе «Итоги» в A1 напишите «Первый заказ», а в B1 введите ссылку на G2 листа «Заказы». Название листа отделяется восклицательным знаком.

=Заказы!G2

Если в имени листа есть пробел, его заключают в одинарные кавычки: ='Мои заказы'!G2. Удобнее начать формулу знаком равенства, затем щёлкнуть нужный лист и нужную ячейку: Excel сам подставит ссылку. Избегайте без необходимости ссылок на другие файлы, которые потом могут переместить или переименовать.