AndreyMitrokhin

1. Вложенные заголовки: невозможно сравнить показатели по продукту/региону.

2. Данные на пересечении строк и столбцов: сложно строить сводные таблицы.

3. Несколько строк заголовков: еxcel не распознаёт структуру правильно.

Решение: нужна «плоская таблица» — с одной строкой заголовков и одним типом данных в каждом столбце .

Шаг 1: Подготовка данных в Excel

1.1 Создание именованного диапазона

Вместо умной таблицы используем именованный диапазон (многие пользователи не любят умные таблицы, да тут нам они особо и не нужны):

1. Выделите всю таблицу

2. В имени ячейки (слева сверху) введите понятное имя, например Данные

3. Обязательно нажмите Enter после ввода имени

1.2 Загрузка в Power Query

Для Excel 2019+:

• Вкладка Данные - группа Получить и преобразовать данные - Из таблицы/диапазона

Для Excel 2016:

• Вкладка Данные - группа Загрузить & преобразовать - Создать запрос

Для Excel 2010/2013: нужно отдельно скачать надстройку Power Query. Но с официального сайта Microsoft уже не получится это сделать.

Шаг 2: Работа с заголовками (первый этап)

Заголовки в нашей таблице занимают три строки:

2.1 Оставляем только строки заголовков

1. В редакторе Power Query справа на панели Примененные шаги удалите шаги: «Изменённый тип» и «Повышенные заголовки»

2. Вкладка Главная - группа Сократить строки - Сохранить строки - Сохранить верхние строки

3. Укажите 3 (количество строк заголовков)

2.2 Транспонирование таблицы

Цель: превратить горизонтальный массив в вертикальный.

1. Вкладка Преобразование - Транспонировать

2.3 Заполнение пустых значений

1. Выделите столбцы с типом данных null (через Ctrl)

2. Вкладка Преобразование - Заполнить - Заполнить вниз

2.4 Объединение столбцов в один

1. Выделите три столбца (зажмите Shift)

2. Вкладка Добавление столбца - Объединить столбцы

1. Выберите Пользовательский разделитель

2. Важно: используйте разделитель, которого нет в данных (например, -- двойной дефис)

3. Нажмите ОК, затем удалите другие столбцы (правая кнопка - Удалить другие столбцы)

2.5 Обратное транспонирование

1. Вкладка Преобразование - Транспонировать

2. Теперь столбец превратился в строку — работа с заголовками завершена

Шаг 3: Работа с данными (второй этап)

3.1 Возврат к исходным данным

Важный приём Power Query — работа с шагами:

1. Перейдите к последнему шагу в истории

2. В строке формул нажмите f(x) - Добавить шаг

3. Замените ссылку на Источник (название шага с полными данными). У вас это может быть другое название. Важно, чтобы вы выбрали именно тот шаг, на котором была полная таблица.

4. Соблюдайте регистр! Power Query чувствителен к заглавным буквам

3.2 Удаление строк заголовков

1. Вкладка Главная - Удалить строки - Удалить верхние строки

2. Удалите 3 строки (заголовки)

3. Отфильтруйте строку «Итого»: правая кнопка - Текстовые фильтры - Не содержит - Итого

3.3 Разделение столбца «Регион + Продукт»

Создаём два столбца из одного:

1. Выделите столбец Column1

2. Вкладка Добавление столбца - Условный столбец

3. Логика: Если Column1 не содержит Продукт то Column1

4. Нажмите ОК

Теперь заполните столбец вниз:

• Выделяем столбец. Вкладка Преобразование - Заполнить - Заполнить вниз

3.4 Удаление строк «Итого» через чередование

Если структура фиксирована (регион + 4 продукта или что-то в этом роде):

1. Вкладка Главная - Удалить строки - Удалить чередующиеся строки

2. Первая строка для удаления: 1

3. Количество строк для сохранения: 4

4. Power Query будет: удалять 1 строку, сохранять 4, удалять 1, сохранять 4... и так до конца документа

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

Шаг 4: Объединение этапов и финальная обработка

4.1 Конкатенация (объединение) двух таблиц

Объединяем таблицу заголовков и таблицу данных:

1. Перейдите к последнему шагу

2. Нажмите f(x) → Добавить шаг

3. В строке формул: =#"Удалённые чередующиеся строки" & #"Транспонированная таблица"

4. Используйте амперсанд (&) для конкатенации

4.2 Повышение заголовков

1. Нажмите кнопку слева: Использовать первую строку в качестве заголовков

2. Переместите столбец Регион в начало (перетащите)

3. Переименуйте столбцы: Продукт, Регион

4.3 Магия Unpivot (отменить свёртывание столбцов)

Ключевой шаг всей операции:

1. Выделите столбцы Регион и Продукт (которые нужно оставить как есть). Это те столбцы, которые разворачивать не нужно.

2. Правая кнопка мыши по любому выделенному столбцу - Отменить свёртывание других столбцов

3. Теперь показатели превратились в строки

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

1. Выделите объединённый столбец

2. Вкладка Преобразование - Разделить столбец По разделителю

3. Пользовательский разделитель: -- (тот же, что использовали ранее)

4. Нажмите ОК

4.5 Настройка типов данных

• Выделите текстовые столбцы - иконка текста

• Последнему столбцу (значения) - тип Десятичное число (или тот формат, который вам нужен)

• Проверьте, чтобы все типы соответствовали ожидаемым данным

Шаг 5: Выгрузка обратно в Excel

1. Файл - Закрыть и загрузить в...

2. Выберите Таблица - на существующий лист

3. Укажите ячейку (например, J1)

4. Нажмите ОК

Повторное использование запроса для новых данных

Если приходит похожая таблица (новая неделя, другие регионы/продукты):

1. В Excel откройте Данные - Запросы и подключения

2. В запросе измените шаг Источник: вместо Данные напишите Данные_новые (предварительно присвоив диапазону с новой таблицей такое имя)

3. Нажмите Enter

4. Выгрузите данные обратно в Excel

5. Готово! Все шаги применятся автоматически

Если меняется структура таблицы

Если количество продуктов различается (где-то 3, где-то 4) или ещё какая-то логика таблицы меняется, то нужно в запросе изменить/удалить нужный шаг. Например:

• Удалите шаг «Удалённые чередующиеся строки»

• Вместо этого используйте фильтр: правая кнопка - Текстовые фильтры - Не содержит - Итого

• Power Query запомнит новый шаг

Преимущества подхода: что вы получаете

1. Плоская таблица: можно строить сводные таблицы

2. Наглядный анализ: сравнивать план/факт по продукту, региону, показателю в сводной

3. Автоматизация: один раз настроил — используешь бесконечно

4. Быстрое обновление: поправил данные в источнике - правой кнопкой мыши по любой ячейке таблицы - Обновить

Заключение

Получилось много букв и картинок, но таков путь. Решение не унифицированное: у вас может быть другое количество строк заголовков, другая структура таблицы и так далее. Но главное, это понять суть того, как мы можем сделать это в Power Query. На самом деле, проделав это пару раз, поняв логику своей таблицы и решения, в дальнейшем вы очень сильно упрощаете и ускоряете работу с такими вот чудесными таблицами. Спасибо всем, кто осилил мою простыню :) Искренне надеюсь, что кому-то было полезно.

• Как прописать: =АГРЕГАТ(9; 6; ваш_диапазон).

• Что значат цифры: 9 — это команда «Суммировать», а 6 — та самая магическая настройка «Игнорировать ошибки».

• Бонус: Она также умеет игнорировать скрытые строки, если вы решите что-то отфильтровать.

2. Формула массива (для любителей классики).

Если вы хотите использовать старую добрую СУММ, вам придется объединить ее с проверкой на вшивость ошибки.

• Конструкция: =СУММ(ЕСЛИОШИБКА(диапазон; 0)).

• Важно: Если у вас старая версия Excel (2019 и ниже), не забудьте нажать Ctrl + Shift + Enter после ввода, чтобы формула превратилась в формулу массива. Теперь Excel сначала заменит все ошибки на нули в уме, а потом всё сложит.

3. СУММЕСЛИМН (метод для терпеливых)

Можно сказать Экселю буквально: «Сложи мне всё, что НЕ равно ошибке».

• Минус: вам придется прописать каждую ошибку вручную. Выглядит это примерно так: ...; диапазон; "<>#Н/Д"; диапазон; "<>#ССЫЛКА!".

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

4. Промежуточные итоги + Фильтр (метод «на коленке»)

Если новые или сложные формулы — это не ваше, можно пойти по пути наименьшего сопротивления.

• Шаг 1: используйте функцию =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; диапазон) (SUBTOTAL).

• Шаг 2: просто отфильтруйте столбец, сняв галочки со всех ошибок.

• Результат: функция пересчитает сумму только по видимым ячейкам. Быстро, просто, но требует ручной фильтрации.

Итоговый совет:

Если ошибки в таблице — это временное явление (например, данные еще не подтянулись), используйте АГРЕГАТ. Это самый быстрый и простой (с моей точки зрения) способ.

На этом всё. Всем спасибо, кто дочитал. Надеюсь, было полезно и кому-то поможет :) Если знаете другие интересные способы решить подобную задачу, делитесь в комментариях.

Иногда обычная замена пробела не работает. Это значит, что в ячейке засел «неразрывный пробел».

• Трюк: Скопируйте этот странный символ прямо из ячейки и вставьте в поле «Найти». Или используйте код Alt + 0160 (на цифровой клавиатуре). В поле «Заменить на» укажите обычный пробел или иной символ, который вам нужен.

3. Укрощение «двухэтажных» строк

Текст в ячейке переносится на новую строку, и это бесит?

• Магия: В поле «Найти» нажмите Ctrl + J (это невидимый символ переноса строки). В поле «Заменить на» поставьте обычный пробел. Один клик — и весь текст выстроится в стройную шеренгу.

4. Массовое «обрезание» текста

Нужно убрать всё, что идет после дефиса или скобки?

• Используем звездочку: Напишите в поиске -* (дефис и звездочка). Звездочка означает «любое количество любых символов». Excel найдет дефис и всё, что за ним следует, и удалит это под корень, если оставить поле «Заменить на» пустым.

5. Переезд ссылок на новый год

У вас сотни формул ссылаются на лист «2025», а наступил «2026»? Не нужно менять каждую вручную. Или через "Изменить связи", в случае, если изменить нужно только в конкретных столбцах/ячейках.

• Хитрость: Заменяйте ]2025 на ]2026. В параметрах поиска выберите «Искать в: Книге», и Excel перепишет историю (точнее, ссылки) во всем файле сразу.

6. Дизайнерская замена (поиск и замена по цвету!)

Может, не самый гибкий и продвинутый вариант менять цвет ячеек (эффективнее сделать это через условное форматирование), но пройти мимо не мог . Предварительно нужно нажать кнопку Параметры в окне поиска и замены.

• Кейс: Хотите все ячейки со словом «Выполнено» сделать зелеными? В параметрах замены выберите нужный формат (цвет, шрифт, границы). Можно даже «пипеткой» выбрать формат прямо из образцовой ячейки. В поле «Заменить на» указываете текст (если нужно) и формат, который хотите получить.

7. Как найти саму «звездочку»?

Если вы попробуете найти и удалить символ *, Excel решит, что вы хотите удалить вообще всё содержимое ячеек (помним про п. 4).

• Секретный символ: Поставьте перед звездочкой тильду — ~*. Тильда говорит программе: «Эй, я ищу именно этот знак, а не использую его как команду!». Это же работает и для поиска знака вопроса ~?.

Главный совет:

Всегда выделяйте нужный диапазон перед заменой. Иначе Excel с энтузиазмом исправит всё на всех листах, и придется судорожно жать Ctrl + Z.

Вот, пожалуй, и всё, что я хотел написать. Конечно, наверняка есть ещё интересные и нестандартные способы при работе с этим инструментом. Поделитесь ими в комментариях :) Как обычно, спасибо всем, кто осилил данный пост.

Надеюсь, хоть что-то хоть для кого-то было полезным :)

2. Несколько условий к одному столбцу.

Хотите посчитать продажи с 5 по 9 марта? Нельзя просто перечислить условия через точку с запятой. Или прописать так, как мы делали это в школе: 5 <= x <= 9. Excel требует соблюдения «парности».

• Правило: даже если вы ищете по одному и тому же столбцу (например, Даты), этот столбец нужно указывать для каждого условия отдельно. Сначала «где ищем — больше 5», потом снова «где ищем — меньше 9».

3. Следим за размером диапазонов.

Ваши диапазоны должны быть как близнецы: строго одного размера. Если один столбец у вас с 1-й по 11-ю строку, а второй — со 2-й по 12-ю, функция просто откажется работать. Excel — перфекционист, он не умеет сопоставлять кривые диапазоны.

Правильно:

4. Даты, которые не даты (текст вместо чисел).

Иногда дата выглядит как дата, но... это текст.

• Как проверить: если дата прижалась к левому краю ячейки — это шпион.

• Лечение: используйте «поиск и замену» (Ctrl + H): замените точку на точку. Это заставит Excel «переварить» данные и признать их законными датами.

5. Невидимые пробелы (Груша vs Груша_ )

Если вы ищете «Груши», а в таблице написано «Груши » (с пробелом в конце), Excel их не найдет. Он понимает всё буквально.

• Лайфхак: используйте символ * (звездочка). Условие "*Груши*" найдет фрукт, даже если вокруг него куча лишних пробелов или пояснений.

6. Ловушка «ИЛИ».

СУММЕСЛИМН работает по принципу «И». То есть она ищет ячейку, которая ОДНОВРЕМЕННО и Яблоко, и Груша. Таких чудес селекции в обычных таблицах нет.

Что делать: либо складывать две функции СУММЕСЛИМН.

либо использовать продвинутый финт ушами с функцией СУММПРОИЗВ (там можно настроить логику «ИЛИ» через сложение массивов).

7. Ссылки на ячейки других книг.

Если вы ссылаетесь на другую книгу (другой файл Excel), СУММЕСЛИМН будет работать только до тех пор, пока тот файл открыт. Как только вы его закроете и обновите формулу — получите ошибку.

• Вывод: либо держите оба файла открытыми, либо переезжайте на более стабильные способы связи данных (например, тот же Power Query).

Бонус: помощь при отладке любой формул.

Если формула ведет себя странно, используйте «Вычислить формулу» на вкладке «Формулы». Это как рентген: вы увидите пошагово, где Excel превращает вашу гениальную задумку в ошибку или 0.

Заключение.

Наверное, это не все ошибки, но, думаю, самые основные. Спасибо всем, кто дочитал. В комментариях делитесь своими ошибками и способами их решения.

Всем безошибочных формул! :)

Способ №1: Метод «Ctrl+G» (для тех, кто ценит классику)

Этот способ хорош, если вам нужно навести порядок здесь и сейчас.

Как делать: выделяем весь столбец (лучше тот, где точно должны быть данные). Жмем сочетание Ctrl + G, слева внизу выбираем "Выделить" - далее "Пустые ячейки" - Ок.

Финал: когда Excel подсветит все «дырки», жмем Ctrl + (минус) или правой кнопкой мыши по любой выделенной ячейке, потом "Удалить". В следующем окне выбираем "Строку".

Автоматизация: если вы делаете это чаще, чем слышите "сикс севен" от школьников, запишите это действие в Макрос. Сохраните его в «Личную книгу макросов», и тогда очистка таблицы будет занимать ровно одну секунду в любом вашем файле.

ВАЖНО!!! ДАЛЬНЕЙШИЕ ДЕЙСТВИЯ ВЫ ДЕЛАЕТЕ НА СВОЙ СТРАХ И РИСК. АВТОР НЕ НЕСЁТ ОТВЕТСТВЕННОСТИ ЗА НЕПРАВИЛЬНО ЗАПИСАННЫЙ МАКРОС.

Чтобы записать макрос:

1. Включаем вкладку Разработчик (слева наверху Файл - Параметры - Настроить ленту - Разработчик)

2. На вкладке Разработчик нажимаем кнопку Запись макроса.

Сохраняем макрос в Личную книгу макросов, чтобы работал везде.

!!! НАСТОЯТЕЛЬНО РЕКОМЕНДУЮ ДЕЛАТЬ ЭТО С КОПИЕЙ ТАБЛИЦЫ ИЛИ ВООБЩЕ С КОПИЕЙ ФАЙЛА. ТО, ЧТО СДЕЛАНО МАКРОСОМ, НЕ ВЫРУБИШЬ И ТОПОРОМ (ОТМЕНА ДЕЙСТВИЯ НЕ РАБОТАЕТ) !!!

3. Теперь Excel записывает КАЖДОЕ ваше действие, которое вы совершаете в программе. Поэтому без каких-либо лишних движений повторяем действия из способа 1.

4. Как только строки удалены, запись макроса нужно остановить, нажав Остановить запись на вкладке Разработчик.

5. Чтобы запустить макрос, на вкладке Разработчик выбираем Макросы, далее наш макрос - Выполнить.

Способ №2: «СЧЁТЗ» и фильтры (для подозрительных и щепетильных)

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

• Логика: создаем столбец «Проверка» и пишем формулу =СЧЁТЗ(диапазон_строки). Она посчитает, сколько ячеек в строке НЕ пустые.

• Действие: фильтруем таблицу по этому столбцу, оставляя только "0" (абсолютная пустота). Удаляем отфильтрованное и вуаля — ваша совесть и данные чисты.

Способ №3: Power Query (для тех, кто готов к новому и неизведанному)

Если ваша таблица обновляется постоянно (например, выгрузка из бухгалтерии), забудьте про ручную работу. Пусть за вас страдает Power Query.

1. Необходимо загрузить данные в Power Query. Но есть нюанс. Если вы загружаете данные, которые находятся в этой же книге, то Power Query автоматически преобразует их в "умную" таблицу. Если вы их недолюбливаете, то можно пойти на небольшую хитрость (если вам всё равно, то переходите к пункту 2). Чтобы не превращать таблицу в «умную», выделите диапазон и дайте ему имя (например, "Отчет"). Можно выделить чуть с запасом, если в будущем таблица будет дополняться.

2. Загружаем этот диапазон в Power Query через вкладку "Данные". Выбираем наш именованный диапазон и нажимаем "Из таблицы/диапазона".

3. Там находим кнопку "Удалить строки" - "Удалить пустые строки". Будут удалены только полностью пустые строки.

4. Выгружаем данные обратно с помощью Закрыть и загрузить.

Результат: теперь при добавлении новых данных в исходник вам достаточно нажать правой кнопкой мыши по любой ячейке выгруженной таблицы - "Обновить". Никаких лишних телодвижений! Все пустые строки будут автоматически удалены.

Краткая выжимка.

• Нужно один раз? Ctrl + G.

• Делаете это каждый день в разных файлах? Макрос.

• Работаете с одним и тем же отчетом? Power Query.

Заключение.

Огромное спасибо всем, кто осилил данную простыню. Опять же, я не рассказал про что-то новое, но вдруг кому-то данные советы сделают жизнь чуть проще :) Если знаете другие классные способы удалять пустые строки - делитесь ими в комментариях. И помните: пустая строка в Excel — это не просто место для отдыха ваших глаз, это потенциальная ошибка в отчете. Чистых вам таблиц!

Достаточно часто встречаю это в файлах, которые мне присылают. Условное форматирование - очень увлекательный и полезный инструмент. Но и довольно ресурсоёмкий. Особенно, если используется какая-то формула. И если создать правило целиком для столбца, то высока вероятность того, что файл будет тормозить.

Также проверьте, нет ли в принципе тех правил, которые уже старые и ненужные. В случае нахождения таких, удаляйте их.

Что делать?

Тут два варианта.

1. Если правило нужно, то меняйте диапазон на конкретный, выделив только те ячейки, которые нужно разукрасить.

2. Если правило не нужно, то удаляйте его без какого-либо сожаления.

2. Проверяем активный диапазон.

Тут вот какое дело. Не всегда последняя строка таблицы является последней активной ячейкой, которую видит Excel. Проверить это очень легко: нажимаем Ctrl+End и смотрим, в какой ячейке оказались. Если далеко-далеко внизу, то это как раз ваш случай.

Что делать?

Нужно удалить все неиспользуемые ячейки. Выделяем все строки под таблицей (проще всего сделать с помощью Ctrl+Shift+Enter), далее удаляем строки. ВАЖНО! После удаления нужно обязательно сохранить книгу! Сохранили? Отлично, проверяем ещё раз, нажав сочетание Ctrl+End.

3. Криво преобразовали диапазон в "умную" таблицу.

Проверяя различные работы не так уж и редко с этим сталкивался. При преобразовании данных в "умную" таблицу (Главная - Стили - Форматировать как таблицу или Ctrl+T) пользователь выбирает целиком столбцы. Никогда. Нет, не так. НИКОГДА так делайте. Моментально файл начнёт тормозить.

Что делать?

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

Но будьте готовы к тому, что операция это долгая (минута-две).

4. Ссылки на ячейки других книг.

Да, как бы грустно это не звучало, но если в книге у вас много (тысячи тысяч) ссылок на ячейки других книг, то файл будет тормозить.

Что делать?

1. Проверить (Данные - Запросы и подключения - Workbook links (Изменить связи)), а все ли связи вам нужны. Если ответ отрицательный, то разрываем связь.

2. Если связи нужны, то:

а) Держите все связанные книги открытыми (да, это может быть дико не удобно, но файл будет работать быстрее).

б) Отключите автоматический пересчёт формул.

В принципе, это вообще универсальный вариант, чтобы Excel начал работать быстрее. При включении этого параметра формулы будут пересчитываться только тогда, когда пользователь этого захочет. Например, при сохранении книги. Или при нажатии на кнопки Произвести вычисления (только на этом листе) или Пересчёт (формулы пересчитаются во всей книге). Но помните, что данная настройка работает для всех ваших файлов. То есть в одном файле поставили ручной пересчёт, в остальных будет тоже самое. И ещё важный момент. Когда вы запустите пересчёт, Excel зависнет. Ведь ему нужно будет все изменения пересчитать. Но зависать он будет только тогда, когда вы ему это разрешите, а не каждый раз при выполнении какой-либо операции на листе.

5. Сложные формулы и/или огромное количество формул.

По поводу огромного количества формул нужно иметь в виду, что если у вас в книге их сотни тысяч, то хоть ты тресни, а Excel будет тормозить (степень тормознутости зависит от вашего железа). По поводу сложных формул есть моменты, которые вы можете оптимизировать.

Что делать?

1. Если формул много, то отключайте автоматический пересчёт формул (см. пункт выше).

2. Если формулы сложные, то можно их попробовать оптимизировать. Например, у меня вот такая формула массива:

Копирую я её всего-то на 1000 строк. Но в таком виде данная операция занимает на моём железе (не самое слабое - i7 12700kf, 64 гига оперативки) около 15 секунд. А всё почему? Потому что в формуле я указывал диапазоны столбцами. Если формулу переписать вот так:

То работать она станет в десятки (!!!) раз быстрее. Так что выделение целиком столбцов в формулах - это стильно, модно и молодёжно, сам постоянно так делаю, но если формула сложная, то старайтесь выделять ячейки аккуратно.

6. Изображения.

Кто же не любит красивые картинки в Excel? :) В большинстве случаев они там не нужны, но их всё равно добавляют. Или "случайно как-то само появилось". Так вот изображения очень сильно раздувают размер файла.

Что делать?

1. Если изображения всё-таки нужны, то банально сжать их размер. Выделяем их, а потом вот тыкаем сюда и выбираем Электронная почта:

Кстати, выделить разом все изображения можно следующим образом: нажимаем Ctrl+G - в следующем Выделить - в следующем окне Объекты.

2. Если изображения не нужны, то просто их удаляем с глаз долой, из Excelя вон.

7. "Летучие" функции.

В английской версии звучит как volatile (волатильные), но англицизмы запрещены, поэтому пусть будут летучими. Функций этих немного, вот список:

Смысл в том, что функции эти пересчитываются ПРИ ЛЮБОМ ИЗМЕНЕНИИ, которые мы делаем на листе. То есть написали в ячейке =1+1, функции эти пересчитываются. И если их тысячи, то файл начнёт тормозить.

Что делать?

1. Вышеупомянутое отключение автоматического пересчёта формул (если формулы всё-таки нужны).

2. Если есть возможность, то формулы превращаем в значения. Копируем их, потом вставляем как значения.

8. Скрытые листы.

Вот такая простая причина :) Листы же можно скрывать. И вот кто-то давным-давно лист с огромным объёмом данных скрыл (авось пригодится), и вам он в наследство достался. Тут не должен работать принцип "нести тяжело, а бросить жалко". Если листы не нужны, без сожаления и страха их удаляем.

Что делать?

Проверить, нет ли скрытых листов. ПКМ по ярлычку любого листа - Показать (если активна, значит кому-то есть чего скрывать).

Далее всё просто. Если чего-то не нужно, отображаем - удаляем. Но убедитесь, что лист действительно бестолковый (нет ли в ваших формулах ссылок на данные с этого листа).

Бонус: сохраняем файл в формате двоичной книги (*.xlsb).

Очень действенный способ уменьшить размер файла в несколько раз и ускорить его работу. Когда-то давно на одном форуме кто-то написал примерно следующее: единственный минус формата xlsb - это то, что теперь формат xlsx вообще не нужен (там по-другому было написано немного, но вдруг мой пост будут читать дети). Но написано это было давно. И минусы всё же есть.

По поводу самого сохранения, формат так и называется - двоичная книга Excel:

Заключение.

Это далеко не все причины, но, думаю, основные и наиболее распространённые. И нужно иметь в виду, что Excel не вытягивает огромные таблицы. 500 тысяч строк, заполненных просто значениями, без формул, без форматирования уже будут трудно даваться старичку. Но для таких объёмов у нас есть Power Query/Power Pivot.

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

Кому лень читать - вот есть видео

Ступор, небольшое замешательство, осознание, что чего-то пошло не так. Самые смелые заканчивают ввод формулы, видят нормальное значение, радуются, копируют формулу, после чего радость заканчивается. Потом, скорее всего, поиски в интернете "как избавиться от ПОЛУЧИТЬ.ДАННЫЕ...". Про то, как избавиться, мы ещё поговорим в самом конце. А пока...

В чём сила этой функции, брат?

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

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

Давайте сейчас определим сложности, с которыми мы столкнёмся, и которые не позволят нам банальным копированием-вставкой или прямой ссылкой заполнить наш отчёт:

1. Клиенты продавали не все наименования. Но, в теории, всё это у них может быть.

2. Порядок клиентов в сводной таблице и в отчёте разный ("потому что" ©).

3. Отчёт сразу за год, а у нас пока данные только до ноября. Добавлять формулы потом отдельно на декабрь не очень хочется. Хочется в начале года прописать формулу, потом сводную обновлять и радоваться жизни.

4. Почему не СУММЕСЛИМН? Потому что структура отчёта не позволяет суммировать продажи по наименованиям внутри каждого клиента. Или городить какую-нибудь формулу массива (не пробовал, но может и получится).

5. Можно ещё немного пофантазировать и предположить, что могут появиться новые клиенты и новые наименования. И решение должно это учитывать (добавил клиента с наименованиями, скопировал формулу, гордый собой пошёл пить чай/кофе).

И вот тут как раз на сцену выходит она - ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GetPivotData).

Не смотря на всю свою несуразность и загадочность, начиная с названия и заканчивая синтаксисом, функция довольно простая:

Первый аргумент - поле из сводной, по которому мы производим вычисления. Далее - ЛЮБАЯ ячейка из сводной таблицы (обычно берут верхнюю левую). А потом идут пары: в каком поле что нужно найти.

Первоначально нужные элементы указываются в виде текста. Вот тут и начинается самое интересное. Ведь вместо текста можно указывать ссылки на ячейки (учитывая все закрепления, конечно же). То есть если возвращаться к нашему отчёту, формулу для наименований можно прописать следующим образом (показана формула для ячейки В3):

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма, руб";

'Исходник 2'!$G$1;"Наименование";$A3;"Клиент";$A$2;"Месяцы (Дата)";B$1)

Исходник 2 - лист, на котором находится сама сводная таблица.

В ячейке хотим видеть значение из поля "Сумма, руб" нашей сводной. G1 - ссылка на ячейку сводной. А дальше те самые пары. В поле "Наименование" ищем значение из ячейки А3 (1), в "Клиент" ищем А2 (2), в "Месяцы(даты)" ищем В1 (3). Порядок пар роли не играет.

ВАЖНО! Названия полей (наименование, клиент, месяцы(даты)), тоже можно сделать в виде ссылок на ячейки, если таковые есть. А вот название поля данных, в котором происходит расчёт (сумма, руб) обязательно должно быть указано текстом. В любом случае, названия должны совпадать с теми, которые указаны в сводной таблице.

Если сейчас скопировать формулу на все месяцы, то в декабре будет ошибка #ССЫЛКА, так как нет такого элемента в сводной таблице. Чтобы убрать это непотребство, просто добавляем функцию ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма, руб";

'Исходник 2'!$G$1;"Наименование";$A3;"Клиент";$A$2;"Месяцы (Дата)";B$1);"")

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

1. Если в нашей сводной что-то будет смещено, то формула не сломается, в отличие от прямой ссылки.

2. Когда будут данные за декабрь, мы их добавляем в нашу "умную" таблицу, обновляем сводную и готово.

3. Если появится новый клиент или наименование, то нужно будет добавить новые строки в отчёт и скопировать формулу.

Недостатки у такого подхода, конечно, тоже имеются:

1. Если фильтровать сводную или полностью перелопатить её структуру, то значения в формуле будут пересчитываться в соответствии с тем, что сейчас отображается в сводной. Таким образом, например, если поставим фильтр в сводной таблице на какие-то определённые наименования, а потом забудем фильтр очистить, то в отчёте суммы по этим наименованиям потеряем (ячейки будет пустыми, ошибок не будет, ведь мы приправили всё ЕСЛИОШИБКОЙ).

2. Если отчёт и сводная находятся в разных книгах, то формула будет работать только тогда, когда обе книги открыты одновременно (не относится к ситуации, когда обе открыли, а потом книгу со сводной закрыли).

Заключение.

На этом, пожалуй, всё. Повторюсь ещё раз, я описал свой опыт. С моей субъективной колокольни, именно такое использование данной функции наиболее полезное и практичное. То, что потом на основе таких вот отчётов можно строить обычные диаграммы - это уже производное от главного, а именно от того, что основное преимущество данной функции - более менее безопасный и гибкий способ переносить данные из сводной таблицы в обычные таблицы.

Как обычно, спасибо всем, кто потратил своё драгоценное время на чтение данной статьи. Надеюсь, было полезно. И да, если уже пользуетесь этой функцией, поделитесь в комментариях, какие задачи она вам помогает решать. Наверняка есть что-то такое, чего я о ней не знаю (никогда не было стыдно признаться в том, что я чего-то не знаю). Или если при использовании данной функции вы сталкивались с какими-то критичными трудностями. Да пребудет с нами сила ИКСэль.

P.S.

Алё, а отключить-то как?! На всякий случай напишу, как отключить автоматическое создание этой функции, когда ссылаемся на ячейки сводной (большинству она вот совсем не нужна). Всё просто:

1. Выбираем любую ячейку сводной.

2. Вкладка Анализ - группа Сводная таблица - раскрываем Параметры - снимаем галочку Создать GetPivotData.

3. Profit!!11

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