VBA.Excel

Файл с формулой можно скачать по следующей ссылке:

https://drive.google.com/file/d/1YLsHzifuVgath7ruzLlsxywOdTt...

Как можете заметить, в функции очень много ссылок на одну и ту же ячейку – H6. Естественно, вручную перетаскивать все ссылки H6 не вариант (их 97!). Поэтому вот как работаем с этой формулой:

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

2. Выбираем две ячейки, включая ячейку с формулой (показываю на примере самого файла по ссылке – в формуле с выводом сотых заменим ссылку на новое значение в ячейке C6):

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

3. Вызываем окно поиска и замены с помощью CTRL+H

4. В «Найти» вписываем заменяемый адрес – H6, в «Заменить на» - адрес новой нужной ячейки – например, C6

5. Нажимаем на «Заменить все». Замена происходит лишь в обеих выбранных ячейках (именно для этого мы и выбирали вторую ячейку, чтобы ограничить замену лишь на выбранный диапазон). Готово! Формула сразу ссылается на нужное значение:

Из особенностей формулы:

1. Формула абсолютно самостоятельна и не использует VBA или сторонние Add-Ins

2. Формула работает с числами до 999.999.999

3. Формула правильно склоняет слова

4. Вся формула в свою очередь состоит в основном из формул ЛЕВСИМВ, ПСТР, ЕСЛИ, ТЕКСТ, И, ВЫБОР

5. Количество символов в формуле с прописью целых и сотых: 6034

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

Если присмотреться, то можно увидеть этот самый неброский элемент для настройки примененного правила форматирования:

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

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

Настройка «Ко всем ячейкам, содержащим значения «Выручка»» привязывает условное форматирования абсолютно ко всем значениям выручки, в том числе и к общему итогу:

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

Теперь, даже если мы меняем размещение полей, Excel всегда знает, к какому именно диапазону следует применять условное форматирование. Вот, например всё та же сводная таблица, только теперь поле «Город» находится в разделе столбцов:

Вот такая особенность работы УФ со сводными таблицами. При этом, есть еще пара особенностей применения УФ к сводным таблицам, которые имеют множество полей как в разделе строк, так и в разделе столбцов. О них (а также о всём рассказанном выше, но в наглядной форме) я рассказал вот в этом видео, предлагаю его посмотреть:

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

Делается это очень просто. Выбираем одну из имеющихся сводных таблиц (1) и во вкладке «Анализ» (2), щелкнув по треугольнику в выпадающем списке "Параметры" убираем галочку напротив пункта «Создать GetPivotData», просто нажав на него (3):

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

Вот и всё! Настолько просто можно отключить автовставку функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. При этом, обрати внимание на то, что измененная нами настройка является настройкой на уровне программы Excel, то есть если мы откроем любой другой файл Excel, в нём уже точно также данная функция будет отключена. Если же мы при этом откроем этот же файл на другом компьютере, где настройка «Создать GetPivotData» еще не была отключена, то там будет всё также вставляться рассмотренная функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Но не переживай! Все уже прописанные формулы не будут изменяться. В конце концов ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ — это самая обычная функция, которая позволяет получать данные из сводных таблиц с помощью указания полей сводной таблицы вне зависимости от их физического расположения на рабочем листе.

Также предлагаю посмотреть это короткое видео, в котором всё описанное в этом посте наглядно показано:

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

Приятного просмотра! 😊

Что рассмотрено в видео:

• как создавать интерактивные дэшборды в Excel со сводными и не только диаграммами на основе сводных таблиц

• оптимальная структура рабочей книги для дэшборда

• подход в создании дэшбордов для их долгосрочного использования и развития (разработка отдельных деталей дэшборда на отдельных листах)

Приятного просмотра! 😊