Как закрепить условное форматирование в сводной таблице

Как закрепить условное форматирование в сводной таблице

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

то на первый взгляд всё сработает:

Правило будет выглядеть так

Однако, если вы обновите сводную таблицу, то форматирование исчезнет! А правило неожиданно станет таким:

В чём тут дело? Оказывается при создании правила УФ нельзя совместно выделять и обычные ячейки и ячейки сводной таблицы. Встаньте на любую ячейку сводной таблицы из области значений и начните создавать правило УФ:

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

Обратите внимание как выглядит правило УФ — добавляется иконка сводной таблицы.

Подобное УФ сохранит свою работоспособность даже, если вы поле Город перетащите из раздела СТРОКИ в раздел КОЛОННЫ сводной таблицы. Вот так:

Это всё, что я имел сказать про условное форматирование сводных таблиц 🙂

Условное форматирование из сводной таблицы Excel

В данном примере мы будем использовать данные из сводной таблицы в качестве критерия для условного форматирования значений Excel. Эта статья является продолжением для примера: Форматирование сводной таблицы Excel для планирования продаж.

Подготовка плана продаж для условного форматирования

Создадим лист под названием «Шаблон анализа» как указано ниже на рисунке:

Важно чтобы в диапазоне A2:A5 находились имена тех самых клиентов, а в диапазоне B1:F1 – наименование тех самых товаров. В диапазон ячеек B6:G6 вводим формулы: =СУММ(B2:B5). А диапазон G2:G5 заполняется формулами: =СУММ(B2:F2).

Примечание. Для быстрого заполнения указанных выше диапазонов ячеек формулами удобно использовать комбинацию клавиш CTRL+Enter. Сначала выделяем диапазон B12:F12 так чтобы активной осталась ячейка B12 (то есть с нее начинаем выделять диапазон). Нажимаем клавишу F2 вводим формулу =СУММ(B2:B11) и нажимаем комбинацию горячих клавиш CTRL+Enter. Благодаря этому выделенный диапазон сам автоматически заполняется формулами и подставляет нужные ссылки в параметрах функции СУММ. Аналогично выполняем эти действия для автоматического заполнения формулами диапазона G2:G11.

Таблица для планирования продаж будет рассчитана на рост +5% по отношению к предыдущему году. Поэтому ее значения будут увеличены на 5%. Для того чтобы комфортно корректировать план следует записать предполагаемый уровень роста в отельную ячейку и присвоить ей имя. Ссылаясь в формулах на имя этой ячейки, мы можем изменять параметры плана с 5% на любой другой уровень роста. Для этого в ячейку H1 введем текст «Уровень роста», а в ячейку H2 вводим 5%. После чего не убирая курсор с ячейки H2 выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя» и в поле «Имя:» введите значение «урРост» (или просто введите слово «урРост» в поле имен при активной ячейке H2).

Так как данный лист будет содержат дополнительные анализы, то желательно убрать сетку с листа. Для этого снимите галочку с опции: «ВИД»-«Показ»-«Сетка»:

Шаблон для аналитического планирования продаж – готов.

Загрузка данных для шаблона анализа

Теперь подготовим данные:

1. На новый лист скопируйте данные из таблицы ниже во фрейме:
2. На основе этих исходных данных создадим сводную таблицу: «ВСТАВКА»-«Сводная таблица»
3. Сразу после создания нового листа со сводной таблицей присваиваем новые имена и для листа, и для сводной таблицы. Чтобы переименовать лист щелкните правой кнопкой мышки по ярлычку нового листа и выберите из контекстного меню опцию «Переименовать». Потом введите новое имя «Сводный отчет» и нажмите клавишу Enter для подтверждения переименования листа.
4. Чтобы наша сводная таблица отображала нужные нам данные в соответственном порядке, упорядочиваем значения в полях:

Параметры полей значений в сводной таблице:

• в полю СТРОКИ – значения Клиент и Год;
• в полю КОЛОННЫ – значения Товар;
• в полю ЗНАЧЕНИЯ – Количество.

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

Теперь, когда данные подготовлены возвращаемся на шаблон анализа. Автоматически заполним табличную часть, сложной формулой которая ссылается на имя «Рост» и сводную таблицу на листе «Сводный отчет». Предварительно выделите диапазон табличной части B2:F11 так чтобы активной была ячейка B2. Потом нажмите клавишу F2 и введите формулу:

После ввода формулы нажмите комбинацию клавиш CTRL+Enter.

Данная формула использует функцию =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(). Название данной функции говорит само за себя. В первом аргументе указывается поле для исходных данных (КОЛИЧЕСВТО). Во втором аргументе следует указать любой диапазон сводной таблицы (‘Сводный отчет’!$A$3). Третий аргумент содержит в себе пару поле/элемент которая описывает данные запроса (например, «Год»;2014). В данном примере год товара и клиента должен быть тот-же 2014. Выбор клиента и товара будет постоянно изменяться – соответственно. Поэтому следует их параметризировать смешанными ссылками: $A2 – для клиента и B$1– для товара.

В данном шаблоне изначально было запроектировано, что бюджетирование будет учитывать рост продаж для следующего года +5%. Поэтому в формуле мы умножаем функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() на (1+урРост). Ведь рост — это имя ячейки, которая в данный момент содержит значение 5%. Вся эта формула помещается внутрь функции =ЕСЛИОШИБКА(). Благодаря ей все ошибочные результаты вычисления будут заменятся на значение 0. Например, если определенный клиент не покупал определенный товар в 2014 году, тогда формула возвращает значение ошибки #ЗНАЧ!. Но благодаря функции ЕСЛИОШИБКА мы просто получим 0.

Шаблон анализа с условным форматированием

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

1. Изменено значение продаж в соответствии с настройками (рост продаж +5% или больше).
2. Изменено значение продаж на меньше установленного роста 5%.

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

1. Выделите диапазон табличной части B2:F11 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. В появившемся окне «Создание правила форматирования» активируйте опцию «Использовать формулу для определения форматируемых ячеек».
3. В поле ввода вводим формулу: ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Количество»;'Сводный отчет'!$A$3;»Клиент»;$A5;»Товар»;E$1;»Год»;2014)*(1+урРост)’ >
4. Нажмите на кнопку «Формат», чтобы задать зеленый цвет для шрифта значений ячеек.

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

А в четвертом пункте указываем красный цвет шрифта формата ячеек. Для предварительной проверки откройте «Диспетчере правил». Для этого выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами»

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

В ячейке H2 можем изменять значения (например, вместо 5% вводим 7%) и после нажатия клавиши Enter сразу получаем новый отчет для анализа.

Все описанные таблицы и формулы можно рассмотреть более детально в готовом примере скачав ниже по ссылке файл Excel.

Условное форматирование сводных таблиц ⁠ ⁠

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

В качестве примера, выбрав значения следующей сводной таблицы применяем к ней классическое цветовое УФ (Главная -> Условное Форматирование -> Цветовые шкалы -> Цветовая шкала «Зеленый-Желтый-Красный»):

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

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

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

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

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

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

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

635 постов 14.5K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

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

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

Мгновенное заполнение в Excel — магия в чистом виде⁠ ⁠

Друзья, всем привет. Сегодня хочу рассказать вам про мгновенное заполнение в Excel.

Ссылка на файл, чтобы можно было потренироваться — https://disk.yandex.ru/i/HyW0N215F6CuUg

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

Так вот это и есть мгновенное заполнение во всей своей красе. Да, иногда это раздражает, потому что тебе это не нужно. Но в большинстве случае польза мгновенного заполнения огромна.

Извлечение данных

Предположим, у нас есть вот такой столбец с текстом:

Нам нужно извлечь отдельно номер договора и дату. Это можно сделать с помощью инструмента «Текст по столбцам». Правда, потом придётся от символа «№» ещё избавляться. А вот мгновенное заполнение справится с этим намного быстрее. Просто вводим справа от текста в первую ячейку номер договора (1), нажимаем Enter. Далее возможны два варианта.

Вариант 1. Вручную вводим в ячейку первую цифру второго договора (2). Excel предлагает свои варианты, жмём Enter — PROFIT!

Вариант 2. После того, как перешли ко второй ячейке, сразу нажимаем сочетание Ctrl + E (Е английская, конечно). Именно это сочетание отвечает за запуск мгновенного заполнения. Аналогично с датами. Вводим в ячейку С2 дату первого договора — Enter — Ctrl + E — наслаждаемся результатом.

ОЧЕНЬ ВАЖНАЯ ЧАСТЬ СТАТЬИ.

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

Ух ты! И так будет работать всегда?! Строго говоря — нет. Иногда, Excel не может с одной ячейки распознать логику. В этом случае нужно вручную заполнить не одну, а две, три, четыре (если случай совсем запущенный) ячейки. И только после этого нажимать Ctrl + E. Чем больше ячеек заполняешь, тем выше вероятность того, что твоя логика будет верно распознана могучим интеллектом Excel. Порой мгновенное заполнение не справляется с поставленной задачей:

Даты в первом столбце указаны в формате ГГГГ-ММ-ДД. При попытке привести их в формат ДД-ММ-ГГГГ получается вот такая «красота». Поэтому не поленитесь после того, как все ячейки будут заполнены, пробежаться по ним, а тот ли в них результат, который ты ожидал увидеть.

Образцы вводите в соседнем столбце от источника (можно справа или слева). Не «убегайте» далеко от данных, результат может быть непредсказуемым или вообще ничего не будет.

Ещё одно важное дополнение: мгновенное заполнение работает в версиях Excel 2013 и выше.

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

Извлечение только чисел из столбца

Если нам из «красивого» столбца, в котором есть значения вроде «123руб», «55 рублей» и так далее, нужно извлечь только цифры, то вы уже знаете, что нам поможет:

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

Работа с текстом

В столбце указаны Имя и Фамилия. Нам нужно получить результат в виде «Имя Ф.» В первой ячейке вводим образец — Enter — Ctrl + E:

Кстати, если попробовать получить Фамилия И., то будьте внимательны. Если прописать два примера, потом начать вводить третий, то появляется довольно забавный список:

Но если не начинать вводить в третью ячейку текст, а сразу нажать на Ctrl + E, то всё будет нормально. Раз на раз не приходится. Временами мгновенное заполнение ведёт себя очень странно.

Извлечение части сплошного текста

Необходимо разбить слипшийся текст на части. Вводим в первых двух ячейках образец — Ctrl + E:

С номером поступаем аналогично.

Сбор текста

В отдельных столбцах есть различная информация, которую необходимо собрать в одно предложение. Обратите внимание, что порядок столбцов для мгновенного заполнения роли не играет. Прописываем предложение в первой ячейке — Enter — Ctrl + E:

На этом статью я хотел бы завершить. Уверен, я перечислил далеко не все чудесные возможности мгновенного заполнения. Буду вам благодарен, если в комментариях поделитесь своими способами применения этой чудесной штуки.

В качестве небольшой рекламы позвольте оставить здесь ссылку на мастер-класс, который я буду проводить 9 марта. Кто хочет узнать ещё несколько полезных приёмов при работе в Excel (там почти не будет того, о чём я писал здесь), а ещё хочет услышать чуть больше про то, где я работаю, записывайтесь — Полезные приемы при работе в Excel. Часть 2 (specialist.ru)

На этом всё. Как обычно, спасибо огромное всем, кто потратил своё драгоценное время и осилил данное полотно. Надеюсь, было полезно. Видео по данной статье обязательно появится на моём канале — (36) Андрей Митрохин — YouTube

Таблица подсчета розеток/выключателей/рамок. ⁠ ⁠

Когда я работал в магазине электротоваров регулярно приходилось считать ЭУИ и рамки к ним по зарисовкам заказчиков или их работников, тогда я это делал на бумаге и неплохо набил на этом руку. Но у некоторых продавцов консультантов это выходит не слишком быстро и качественно. Для автоматизации процесса я решил создать таблицу в google, а затем перенес ее в Excel (последний мне нравится больше). Таблицей я намерен поделиться ссылки будут ниже, а пока краткое описание:

Это страница «Сводка» первоначально ее надо заполнить под себя и сохранить как шаблон:
— Наименования всех типов ЭУИ какие у вас могут быть (если не достаточно того, что ввел я)
— Цвета механизмов (или например серия + цвет, как удобнее будет)
— Цвета рамок (аналогично механизмам)

Потом переходим на страницу «Ввод данных»
При добавлении новой строки указываете комнату
цвет механизма, цвет рамки и наполнение постов выбираете из выпадающего списка (подтянутся варианты со страницы «Сводка»), когда вы выбираете механизм для поста — ячейка окрашивается, считая количество постов в рамке

После заполнения страницы «Ввод данных», возвращаемся на «Сводка»
При выборе нужного цвета в крайней правой таблице («Текущий цвет») в списке ЭУИ и рамок останется только то количество, которое соответствует выбранным цветам.
Кроме того, общее количество механизмов и постов в рамках и количество установочных коробок

ЗЫ Отдельное спасибо @XaXa3Pa3a

Полезные трюки при работе в Excel⁠ ⁠

Всем привет. Это моя первая статья на Пикабу, поэтому позвольте сначала представиться. Я являюсь преподавателем Microsoft Excel. Теперь, когда с формальностями покончено, можно перейти к основному.

Сомнения перед написанием

Я довольно часто читаю разный тематический материал на Пикабу, и меня восхищают большинство авторов и статей. Статьи восхищают, в первую очередь, своей интересностью (есть такое слово вообще?) и полезностью. Именно поэтому у меня были большие сомнения, а стоит ли вообще лезть со своими очередными «простыми, но полезными штуками при работе в Excel». Да и кому вообще ты со своим Excel нужен?! Тем более, что беглый поиск по сайту не выдал ни одной подобной статьи. И та часть меня, которая отвечает за неуверенность, сразу подметила, что раз нет, значит, оно никому не нужно. А может, просто плохо искал. И да, я отдаю себе отчёт в том, что подобного материала довольно много на просторах интернета. И всё-таки, принцип «лучше сделать и жалеть, чем не сделать вовсе» возобладал.

Почему я посчитал, что это будет полезно

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

Небольшое пояснение

Путь до той или иной команды обычно описывается следующим образом: название вкладки — потом группа команд — сама команда:

Если у вас ноутбук, то функциональные клавиши могут работать только при одновременном нажатии на кнопку Fn+F1-12 (есть такие ноутбуки, в которых и этот способ не работает, тут надо уже по модели ноута смотреть).

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

F4 — повтор последнего выполненного пользователем действия (если нажимать её не тогда, когда курсор находится в строке формул)

Например, вам нужно для нескольких несмежных столбцов установить определённую ширину. Вместо того, чтобы каждый раз выбирать столбец, потом переходить на вкладку Главная — Ячейки — Формат — Ширина столбца. Можно один раз проделать эту операцию, потом просто выделить следующий столбец и нажать F4. И такой фокус можно проделывать со многими операциями, будь то закраска ячеек, строк, столбцов, части графика на диаграмме или банальная вставка столбцов (да, столбец можно вставлять сочетанием Ctrl + «+», но ведь это две кнопки, а F4 — одна).

Представления

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

И вот вы каждый раз раскрываете фильтр, устанавливаете нужные критерии, просматриваете данные, потом раскрываете фильтр, следующий критерий, потом фильтр. Думаю, суть вы уловили. «Но всё меняется, когда приходят они — представления!» © Установив нужные критерии, переходим на вкладку Вид — Режимы просмотра книги — нажимаем Представления:

Далее всё интуитивно (куда же без интуиции в этой прекрасной программе) понятно. Жмёшь «Добавить», обзываешь представление так, как тебе угодно — Ок. Здесь же, в окне добавления представления, мы можем узнать, а что, собственно, Excel сохраняет. А сохраняет он параметры печати, результаты фильтрации, скрытые строки и столбцы. Создав под каждый набор фильтров, строк и столбцов представление, потом лёгким и непринуждённым нажатием на эту команду ты будешь менять свою таблицу в мгновение ока. Это не совсем удобно? Что же, согласен. Давайте сделаем ещё удобнее и добавим представления на панель быстрого доступа. Для этого раскроем настройку панели быстрого доступа — Другие команды:

В открывшемся окне в поле «Выбрать команды из:» выбираем «Все команды». Потом находим «Представления» — Добавить:

Кстати, так можно добавить на панель быстрого абсолютно любую команду.

Теперь у нас появился выпадающий список со всеми нашими сохранёнными представлениями. Через это же окно можно и новые представления создавать. Просто пишешь в нём название, нажимаешь Enter — готово.

ПРЕДУПРЕЖДЕНИЕ!

Представления не работают в книгах, в которых есть «умные» таблицы (таблицы, которые мы создаём через вкладку Главная — Стили — Форматировать как таблицу).

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

Два окна одной книги.

Прежде, чем кидать в меня различные предметы с криками «мало того, что про какой-то Excel пишет, так сейчас ещё будет рассказывать, как в двух окнах работать, смерд?!» позвольте пояснить. Речь пойдёт о том, как работать в двух окнах с ОДНОЙ книгой. Давайте смоделируем ситуацию. Есть у тебя два монитора (если ещё нет, обязательно заводи второй, пускай небольшой, но чтобы был), один файл Excel с несколькими листами внутри. Тебе нужно из одной таблицы перенести данные в другую (сравнить их, связать формулами и так далее). Что ты делаешь? Правильно, бесконечно долго и уныло переключаешься между листами. Второй монитор тем временем грустно за этим наблюдает. Но можно сделать этот процесс более удобным и быстрым. Прошу любить и жаловать, вкладка Вид — Окно — Новое окно:

Нажав на эту команду, мы получим ту же самую книгу, но в другом рабочем окне. Название файла будет немного изменено на «Мой файл:1» и «Мой файл:2». А дальше уже дело за тобой. Располагай окна так, как тебе удобно (на одном мониторе, на разных), копируй данные, создавай связи, формулы — в общем, работай. Но делать ты это уже будешь быстрее и удобнее. Все изменения, которые мы вносим в любое из клонированных окон, появляются сразу во всех связанных окнах. Главное, не забыть нажать «Сохранить» хоть в каком-нибудь окне.

Специальная вставка (пропускать пустые ячейки)

Вообще, про специальную вставку в Excel можно написать отдельную статью, наверное. Инструмент во многих случаях просто незаменимый. Но в рамках данной статьи я расскажу только про одну возможность. Представим, что есть две таблицы:

Нужно перенести данные из крайнего правого столбца второй таблицы (столбец Р) в крайний столбец первой таблицы (столбец F) таким образом, чтобы существующие номера остались. Обычным копированием-вставкой сделать это не получится, так как в столбце Р есть пустые ячейки, которые заменят собой существующие номера в столбце F. И тут на сцену выходит специальная вставка. Выделяем диапазон из столбца Р, копируем. Далее выбираем ячейку, начиная с которой нужно вставить данные (в нашем случае это F2), и либо щёлкаем правую кнопку мыши — в контекстном меню ищем «Специальная вставка», либо нажимаем сочетание клавиш Ctrl+Alt+V. Попадаем в такое окно:

Ставим галочку рядом с «пропускать пустые ячейки» — Ок. Профит!

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

P.S. Если статья покажется интересной и полезной, то на примете есть ещё несколько приёмов, про которые могу рассказать.

Друзья, создал на Ютубе свой канал. Пока только видео с первой статьёй. В ближайшие дни опубликую вторую часть. Полезные трюки и приёмы при работе в Microsoft Excel — YouTube

Если б мишки были пчёлами… условия в Excel⁠ ⁠

Решил попробовать формат коротких постов по Excel

Дети, сегодня я расскажу вам о такой удобной функции в Excel как ЕСЛИ.

В общем виде выглядит так:

ЕСЛИ (условие; результат если условие верное; результат если условие не верное)

Я буду писать формулу, а на картинке будет результат этой формулы в 5 столбце

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

=ЕСЛИ(И(A2=»овощи»;D2>50);»дорогой овощ»;»либо не дорогой, либо не овощ»)

Не забываем, что можно комбинировать несколько ЕСЛИ Главное не запутаться в скобках и точках с запятыми.

=ЕСЛИ(A2=»фрукты»;ЕСЛИ(D2>100;»дорогой фрукт»;»не дорогой»);»не фрукт»)

Как видим результат получается довольно гибкий.
Также напомню, что есть операторы сравнения <,>,<=,>=,=,<>.

А на этом сегодня все, пойду дальше писать про макросы…

Ответ на пост «Помощь в формировании Таблицы на основе 2 таблиц»⁠ ⁠

Если по-простому, формулами, то так:

Таблицу 3 переделываем в другой вид:

Это будет состав детали, даты будем добавлять вправо.

(Таблица соответствия количества материала количеству детали)

Таблица 2 тогда будет выглядеть так:

(тупо суммируем кол-во материалов на дату)

1. Важно чтобы во всех 3-х таблицах даты были синхронизированы, чтобы можно было протягивать формулы не опасаясь что-то поломать.

2. Важно следить чтобы в составе (таблица 3) не было дубликатов пар — [деталь — материал] (желтые столбцы), иначе объем задваиваться будет.

Сумма итогов, чтобы её не переписывать каждый раз (в ячейке H2) — может выглядеть так:

Ответ на пост «Excel, делим покупки на несколько человек»⁠ ⁠

И так господа, перед Вами методичка по распиливанию затрат после попоек.

Разработана она мною, но на копирайт не претендую.

Ситуация проста: Пятница, Вы собрались пошуметь. Заказаны «стартовые» места, собрана компания друзей, сняты ограничения с карточек, побрились, подмылись, вызвали такси и поехали!

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

Помните, между друзьями не должно быть обид или споров по денежным вопросам. Если у вас в компании есть друг, не желающий сопровождать общий счёт, объясните ему, что он сам платит за себя и главное платит СРАЗУ! Принесли счёт в ресторане, он сам запоминает и считает сумму напитого и наеденного и СРАЗУ кидает на стол деньги, а ещё фиксит это, например в общем чате. Кстати, общий чат — это тема. История с карточками — это хорошо, но налом платить в некоторых местах безопасней. Кидайте сразу в общий чат, кто, сколько и главное, за что заплатил. Поверьте, с утра вам будет не до воспоминаний, да и не всё вспомнить. Не забывайте о конспирации! Нельзя писать «3000 Диме на приват». Продумайте заранее все фразы и договоритесь об обозначениях.

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

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

1. Кидаем в чат позывной. Кто, за что, вчера платил.

2. Открываем Excell и создаём следующую таблицу:

И так: Перед вами таблица со следующими параметрами:

Столбец «Событие» — кратко фиксим события оплаты;

Столбцы «Участники» — все, кто присутствовал на пьянке;

Столбец «Сумма» — Общая сумма к разделу;

Столбец «N» — кол-во человек участвующих в разделе;

Столбец «Проверка» — собственно сумма всех сумм в столбцах B-E = 0.

Если пьянка длилась несколько дней (например Нашествие), то слева ещё вставляется таблица «Дата».

Допустим начали сначала Вася, Петя, Игорь. Зашли в Кафе, пожрали, да выпили на 9500 рубликов. Платил Петя.

Тогда для Пети пишется формула:

Для всех, кто учувствовал пишется формула:

В результате получаем:

Все кто с минусом — те в долгах;

Кто с плюсом -тот кредитор.

Формулы с $ помогают просто копипастить ячейки, и заполняя только столбцы Сумма и N, долбить долг дальше по событиям:

Как мы видим, в пабе к нам пришёл ещё Ваня.

Дальше из караоке идём в клуб. Ваня цепляет тёлку, Вася уезжает домой.

Для этого вводим ещё один столбец а с Васей прощаемся. В клубе мы оставляем 15 к.

Ваня с «Ваней — 2» уезжают, а Петя и Игорь идут:

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

Условное форматирование сводной таблицы в Excel

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

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

Excel выделяет город с наибольшими и наименьшими продажами, причем правило форматирования сохраняется при смене фильтра с помощью среза.

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

Пошаговое применение условного форматирования к сводной таблице

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

Задача: В приведенной ниже сводной таблице необходимо применить «3-цветную шкалу» к значениям «Месяц».

• Прежде всего, выберите любую из ячеек, которые имеют значение месяца.

• Затем перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».

• Вы увидите диалоговое окно для применения условного форматирования к сводной таблице.

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

1. К выделенным ячейкам: используйте этот параметр, чтобы применить условное форматирование только к выбранной ячейке.
2. Ко всем ячейкам, содержащим значения «Сумма»: все ячейки, которые имеют значения суммы.
3. Ко всем ячейкам, содержащим «Сумма» для «Месяц»: все ячейки, значения которых имеют сумму, но только за месяцы.

• Выберите «Ко всем ячейкам, содержащим значения «Сумма» для «Месяц».
• В «Измените описание правила» выберите 3-х цветную шкалу.
• Измените тип минимума, среднего значения и максимума на проценты. После этого выберите цвет для всех трех.

• В конце нажмите ОК.

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

Примеры применения условного форматирования в сводной таблице.

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

Вот список примеров, которые вы можете сразу же применять в своей работе.

1. Динамическое условное форматирование с фильтрами

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

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

2. Применение условного форматирования к отдельной строке в сводной таблице

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

• Выберите любую из ячеек.

• Перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
• Применить правило: третий вариант.
• Из типов правила выберите «Форматировать только первые или последние значения».
• В описании правила введите 1 в поле ввода и в раскрывающемся меню выберите «Каждой группы столбцов».

• Примените форматирование, которое вы хотите.
• Нажмите ОК.

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

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

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

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

Для реализации нужно сделать следующие шаги:

• Выберите ячейку и перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
• Затем выберите третий вариант из «Применить правило к» и выберите «Форматировать все ячейки на основании их значений» из типа правила.
• Теперь в описании правила выберите «Гистограмма», в раскрывающемся меню «Тип» выберите число для максимального и минимального значений.
• В поле минимального значения введите 0, а в поле максимального значения введите ссылку на ячейку D4.

• В конце укажите цвета заливки и нажмите кнопку «ОК».

4. Применение условного форматирования к промежуточным итогам сводной таблицы.

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

Действуем по шагам:

• Выберите ячейку и перейдите на вкладку «Главная» → «Стили» → «Условное форматирование» → «Создать правило».
• Затем выберите третий вариант из «Применить правило к» и выберите «Форматировать все ячейки на основании их значений» из типа правила.
• В описании правила выберите «Наборы значков» и стиль значков.
• Теперь в правиле отображения значков используйте процент для применения условного форматирования к промежуточным итогам.
• В конце нажмите ОК.

Когда вы применяете эти наборы значков для промежуточных итогов, они будут рассматривать самое высокое значение как 100%. Ячейки со значениями выше 67% получат зеленый значок, ячейки со значениями от 33% до 67% получат желтый значок, а другие ячейки ниже — красный значок.

Вы можете изменить значки и процентные значения в соответствии с вашими потребностями.

5. Применение условное форматирование к пустым ячейкам в сводной таблице

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

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