Перейти к содержанию

Lecture 01

1. Настройка интерфейса

Перед началом работы важно настроить Excel так, чтобы основные команды были под рукой.

Добавление команд на панель быстрого доступа

  1. Пройдите в Файл -> Параметры -> Панель быстрого доступа
  2. Из левой части "Выбрать команды из:" добавьте нужные команды в правую "Настройка панели быстрого доступа"
  3. Добавьте часто используемые функции: Создать файл, Сохранить/Сохранить как, Отменить/Повторить, Просмотр и печать, Очистить все фильтры, Применить все повторно, Удаление дубликатов.
  4. Удобно использовать <Разделитель>, когда нужно разделить команды по функционалу.
  5. Нажмите ОК.

2. Сортировка, фильтрация, смарт-таблицы

Эти инструменты позволяют быстро анализировать и структурировать данные.

Сортировка

  • По одному столбцу: Выберите диапазон, затем Данные → Сортировка → выберите критерий.
  • По нескольким столбцам: Нажмите Добавить уровень в меню сортировки.
  • Нестандартные моменты:
    • Сортировка по цвету ячеек/шрифта
    • Сортировка по пользовательскому списку (например, "Высокий, Средний, Низкий")

Фильтрация

  • Включаем фильтр: Данные → Фильтр.
  • Автофильтр: Использование фильтра по тексту, числам, датам.
  • Фильтрация по условиям: (например, все продажи выше 10 000 руб.).

Вот доработанный вариант с дополнительными пояснениями:

Смарт-таблица

Смарт-таблица (или «умная таблица») - это специальный инструмент Excel, который делает работу с данными удобнее и эффективнее. Она автоматически обновляет диапазоны, упрощает ввод формул и делает таблицу более наглядной.

Преимущества смарт-таблиц

  1. Автоматическое обновление диапазона
    Добавляете новые строки или столбцы - таблица расширяется автоматически, и все формулы остаются корректными.
  2. Фиксация заголовков
    При прокрутке вниз заголовки остаются на месте, что удобно при работе с большими таблицами.
  3. Встроенная фильтрация и сортировка
    Каждый столбец получает кнопку фильтра, которая позволяет быстро находить нужные данные.
  4. Динамические ссылки в формулах
    Вместо обычных диапазонов (A2:A100) можно использовать имена столбцов ([Продажи]), что делает формулы понятнее.
  5. Автоматическое применение стилей
    Таблица оформляется автоматически: чередуются цвета строк, выделяются заголовки и итоговые строки.

Как создать смарт-таблицу?

Вариант 1 1. Выделите данные (включая заголовки).
2. Перейдите в Главная → Форматировать как таблицу.
3. Выберите стиль оформления.
4. Убедитесь, что установлен флажок «Таблица с заголовками».
5. Нажмите ОК - таблица готова!

Вариант 2
1. Встаньте ячейку внутри таблицы 2. Нажмите сочетание клавиш Ctrl+T 3. Убедитесь, что установлен флажок «Таблица с заголовками». 4. Нажмите ОК - таблица готова!

Дополнительные функции смарт-таблицы

  • Добавление итоговой строки
  • Кликните в любую ячейку таблицы.
  • Перейдите в Конструктор таблицы. Это вкладка меню появляется только тогда, когда вы стоите на смарт-таблице.
  • В разделе "Параметры стилей таблиц" установите флажок "Итоговая строка".

  • Внизу таблицы появится строка, в которой можно выбрать функции (СУММ, СРЗНАЧ и т. д.).

  • Добавление новой строки
    Просто начните вводить данные сразу под таблицей - Excel автоматически её расширит.

  • Задать или изменить имя таблицы
    В Конструкторе таблицы есть поле «Имя таблицы» - задайте понятное название, чтобы использовать его в формулах.

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

Функции работы с текстом

Функция Описание Пример
ПРОПИСН Преобразует текст в ВЕРХНИЙ регистр =ПРОПИСН(A1)
ПРОПНАЧ Преобразует первый символ каждого слова в прописную букву =ПРОПНАЧ(A1)
СТРОЧН Преобразует текст в нижний регистр =СТРОЧН(A1)
СЖПРОБЕЛЫ Убирает лишние пробелы =СЖПРОБЕЛЫ(A1)
ПОДСТАВИТЬ Заменяет часть текста =ПОДСТАВИТЬ(A1; "_"; " ")
СЦЕП Объединяет текст из нескольких ячеек =СЦЕП(A1; " "; B1)
ДЛСТР длина строки (например, найти слишком длинные названия) =ДЛСТР(A1)
ЛЕВСИМВ / ПРАВСИМВ извлекает символы слева или справа (например, выделить код региона из номера телефона) =ЛЕВСИМВ(A8;3)
НАЙТИ / ПОИСК ищет символ или текст внутри строки (например, определить, есть ли в названии товара слово "скидка") =НАЙТИ("скидк";A5;1)
ЛЕВСИМВ + НАЙТИ Извлекает часть текста, основываясь на положении символа =ЛЕВСИМВ(A1; НАЙТИ(" ";A1)-1)

Разделение текста по столбцам

  • Выделяем колонку → Данные → Текст по столбцам → выбираем разделитель (запятая, пробел и т. д.).

Объединение текста

  • =A1 & " " & B1 (объединение через амперсант - &)
  • =СЦЕПИТЬ(A1; " "; B1) (альтернативный способ)

Работа с числами

Функция Описание Пример
ОКРУГЛ Округление до заданного количества знаков =ОКРУГЛ(A1;2)
ОКРУГЛВНИЗ Округление в меньшую сторону =ОКРУГЛВНИЗ(A1;0)
ОКРУГЛВВЕРХ Округление в большую сторону =ОКРУГЛВВЕРХ(A1;0)

4. ВПР (поиск значений)

Что делает ВПР?
ВПР (вертикальный просмотр) позволяет находить данные в одном столбце и возвращать соответствующее значение из другого столбца. Это полезно при работе с прайс-листами, базами клиентов и другими таблицами.

Пример: Допустим, у нас есть список товаров и их цены, и мы хотим найти цену конкретного товара. 

=ВПР("Яблоко"; $A$2:$B$10; 2; ЛОЖЬ)
Этот запрос ищет слово "Яблоко" в диапазоне $A$2:$A$10 и возвращает соответствующее значение из второго столбца (B2:B10).

Почему важно закреплять диапазон с $?

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

Что происходит без закрепления?

Допустим, у нас есть формула:

=ВПР("Яблоко"; A2:B10; 2; ЛОЖЬ)
Если мы скопируем её на строку ниже, Excel автоматически изменит диапазон, сдвигая его вниз:
=ВПР("Яблоко"; A3:B11; 2; ЛОЖЬ)
Теперь поиск идёт не в тех ячейках, и формула может вернуть неверный результат или ошибку.

Как исправить?

Чтобы диапазон не изменялся при копировании, закрепляем его с помощью $ или выделив его нажимаем на клавишу F4:

=ВПР("Яблоко"; $A$2:$B$10; 2; ЛОЖЬ)

Как работают $ в Excel?

  • $A$2 - полностью закрепленная ячейка (не изменяется при копировании).
  • $A2 - закреплен только столбец (строка меняется).
  • A$2 - закреплена только строка (столбец меняется).

В случае с ВПР нам важно закрепить весь диапазон, поэтому используем $A$2:$B$10.

Недостатки ВПР

  • Ищет только слева направо
  • Может замедляться при больших данных
  • Возвращает только первое совпадение

5. СУММЕСЛИ и СУММЕСЛИМН (суммирование по условиям)

Эти функции помогают суммировать данные с учетом условий.

СУММЕСЛИ (одно условие)

Функция СУММЕСЛИ работает так: Excel ищет строки, где выполняется заданное условие и соответствующие значения суммируются.

Пример: Посчитать сумму продаж менеджера "Иван".

Менеджер Регион Продажи
Иван Москва 10 000
Петр Москва 8 000
Иван Петербург 12 000
Иван Москва 5 000
Петр Ростов-на-Дону 6 000 

=СУММЕСЛИ(A2:A6; "Иван";C2:C6)
Ответ: 27 000

СУММЕСЛИМН (несколько условий)

Функция СУММЕСЛИМН работает так, что каждое условие проверяется по строкам, то есть Excel ищет строки, где оба условия выполняются одновременно. 

Пример: Посчитать сумму продаж "Ивана" в регионе "Москва".

=СУММЕСЛИМН(C2:C6;A2:A6;"Иван";B2:B6;"Москва")
Ответ: 15 000

Нестандартные случаи использования

  1. Сумма по частичному совпадению (например, все менеджеры с именем, начинающимся на "П")

=СУММЕСЛИ(A2:A6; "П*";C2:C6)
Ответ: 14 000

  1. Сумма за последние 7 дней

=СУММЕСЛИМН(B2:B100; A2:A100; ">=" & СЕГОДНЯ()-7)
3. Самый интересный случай, если вам потребовалось суммировать значения по нескольким критериям находящимся в одном и том же диапазоне
Пример: Посчитать сумму продаж со склада Москва и Петербург

Можно сразу подумать, что такая задача решается просто дублированием диапазона и добавлением критерия:

=СУММЕСЛИМН(C2:C6;B2:B6;"Москва";B2:B6;"Петербург")
НО, такая формула выдаст 0, и это соответственно не верно!
Дело в том, что в этой формуле мы дважды задаем условия для одного и того же столбца B. Это означает, что Excel ищет строки, где B одновременно равно "Москва" и "Петербург", но в одной ячейке не может быть двух значений сразу - поэтому формула не работает.

Как исправить?

Вариант 1. Через сумму двух СУММЕСЛИ (СУММЕСЛИМН):

Если нужно суммировать данные, где B равно "Москва" и "Петербург", используем СУММЕСЛИ или СУММЕСЛИМН дважды и складываем результаты:

=СУММЕСЛИ(B2:B6; "Москва";C2:C6) + СУММЕСЛИ(B2:B6; "Петербург";C2:C6)

=СУММЕСЛИМН(C2:C6;B2:B6;"Москва") + СУММЕСЛИМН(C2:C6;B2:B6;"Петербург")
Вариант 2. Через массив в СУММЕСЛИ.

Можно задать сразу несколько критериев через фигурные скобки {}. Если у вас версия Excel до 2019, тогда формулу нужно вводить через Ctrl+Shift+Enter: 

=СУММ(СУММЕСЛИ(B2:B6; {"Москва";"Петербург"};C2:C6))
Как это работает?
1. Внутренняя часть СУММЕСЛИ(B2:B6; {"Москва";"Петербург"}; C2:C6) - СУММЕСЛИ(диапазон_условий; условие; диапазон_суммирования) - Здесь мы проверяем, какие значения в диапазоне B2:B6 равны "Москва" или "Петербург". - Так как мы передали массив {"Москва";"Петербург"}, Excel автоматически создает две СУММЕСЛИ: 1. Одна ищет и суммирует C2:C6, где в B2:B6 = "Москва". 2. Другая ищет и суммирует C2:C6, где в B2:B6 = "Петербург". - В итоге, результатом этой части формулы будет массив из двух чисел:

{Сумма для Москвы; Сумма для Петербурга}
2. Внешняя функция СУММ(...)
- Теперь у нас есть массив {Сумма для Москвы; Сумма для Петербурга}. - Функция СУММ(...) складывает эти значения, получая общий итог.

Почему этот метод удобен?
  • Не нужно писать СУММЕСЛИ для каждого города отдельно и складывать вручную.
  • Если нужно добавить больше городов, просто расширяем список: 
    =СУММ(СУММЕСЛИ(B2:B6; {"Москва";"Петербург";"Ростов-на-Дону"}; C2:C6))

6. СУММПРОИЗВ - мощная альтернатива СУММЕСЛИМН

СУММПРОИЗВ чаще всего используют для суммы произведений массивов, но она также может заменить СУММЕСЛИМН.

Пример: суммирование продаж по нескольким условиям

Менеджер Регион Продажи
Иван Москва 10 000
Петр Москва 8 000
Иван СПб 12 000
Иван Москва 5 000
Петр СПб 6 000

Задача: Посчитать сумму продаж для Ивана в Москве.

Формула через СУММПРОИЗВ:

=СУММПРОИЗВ((A2:A6="Иван")*(B2:B6="Москва")*C2:C6)
- Эта формула умножает массивы, создавая логическую маску (1 если условие выполняется, 0 если нет).
- По сути, работает как СУММЕСЛИМН, но быстрее в больших таблицах.

Еще интересные применения СУММПРОИЗВ:

  • Подсчет уникальных значений:

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(B2:B10; B2:B10))
Подсчитывает количество уникальных значений в диапазоне A2:A10.

Как работает:

  1. СЧЁТЕСЛИ(B2:B10; B2:B10)
  2. Считает, сколько раз каждое значение встречается в диапазоне.

  3. Например, если "Москва" встречается 3 раза, для неё будет 3.

  4. 1/СЧЁТЕСЛИ(B2:B10; B2:B10)

  5. Преобразует частоту в доли:

  6. Если значение встречается 3 раза → каждая "Москва" даст 1/3.

  7. СУММПРОИЗВ(...)

  8. Складывает все доли, в итоге получается число уникальных значений.

Вот как это будет выглядеть на примере:
Если в B2:B10:
{"Москва", "Питер", "Москва", "Казань", "Питер", "Москва", "Казань", "Ростов", "Сочи"}
Формула вернёт 5, потому что уникальные города - Москва, Питер, Казань, Ростов, Сочи.

Город Сколько раз встречается (СЧЁТЕСЛИ) 1/СЧЁТЕСЛИ (доля)
Москва 3 ⅓
Питер 2 ½
Москва 3 ⅓
Казань 2 ½
Питер 2 ½
Москва 3 ⅓
Казань 2 ½
Ростов 1 1
Сочи 1 1

Теперь складываем все значения:

1/3 + 1/2 + 1/3 + 1/2 + 1/2 + 1/3 + 1/2 + 1 + 1 = 5

Итог: 5 уникальных городов (Москва, Питер, Казань, Ростов, Сочи).

  • Суммирование только положительных значений:

=СУММПРОИЗВ((H2:H10>0)*H2:H10)
Считает сумму только положительных чисел.

  • С помощью СУММПРОИЗВ можно решить нашу прошлую задачу по суммированию значения по нескольким критериям находящимся в одном и том же диапазоне (столбце):
    Вспомним, как выглядела наша таблица:
Менеджер Регион Продажи
Иван Москва 10 000
Петр Москва 8 000
Иван Петербург 12 000
Иван Москва 5 000
Петр Ростов-на-Дону 6 000

Формула для суммирования продаж по Москве и Петербургу:

=СУММПРОИЗВ((C2:C6)*((B2:B6="Москва")+(B2:B6="Петербург")))

Как работает формула?

  1. Выражение (B2:B6="Москва")+(B2:B6="Петербург")
  2. Excel проверяет каждую строку в диапазоне B2:B6:
Регион "Москва"? "Петербург"? Итог
Москва 1 0 1
Москва 1 0 1
Петербург 0 1 1
Москва 1 0 1
Ростов-на-Дону 0 0 0

В итоге формируется массив:

{1;1;1;1;0}
(1 означает, что город соответствует Москва или Петербург, 0 - не соответствует).

  1. Перемножение на C2:C6 (Продажи)
  2. Каждое значение из столбца C (Продажи) умножается на этот массив {1;1;1;1;0}:
Продажи Фильтр (Москва / Петербург) Результат
10 000 1 10 000
8 000 1 8 000
12 000 1 12 000
5 000 1 5 000
6 000 0 0

Получаем массив:

{10 000; 8 000; 12 000; 5 000; 0}

  1. Суммирование через СУММПРОИЗВ(...)
  2. Excel складывает все элементы массива:
    10 000 + 8 000 + 12 000 + 5 000 + 0 = 35 000
  3. В итоге мы получаем: 35 000

Преимущества использования такого метода:
- Работает без Ctrl+Shift+Enter (в любых версиях Excel).
- Можно легко добавить больше городов:

=СУММПРОИЗВ((C2:C6)*((B2:B6="Москва")+(B2:B6="Петербург")+(B2:B6="Ростов-на-Дону")))
- Подходит для больших таблиц без использования массивов {}.