Глава 9. Работа с Excel. Популярные и полезные функции

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

Представьте, что вам нужно построить дом. Вы можете вручную переносить каждый кирпич, делать каждую доску. А можете использовать специализированные инструменты: подъёмный кран, циркулярную пилу, дрель. Функции — это именно такие специализированные инструменты в мире электронных таблиц. Они экономят ваше время, снижают вероятность ошибок и позволяют вам решать задачи, которые вручную были бы невозможны. Также если сложные задачи с Excel, которые невозможно решить стандартными средствами, это можно сделать с помощью нашей платформы CognitiveAI, у нас есть инструмент работы с эксель таблицами с помощью ИИ

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

9.1. Математические функции Excel: СУММ, СРЗНАЧ, МАКС, МИН, СЧЁТ, СЧЁТЗ.

Это ваш базовый набор инструментов для работы с числами. Они автоматизируют самые частые арифметические операции, которые вы бы иначе делали вручную или на калькуляторе. Отличие в том, что эти "калькуляторы" умеют работать с целыми "контейнерами" данных (диапазонами).

9.1.1. СУММ() (SUM): "Главный Сумматор"

Что делает: Суммирует все числа в указанном диапазоне ячеек или список чисел. Синтаксис: =СУММ(число1; [число2]; ...) или =СУММ(диапазон)

• числоN: Ячейки, числа или диапазоны для суммирования.

Примеры использования:

• =СУММ(A1:A10): Сумма всех значений в ячейках от A1 до A10.
• =СУММ(B5; C8; D1): Сумма значений из трёх отдельных ячеек.
• =СУММ(F:F): Сумма всех чисел в столбце F.
• =СУММ(Продажи_Q1): Сумма чисел из именованного диапазона "Продажи_Q1".
• Фишка: Если вы выделяете диапазон, Excel/Google Sheets автоматически предлагают сумму в статусной строке внизу окна.

9.1.2. СРЗНАЧ() (AVERAGE): "Поиск Золотой Середины"

Что делает: Вычисляет среднее арифметическое (сумму всех чисел, деленную на их количество) в указанном диапазоне. Синтаксис: =СРЗНАЧ(число1; [число2]; ...) или =СРЗНАЧ(диапазон)

Примеры использования:

• =СРЗНАЧ(B2:B50): Средний балл студентов из списка.
• =СРЗНАЧ(ДанныеТемператур): Средняя температура за период.
• Важно: Функция СРЗНАЧ игнорирует текстовые ячейки и пустые ячейки.

9.1.3. МАКС() (MAX): "Поиск Пика"

Что делает: Находит наибольшее числовое значение в указанном диапазоне. Синтаксис: =МАКС(число1; [число2]; ...) или =МАКС(диапазон)

Примеры использования:

• =МАКС(C:C): Максимальная цена товара в столбце C.
• =МАКС(ВыручкаЗаМесяц): Самая высокая дневная выручка.

9.1.4. МИН() (MIN): "Поиск Дна"

Что делает: Находит наименьшее числовое значение в указанном диапазоне. Синтаксис: =МИН(число1; [число2]; ...) или =МИН(диапазон)

Примеры использования:

• =МИН(D2:D100): Минимальное количество товара на складе.
• =МИН(Расходы): Наименьшая статья расходов.

9.1.5. СЧЁТ() (COUNT): "Счётчик Числовых Данных"

Что делает: Считает количество ячеек в диапазоне, которые содержат числовые значения. Игнорирует пустые ячейки и ячейки с текстом. Синтаксис: =СЧЁТ(значение1; [значение2]; ...) или =СЧЁТ(диапазон)

Примеры использования:

• =СЧЁТ(E:E): Сколько числовых значений (например, цен) в столбце E.
• =СЧЁТ(Продажи_Ежедневные): Сколько дней было зарегистрировано продаж (если продажи в виде чисел).

9.1.6. СЧЁТЗ() (COUNTA): "Счётчик Заполненных Ячеек"

Что делает: Считает количество ячеек в диапазоне, которые не являются пустыми (то есть содержат любые данные: числа, текст, логические значения, ошибки). Синтаксис: =СЧЁТЗ(значение1; [значение2]; ...) или =СЧЁТЗ(диапазон)

Примеры использования:

• =СЧЁТЗ(A:A): Сколько строк с данными в столбце A (полезно для определения количества записей).
• =СЧЁТЗ(B1:B20): Сколько ответов было получено в опросе (если пустые ячейки означают отсутствие ответа).

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

9.2. Логические Функции Excel: ЕСЛИ, И, ИЛИ, НЕ

Логические функции позволяют вашей электронной таблице "думать" и "принимать решения" на основе определённых условий. Они возвращают либо ИСТИНА (TRUE), либо ЛОЖЬ (FALSE), и часто используются в комбинации друг с другом или внутри других функций (например, ЕСЛИ), чтобы создавать сложные сценарии.

9.2.1. ЕСЛИ() (IF): "Если То-то, То Сделай То-то"

Что делает: Проверяет, соблюдается ли условие, и возвращает одно значение, если условие истинно, и другое значение, если условие ложно. Это как светофор: "Если красный, стой; если зелёный, иди". Синтаксис: =ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь)

• логическое_выражение: Условие, которое проверяется (например, A1>10, B2="Закрыто").
• значение_если_истина: Что показать, если условие истинно.
• значение_если_ложь: Что показать, если условие ложно.

Примеры использования:

• =ЕСЛИ(C2>=5000; "Премия"; "Без премии")
• Объяснение: Если значение в C2 больше или равно 5000, в ячейке будет "Премия", иначе — "Без премии".
• =ЕСЛИ(D3="Готово"; "Завершено"; "")
• Объяснение: Если в D3 написано "Готово", вывести "Завершено", иначе — пустую строку.
• Вложенные ЕСЛИ (Nested IFs): Можно вкладывать ЕСЛИ друг в друга для проверки нескольких условий (но это может стать сложным и плохо читаемым, для таких случаев лучше использовать ЕСЛИМН в Excel 2019+ или другие методы).
• =ЕСЛИ(B2>90; "Отлично"; ЕСЛИ(B2>70; "Хорошо"; "Удовлетворительно"))

9.2.2. И() (AND): "Все Условия Должны Быть Истинны"

Что делает: Возвращает ИСТИНА, если все логические выражения истинны. Если хотя бы одно ложно, возвращает ЛОЖЬ. Это как для получения кредита: "Нужен и паспорт, и прописка, и справка о доходах". Синтаксис: =И(логическое_выражение1; [логическое_выражение2]; ...)

Примеры использования:

• =И(A1>0; B1<100; C1="Активен")
• Объяснение: ИСТИНА, только если A1 положительное, B1 меньше 100 И C1 равно "Активен".
• =ЕСЛИ(И(D2>100; E2<"01.01.2025"); "Выполнить"; "Отложить")
• Объяснение: Если D2 больше 100 И дата в E2 раньше 01.01.2025, то "Выполнить".

9.2.3. ИЛИ() (OR): "Хотя Бы Одно Условие Истинно"

Что делает: Возвращает ИСТИНА, если хотя бы одно из логических выражений истинно. Возвращает ЛОЖЬ, только если все выражения ложны. Это как для входа в клуб: "Нужен паспорт ИЛИ водительское удостоверение". Синтаксис: =ИЛИ(логическое_выражение1; [логическое_выражение2]; ...)

Примеры использования:

• =ИЛИ(F1="Просрочено"; G1="Приостановлено")
• Объяснение: ИСТИНА, если F1 "Просрочено" ИЛИ G1 "Приостановлено" (или оба).
• =ЕСЛИ(ИЛИ(H2="Женщина"; H2="Мужчина"); "Пол указан"; "Пол не указан")
• Объяснение: Если в H2 указано "Женщина" ИЛИ "Мужчина", то "Пол указан".

9.2.4. НЕ() (NOT): "Отрицание Условия"

Что делает: Изменяет логическое значение аргумента на противоположное. ИСТИНА становится ЛОЖЬ, ЛОЖЬ становится ИСТИНА. Это как сказать "НЕ является этим". Синтаксис: =НЕ(логическое_выражение)

Примеры использования:

• =НЕ(A1=10)
• Объяснение: ИСТИНА, если A1 НЕ равно 10.
• =ЕСЛИ(НЕ(B2="Закрыто"); "Активно"; "Неактивно")
• Объяснение: Если B2 НЕ равно "Закрыто", то "Активно".

Ваш Цифровой Аналитик: Логические функции — это ваш "цифровой мозг", который позволяет вашей таблице не просто считать, но и анализировать данные на основе сложных условий, выводя нужные вам статусы, категории или флаги. Они являются основой для создания умных и динамичных отчётов.

9.3. Текстовые Функции Excel: СЦЕПИТЬ, ЛЕВ, ПРАВСИМВ, СРЕД, ДЛСТР, НАЙТИ, ЗАМЕНИТЬ, ПРОПИСН, СТРОЧН

Текстовые функции позволяют вам манипулировать строками текста — извлекать части, объединять, заменять, изменять регистр. Это как набор инструментов для работы со словами: "ножницы", "клей", "лупа" и "трансформатор".

9.3.1. СЦЕПИТЬ() (CONCATENATE) / ОБЪЕДИНИТЬ() (TEXTJOIN) / Оператор &: "Склейка Фраз"

Что делает: Объединяет несколько текстовых строк в одну. Синтаксис:

• =СЦЕПИТЬ(текст1; [текст2]; ...)
• =ОБЪЕДИНИТЬ(разделитель; игнорировать_пустые; текст1; [текст2]; ...) (Excel 2016+ / Google Sheets)
• текст1 & текст2 (оператор)

Примеры использования:

• =СЦЕПИТЬ(A2; " "; B2): Если A2="Иван", B2="Петров", результат: "Иван Петров".
• =A2 & " " & B2: То же самое, но с оператором &. Часто более удобно.
• =ОБЪЕДИНИТЬ(" - "; ИСТИНА; C2:E2): Если C2="Москва", D2="", E2="Россия", результат: "Москва - Россия". Игнорирует пустые ячейки.

9.3.2. ЛЕВ() (LEFT): "Извлечение Начала"

Что делает: Извлекает указанное количество символов из начала текстовой строки. Синтаксис: =ЛЕВ(текст; [количество_знаков])

• текст: Текстовая строка или ссылка на ячейку с текстом.
• количество_знаков: Сколько символов извлечь слева. Если опущено, извлекается 1 символ.

Примеры использования:

• =ЛЕВ(A2; 3): Если A2="Продукт_123", результат: "Про".
• =ЛЕВ(B5; 1): Если B5="Телефон", результат: "Т".

9.3.3. ПРАВСИМВ() (RIGHT): "Извлечение Конца"

Что делает: Извлекает указанное количество символов из конца текстовой строки. Синтаксис: =ПРАВСИМВ(текст; [количество_знаков])

Примеры использования:

• =ПРАВСИМВ(A2; 3): Если A2="Продукт_123", результат: "123".
• =ПРАВСИМВ(B5; 4): Если B5="Заказ-4567", результат: "4567".

9.3.4. СРЕД() (MID): "Извлечение Середины"

Что делает: Извлекает указанное количество символов из середины текстовой строки, начиная с заданной позиции. Синтаксис: =СРЕД(текст; начальная_позиция; количество_знаков)

• начальная_позиция: Позиция первого символа, который нужно извлечь (1 - первый символ).

Примеры использования:

• =СРЕД(A2; 8; 3): Если A2="Дата: 25.07.2025", результат: "25." (начиная с 8-й позиции, 3 символа).
• =СРЕД(B5; 2; 4): Если B5="ABCD123", результат: "BCD1".

9.3.5. ДЛСТР() (LEN): "Длина Строки"

Что делает: Возвращает количество символов в текстовой строке. Синтаксис: =ДЛСТР(текст)

Примеры использования:

• =ДЛСТР(A2): Если A2="Hello World", результат: 11 (включая пробел).
• =ДЛСТР(C5): Полезно для проверки правильности ввода данных (например, длина ИНН).

9.3.6. НАЙТИ() (FIND) / ПОИСК() (SEARCH): "Поиск Иголки в Стоге Сена"

Что делает: Находит начальную позицию одной текстовой строки внутри другой. НАЙТИ чувствительна к регистру, ПОИСК — нет. Синтаксис: =НАЙТИ(искомый_текст; просматриваемый_текст; [начальная_позиция])

Примеры использования:

• =НАЙТИ("apple"; A2): Если A2="pineapple", результат: 5. Если "Apple", выдаст ошибку (регистр).
• =ПОИСК("apple"; A2): Если A2="Apple", результат: 1 (не чувствителен к регистру).
• Применение с СРЕД: =СРЕД(B2; НАЙТИ("-"; B2)+1; 99): Извлечь текст после первого дефиса. (99 — заведомо большое число, чтобы взять всё до конца).

9.3.7. ЗАМЕНИТЬ() (REPLACE): "Цифровой Ластик и Карандаш"

Что делает: Заменяет часть текстовой строки на другую. Синтаксис: =ЗАМЕНИТЬ(старый_текст; начальная_позиция; количество_знаков; новый_текст)

Примеры использования:

• =ЗАМЕНИТЬ(A2; 1; 3; "XYZ"): Если A2="ABCDEF", результат: "XYZDEF".
• =ЗАМЕНИТЬ("item-123"; 6; 3; "456"): Результат: "item-456".

9.3.8. ПРОПИСН() (UPPER) / СТРОЧН() (LOWER) / ПРОПНАЧ() (PROPER): "Изменение Регистров"

Что делает:

• ПРОПИСН(): Преобразует весь текст в верхний регистр (заглавные буквы).
• СТРОЧН(): Преобразует весь текст в нижний регистр (строчные буквы).
• ПРОПНАЧ(): Преобразует первую букву каждого слова в верхний регистр, остальные в нижний (как в заголовках). Синтаксис: =ПРОПИСН(текст)

Примеры использования:

• =ПРОПИСН(A2): Если A2="hello world", результат: "HELLO WORLD".
• =СТРОЧН(B2): Если B2="HELLO WORLD", результат: "hello world".
• =ПРОПНАЧ(C2): Если C2="john doe", результат: "John Doe".
• Польза: Важно для стандартизации данных, особенно если они вводятся из разных источников, чтобы обеспечить правильное сравнение и поиск.

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

9.4. Функции Excel Даты и Времени: СЕГОДНЯ, ТДАТА, ГОД, МЕСЯЦ, ДЕНЬ, ДАТА, РАЗНДАТ

Работа с датами и временем часто вызывает затруднения, но специализированные функции делают её интуитивно понятной. Электронные таблицы хранят даты как числа (например, 1 января 1900 года = 1), что позволяет выполнять с ними арифметические операции. Эти функции помогают преобразовывать, извлекать и работать с временными данными.

9.4.1. СЕГОДНЯ() (TODAY): "Текущий День"

Что делает: Возвращает текущую дату. Обновляется каждый раз при открытии файла или пересчёте листа. Синтаксис: =СЕГОДНЯ() (не имеет аргументов)

Примеры использования:

• =СЕГОДНЯ(): Покажет 13.07.2025 (если сегодня 13 июля 2025 года).
• =СЕГОДНЯ()+7: Покажет дату через 7 дней.
• Польза: Для автоматического отслеживания сроков, расчёта возраста документов, создания динамических отчётов.

9.4.2. ТДАТА() (NOW): "Текущие Дата и Время"

Что делает: Возвращает текущие дату и время. Обновляется так же, как СЕГОДНЯ(). Синтаксис: =ТДАТА() (не имеет аргументов)

Примеры использования:

• =ТДАТА(): Покажет 13.07.2025 10:01 (если сейчас это время).
• Польза: Для точного логирования событий, отметок о начале/конце работы.

9.4.3. ГОД() (YEAR), МЕСЯЦ() (MONTH), ДЕНЬ() (DAY): "Разбор Даты на Компоненты"

Что делает: Извлекает год, месяц или день из заданной даты. Синтаксис: =ГОД(дата), =МЕСЯЦ(дата), =ДЕНЬ(дата)

Примеры использования:

• =ГОД(A2): Если A2 содержит 25.07.2025, результат: 2025.
• =МЕСЯЦ(A2): Результат: 7.
• =ДЕНЬ(A2): Результат: 25.
• Польза: Для группировки данных по годам, месяцам, дням; для создания ежемесячных или ежегодных отчётов.

9.4.4. ДАТА() (DATE): "Сборка Даты из Компонентов"

Что делает: Создает корректную дату из заданных чисел года, месяца и дня. Синтаксис: =ДАТА(год; месяц; день)

Примеры использования:

• =ДАТА(2025; 7; 13): Результат: 13.07.2025.
• =ДАТА(C2; D2; E2): Если C2=2025, D2=7, E2=13, результат: 13.07.2025.
• Польза: Для создания дат из числовых данных, импортированных из других систем; для построения динамических календарей.

9.4.5. РАЗНДАТ() (DATEDIF - Секретная функция в Excel, открытая в Google Sheets): "Расчёт Промежутка Времени"

Что делает: Вычисляет количество дней, месяцев или лет между двумя датами. Синтаксис (Excel/Google Sheets): =РАЗНДАТ(дата_начала; дата_конца; единица_измерения)

• единица_измерения:
• "Y": Количество полных лет.
• "M": Количество полных месяцев.
• "D": Количество полных дней.
• "YD": Количество дней в текущем году (без учёта лет).
• "MD": Количество дней между месяцами (игнорируя годы).
• "YM": Количество полных месяцев (без учёта лет).

Примеры использования:

• =РАЗНДАТ(A2; СЕГОДНЯ(); "Y"): Сколько полных лет прошло с даты в A2 до сегодняшнего дня (расчёт возраста).
• =РАЗНДАТ(ДатаНачала; ДатаОкончания; "D"): Количество дней между двумя датами.
• Важно: В Excel РАЗНДАТ не отображается в списке функций, но она работает! В Google Sheets она видна.

Ваш Хронометр: Функции даты и времени позволяют вам эффективно работать с временными рядами данных, рассчитывать длительность, определять возраст, планировать сроки и анализировать события в хронологическом порядке.

9.5. Функции Ссылок и Массивов: "Навигация по Базе Данных"

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

9.5.1. ВПР() (VLOOKUP - Вертикальный Просмотр): "Искатель в Столбце"

Что делает: Ищет значение в первом столбце таблицы (диапазона) и возвращает соответствующее значение из указанного столбца в той же строке. Синтаксис: =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])

• искомое_значение: Что вы ищете (например, ID товара, имя клиента).
• таблица: Диапазон ячеек, где происходит поиск (ваша "база данных"). Важно: искомое значение должно быть в первом столбце этого диапазона!
• номер_столбца: Номер столбца в вашей "таблице", из которого нужно вернуть значение (1 - первый столбец).
• интервальный_просмотр (необязательно):
• ИСТИНА (TRUE) или 1 (по умолчанию, неточное совпадение, для отсортированных данных).
• ЛОЖЬ (FALSE) или 0 (точное совпадение, рекомендуется для большинства случаев).

Примеры использования:

• =ВПР(A2; БазаТоваров!A:C; 2; ЛОЖЬ): Найти в листе "БазаТоваров" значение из A2 (например, ID товара), найти его в первом столбце диапазона A:C, и вернуть соответствующее значение из 2-го столбца этого диапазона (например, название товара).
• =ВПР(B5; БазаКлиентов; 3; 0): Найти имя клиента в B5 в первом столбце именованного диапазона "БазаКлиентов" и вернуть его телефон (из 3-го столбца).

Ограничения ВПР: Ищет только справа налево (т.е. искомое значение должно быть в первом столбце).

9.5.2. ГПР() (HLOOKUP - Горизонтальный Просмотр): "Искатель в Строке"

Что делает: Аналогичен ВПР, но ищет значение в первой строке таблицы (диапазона) и возвращает соответствующее значение из указанной строки. Синтаксис: =ГПР(искомое_значение; таблица; номер_строки; [интервальный_просмотр])

Примеры использования:

• =ГПР("Июль"; ПродажиПоМесяцам!A1:G10; 5; ЛОЖЬ): Найти "Июль" в первой строке диапазона, и вернуть значение из 5-й строки.
• Применение: Реже, чем ВПР, но полезно для таблиц с заголовками в строках (например, расписания, отчёты по месяцам в строке).

9.5.3. ИНДЕКС() (INDEX) + ПОИСКПОЗ() (MATCH): "Магическая Комбинация: Где? Что? Который?"

Это связка функций, которая считается более мощной и гибкой, чем ВПР, так как она может искать как слева направо, так и справа налево, а также в массивах. Это как сказать: "Возьми предмет из этой коробки (ИНДЕКС), которая находится на такой-то полке (ПОИСКПОЗ)".

• ПОИСКПОЗ() (MATCH): "Поиск Номера Полки"
• Что делает: Ищет значение в одномерном диапазоне (строке или столбце) и возвращает его относительную позицию в этом диапазоне (не значение, а номер!).
• Синтаксис: =ПОИСКПОЗ(искомое_значение; просматриваемый_массив; [тип_сопоставления])
• искомое_значение: Что вы ищете.
• просматриваемый_массив: Диапазон, в котором вы ищете (одна строка или один столбец).
• тип_сопоставления:
• 0: Точное совпадение (рекомендуется).
• 1: Наибольшее значение, меньшее или равное искомому (для отсортированных данных).
• -1: Наименьшее значение, большее или равное искомому (для отсортированных в убывающем порядке).
• Пример: =ПОИСКПОЗ("Яблоки"; A1:A10; 0): Если "Яблоки" находятся в A5, результат: 5.
• ИНДЕКС() (INDEX): "Взять с Полки Номер N"
• Что делает: Возвращает значение ячейки на пересечении указанного номера строки и номера столбца в заданном диапазоне (таблице).
• Синтаксис: =ИНДЕКС(массив; номер_строки; [номер_столбца])
• массив: Диапазон ячеек, из которого нужно извлечь значение.
• номер_строки: Номер строки в этом массиве.
• номер_столбца (необязательно): Номер столбца в этом массиве.
• Комбинация ИНДЕКС+ПОИСКПОЗ:
• Сценарий: У вас есть таблица A1:D10 с "ID", "Название", "Категория", "Цена". Вы хотите найти "Цену" товара по его "ID".
• Формула: =ИНДЕКС(D1:D10; ПОИСКПОЗ(F2; A1:A10; 0))
• Объяснение:
• ПОИСКПОЗ(F2; A1:A10; 0): Ищет ID из ячейки F2 в столбце ID (A1:A10) и возвращает его номер строки (например, 5).
• ИНДЕКС(D1:D10; 5): Берёт 5-е значение из столбца "Цена" (D1:D10).
• Польза: Преодолевает ограничение ВПР с поиском только по первому столбцу. Позволяет искать в любом столбце и возвращать значение из любого другого столбца. Очень гибкая и мощная комбинация.

9.5.4. СМЕЩ() (OFFSET - Только Excel): "Динамическое Перемещение"

Что делает: Возвращает ссылку на диапазон, который смещён на определённое количество строк и столбцов относительно начальной ячейки или диапазона, и может иметь указанный размер. Это как "перейти отсюда на N шагов вправо и M шагов вниз, и взять оттуда область размером X на Y". Синтаксис: =СМЕЩ(ссылка; смещение_строк; смещение_столбцов; [высота]; [ширина])

Примеры использования (продвинутое):

• =СУММ(СМЕЩ(A1; 0; 0; 10; 1)): Суммирует диапазон A1:A10. (Не очень практично, но показывает принцип).
• =СРЗНАЧ(СМЕЩ(A1; СЧЁТЗ(A:A)-5; 0; 5; 1)): Среднее значение последних 5 заполненных ячеек в столбце A. (Очень полезно для динамических диапазонов!).

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

9.5.5. Google Sheets Аналоги / QUERY: "SQL в Таблице"

Google Sheets предлагает свои мощные аналоги для поиска и выборки данных:

• ВПР() (VLOOKUP), ГПР() (HLOOKUP), ИНДЕКС() (INDEX), ПОИСКПОЗ() (MATCH) работают так же, как в Excel.
• FILTER(): Позволяет фильтровать диапазон на основе заданных условий.
• =FILTER(A:C; A:A="Продукт А"; B:B>10): Отфильтровать столбцы A, B, C, где в столбце A "Продукт А" И в столбце B значение больше 10.
• QUERY(): Это самая мощная функция Google Sheets, которая позволяет выполнять запросы к данным, используя язык, похожий на SQL. Это как иметь собственную маленькую базу данных внутри листа.
• Синтаксис: =QUERY(данные; запрос; [заголовки])
• данные: Диапазон для запроса (например, A1:G100).
• запрос: Текстовая строка с инструкциями (начинается с SELECT, WHERE, GROUP BY, ORDER BY, LIMIT и т.д.).
• Примеры использования:
• =QUERY(A:C; "SELECT A, B WHERE C > 100 ORDER BY B DESC")
• Объяснение: Выбрать столбцы A и B из диапазона A:C, где значение в столбце C больше 100, и отсортировать результат по столбцу B в убывающем порядке.
• =QUERY(Продажи; "SELECT Категория, SUM(Продажи) GROUP BY Категория")
• Объяснение: Сгруппировать данные по "Категории" и просуммировать "Продажи" для каждой категории.
• Польза: QUERY может заменить множество комбинаций СУММЕСЛИ, СЧЁТЕСЛИ, ВПР и других функций, предоставляя невероятную гибкость и мощность для агрегирования и фильтрации данных.

Ваш Инструмент Навигации: Функции ссылок и массивов — это ваши "GPS-навигаторы" по данным. Они позволяют вам автоматизировать процесс поиска и извлечения информации из больших таблиц, создавая динамические, взаимосвязанные отчёты и панели управления. ИНДЕКС+ПОИСКПОЗ и QUERY (в Google Sheets) — это маст-хэв для любого продвинутого пользователя.

9.6. Статистические Функции Excel: СЧЁТЕСЛИ, СУММЕСЛИ, СРЗНАЧЕСЛИ (и их множественные аналоги)

Эти функции выходят за рамки простого суммирования и позволяют проводить более глубокий анализ данных на основе определённых условий. Они отвечают на вопросы типа: "Сколько было продаж, если сумма больше X?" или "Какова средняя выручка, если регион Y?".

9.6.1. СЧЁТЕСЛИ() (COUNTIF): "Счётчик с Условием"

Что делает: Считает количество ячеек в диапазоне, которые соответствуют заданному условию. Синтаксис: =СЧЁТЕСЛИ(диапазон; критерий)

• диапазон: Диапазон ячеек для подсчёта.
• критерий: Условие (число, текст, логическое выражение).

Примеры использования:

• =СЧЁТЕСЛИ(A2:A100; "Просрочено"): Сколько задач имеют статус "Просрочено".
• =СЧЁТЕСЛИ(B:B; ">1000"): Сколько значений в столбце B больше 1000.
• =СЧЁТЕСЛИ(C:C; "Иван*"): Сколько строк, где в столбце C имя начинается на "Иван" (звёздочка * — это подстановочный знак для любого количества символов).

9.6.2. СУММЕСЛИ() (SUMIF): "Суммирование с Условием"

Что делает: Суммирует значения в диапазоне, если соответствующие ячейки в другом диапазоне (или том же самом) удовлетворяют заданному условию. Синтаксис: =СУММЕСЛИ(диапазон_критериев; критерий; [диапазон_суммирования])

• диапазон_критериев: Диапазон, где проверяется условие.
• критерий: Условие.
• диапазон_суммирования (необязательно): Диапазон, чьи значения нужно суммировать. Если опущен, суммируется диапазон_критериев.

Примеры использования:

• =СУММЕСЛИ(A:A; "Продажи"; B:B): Суммировать значения из столбца B, где в столбце A написано "Продажи".
• =СУММЕСЛИ(C2:C50; ">1000"): Суммировать все значения в C2:C50, которые больше 1000.

9.6.3. СРЗНАЧЕСЛИ() (AVERAGEIF): "Среднее с Условием"

Что делает: Вычисляет среднее арифметическое для ячеек, которые удовлетворяют заданному условию. Синтаксис: =СРЗНАЧЕСЛИ(диапазон_критериев; критерий; [диапазон_усреднения])

Примеры использования:

• =СРЗНАЧЕСЛИ(D:D; "Восток"; E:E): Средняя выручка для региона "Восток".
• =СРЗНАЧЕСЛИ(F2:F100; "<500"; F2:F100): Среднее значение всех чисел в диапазоне F2:F100, которые меньше 500.

9.6.4. Множественные Условия (Excel: СЧЁТЕСЛИМН, СУММЕСЛИМН, СРЗНАЧЕСЛИМН / Google Sheets: COUNTIFS, SUMIFS, AVERAGEIFS)

Что делает: Аналогичны предыдущим функциям, но позволяют задавать несколько условий одновременно. Синтаксис: =СУММЕСЛИМН(диапазон_суммирования; диапазон_критериев1; критерий1; [диапазон_критериев2; критерий2]; ...)

• Важно: В функциях с "МН" (IFS) первым аргументом идёт диапазон суммирования (или усреднения), а затем пары "диапазон критериев" + "критерий".

Примеры использования:

• =СУММЕСЛИМН(Выручка; Регион; "Север"; Месяц; "Январь")
• Объяснение: Суммировать "Выручку", если "Регион" = "Север" И "Месяц" = "Январь".
• =СЧЁТЕСЛИМН(Задачи; Статус; "В работе"; Приоритет; "Высокий")
• Объяснение: Посчитать количество задач, которые находятся "В работе" И имеют "Высокий" приоритет.

Ваш Аналитический "Фильтр": Эти статистические функции — ваш аналитический двигатель. Они позволяют вам быстро получать агрегированные данные (суммы, средние, количества) для очень специфических сегментов информации, отвечая на сложные бизнес-вопросы без ручной фильтрации.

9.7. Финансовые Функции Excel: (краткий обзор, если актуально для вашей аудитории)

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

• Для кого актуально: Если вы когда-либо задумывались о кредитах, ипотеке, инвестициях, пенсионных накоплениях или оцениваете рентабельность проектов, эти функции станут вашими незаменимыми помощниками. Даже краткое понимание их работы откроет вам новые горизонты для финансового планирования.

9.7.1. ПЛТ() (PMT): "Рассчитываем Комфортный Ежемесячный Платеж"

Что делает: Эта функция помогает вам понять, сколько вам придётся платить регулярно (например, ежемесячно или ежеквартально), если вы берёте кредит или, наоборот, вносите средства в инвестицию. Она идеальна для расчёта аннуитетных платежей — когда сумма платежа остаётся одинаковой на протяжении всего срока.

Синтаксис: =ПЛТ(ставка_за_период; общее_число_периодов; текущая_стоимость_кредита; [будущая_стоимость]; [тип_платежа])

• ставка_за_период: Ваш годовой процент по кредиту, разделённый на количество платежей в году. Например, если 12% годовых и 12 платежей в год, то 12%/12.
• общее_число_периодов: Общее количество платежей за весь срок. Например, 5 лет * 12 месяцев = 60 платежей.
• текущая_стоимость_кредита: Сумма, которую вы берёте в долг или инвестируете сейчас. (Обычно вводится как положительное число).
• будущая_стоимость (необязательно): Какую сумму вы хотите получить или какой долг должен остаться после всех платежей. По умолчанию 0 (полное погашение долга или накопление без остатка).
• тип_платежа (необязательно): 0 (платеж в конце каждого периода) или 1 (платеж в начале периода). По умолчанию 0.

Метафора: Представьте, что вы хотите съесть пирог, но не сразу, а равными частями. ПЛТ рассчитывает, насколько большим должен быть каждый кусочек, чтобы пирог закончился в определённый срок, учитывая, что пирог "растёт" (проценты).

Пример использования: Вы хотите взять новый смартфон в кредит стоимостью 60 000 руб. на 2 года под 18% годовых. Какой будет ежемесячный платёж?

• Ставка за период: 18%/12
• Общее число периодов: 2*12 (24 месяца)
• Текущая стоимость: 60000

Формула: =ПЛТ(18%/12; 2*12; 60000) Результат: -2995.66 (обратите внимание на отрицательное значение, так как это денежный отток для вас). То есть, ваш ежемесячный платёж составит 2995.66 руб.

9.7.2. ВСД() (IRR - Внутренняя Ставка Доходности): "Термометр Привлекательности Инвестиции"

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

Синтаксис: =ВСД(диапазон_денежных_потоков; [предполагаемая_ставка])

• диапазон_денежных_потоков: Список всех ваших денежных операций, начиная с первоначальных инвестиций (которые всегда должны быть отрицательными, так как это отток денег!) и далее всех будущих поступлений (положительные значения) или расходов (отрицательные). Потоки должны идти в хронологическом порядке.
• предполагаемая_ставка (необязательно): Ваша гипотеза о возможной ВСД. Иногда помогает Excel/Google Sheets быстрее найти правильный ответ, если функция "путается" со сложными потоками.

Метафора: Представьте, что вы сажаете финансовое семечко (инвестицию) и ожидаете урожай (будущие доходы). ВСД — это "потенциал роста" этого семечка, выраженный в процентах. Если потенциал выше, чем у других "семечек", то оно перспективнее.

Пример использования: Вы вложили 500 000 руб. в проект. Ожидаете следующие годовые доходы: 100 000 руб. в 1-й год, 150 000 руб. во 2-й, 200 000 руб. в 3-й, 120 000 руб. в 4-й. Пусть эти значения находятся в ячейках A1:A5:

• A1: -500000 (начальная инвестиция)
• A2: 100000
• A3: 150000
• A4: 200000
• A5: 120000

Формула: =ВСД(A1:A5) Результат: Например, 9.22%. Это означает, что внутренняя ставка доходности вашего проекта составляет 9.22% годовых.

9.7.3. ПС() (PV - Приведённая Стоимость): "Сколько Сегодня Стоят Будущие Деньги?"

Что делает: Эта функция отвечает на критический вопрос: "Сколько денег я должен вложить сегодня, чтобы получить определённую сумму в будущем или чтобы обеспечить себе серию будущих выплат?" Она переводит будущие денежные потоки в их эквивалент на сегодняшний день, учитывая инфляцию, доходность или ставку дисконтирования.

Синтаксис: =ПС(ставка_за_период; общее_число_периодов; сумма_периодического_платежа; [будущая_стоимость]; [тип_платежа])

• ставка_за_период: Процент, под который "обесцениваются" или "растут" деньги за период.
• общее_число_периодов: Общее количество периодов, в течение которых происходят платежи или до достижения будущей стоимости.
• сумма_периодического_платежа: Регулярная сумма, которую вы платите или получаете. Если платежей нет, укажите 0.
• будущая_стоимость (необязательно): Какая сумма будет получена (или сколько долга останется) в конце всех периодов. Если вы не указываете сумма_периодического_платежа, то этот аргумент обязателен.
• тип_платежа (необязательно): 0 (в конце периода) или 1 (в начале).

Метафора: Представьте, что кто-то пообещал вам 1000 долларов через год. Но 1000 долларов через год — это не то же самое, что 1000 долларов сегодня (из-за инфляции или упущенной возможности инвестировать). ПС — это функция, которая "переносит" эту будущую 1000 долларов в настоящее, показывая её истинную ценность сейчас.

Пример использования: Сколько вам нужно инвестировать сегодня, чтобы накопить 1 000 000 руб. через 15 лет, если вы ожидаете среднюю годовую доходность в 8%, и при этом не планируете никаких регулярных пополнений?

• Ставка за период: 8% (если пополнений нет, можно использовать годовую ставку)
• Общее число периодов: 15 (лет)
• Сумма периодического платежа: 0 (нет регулярных пополнений)
• Будущая стоимость: 1000000

Формула: =ПС(8%; 15; 0; 1000000) Результат: -315241.70 (показывает, что вам нужно вложить 315 241.70 руб. сейчас, чтобы достичь цели).

9.7.4. БС() (FV - Будущая Стоимость): "Сколько Будет Стоить?"

Что делает: Вычисляет будущую стоимость инвестиции на основе периодических постоянных платежей и постоянной процентной ставки. Синтаксис: =БС(ставка; кпер; плт; [пс]; [тип])

Пример использования:

• Если вы инвестируете 1000 руб. ежемесячно в течение 20 лет под 7% годовых, сколько у вас будет денег?
• =БС(7%/12; 20*12; -1000; 0) (0 - начальные инвестиции, -1000 - исходящий платеж).

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

Итог

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

• Базовые математические операции, которые позволяют быстро агрегировать и анализировать числовые массивы.
• Логические функции, которые превращают вашу таблицу в "мыслящую" систему, способную принимать решения на основе заданных условий.
• Текстовые функции, дающие вам полный контроль над текстовыми данными: их очисткой, форматированием и извлечением нужных фрагментов.
• Функции даты и времени, которые позволяют вам управлять хронологией данных, рассчитывать сроки и анализировать временные ряды.
• Мощнейшие функции ссылок и массивов (ВПР, ИНДЕКС+ПОИСКПОЗ, QUERY в Google Sheets), которые превращают вашу таблицу в полноценную базу данных с возможностью поиска и выборки информации.
• Статистические функции (СУММЕСЛИ, СЧЁТЕСЛИ и их множественные аналоги), позволяющие проводить точный анализ данных с учётом множества условий.
• И даже получили представление о финансовых функциях, если вам предстоит работать с денежными потоками и инвестициями.

Теперь вы не просто вводите данные; вы заставляете их работать на вас, взаимодействовать, анализировать себя и выдавать готовые решения. Ваши электронные таблицы стали не просто хранилищами, а динамичными, интеллектуальными системами, готовыми к любым вызовам.

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

Таблицы cопоставление имён функций Excel (кириллица ↔ латиница)

СУММ() ↔ SUM()

СРЗНАЧ() ↔ AVERAGE()

МАКС() ↔ MAX()

МИН() ↔ MIN()

СЧЁТ() ↔ COUNT()

СЧЁТЗ() ↔ COUNTA()

ЕСЛИ() ↔ IF()

И() ↔ AND()

ИЛИ() ↔ OR()

НЕ() ↔ NOT()

СЦЕПИТЬ() ↔ CONCATENATE()

ОБЪЕДИНИТЬ() ↔ TEXTJOIN()

ЛЕВ() ↔ LEFT()

ПРАВСИМВ() ↔ RIGHT()

СРЕД() ↔ MID()

ДЛСТР() ↔ LEN()

НАЙТИ() ↔ FIND()

ПОИСК() ↔ SEARCH()

ЗАМЕНИТЬ() ↔ REPLACE()

ПРОПИСН() ↔ UPPER()

СТРОЧН() ↔ LOWER()

ПРОПНАЧ() ↔ PROPER()

СЕГОДНЯ() ↔ TODAY()

ТДАТА() ↔ NOW()

ГОД() ↔ YEAR()

МЕСЯЦ() ↔ MONTH()

ДЕНЬ() ↔ DAY()

ДАТА() ↔ DATE()

РАЗНДАТ() ↔ DATEDIF()

ВПР() ↔ VLOOKUP()

ГПР() ↔ HLOOKUP()

ИНДЕКС() ↔ INDEX()

ПОИСКПОЗ() ↔ MATCH()

СМЕЩ() ↔ OFFSET()

СЧЁТЕСЛИ() ↔ COUNTIF()

СУММЕСЛИ() ↔ SUMIF()

СРЗНАЧЕСЛИ() ↔ AVERAGEIF()

СЧЁТЕСЛИМН() ↔ COUNTIFS()

СУММЕСЛИМН() ↔SUMIFS()

СРЗНАЧЕСЛИМН() ↔ AVERAGEIFS()

ПЛТ() ↔ PMT()

ВСД() ↔ IRR()

ПС() ↔ PV()

БС() ↔ FV()

< - Глава 8. Работа с Excel. Основы работы с формулами

Глава 10. Работа с Excel. Расширенные возможности работы с формулами ->