Сообщество - MS, Libreoffice & Google docs
MS, Libreoffice & Google docs
119 постов 7 117 подписчиков
39

Калькулятор неопределенности в Эксель

Доброго дня всем, пишу первый раз, строго не судите.

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

В книге 3 листа:

1) сам калькулятор;

2) список измерительных приборов;

3) список определяемых параметров.

Калькулятор неопределенности в Эксель Excel, Калькулятор

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

В данном файле макросы не использовались. Скачать с яндекс диска https://yadi.sk/i/eTvuGZPUUAJEoQ

9

Как в Excel сделать соответствие ячейки значению

UPD: Вопрос закрыт #comment_161278635


Есть два столбика, А И Б, в которых указана высота и число. Как сделать так что если при получении расчетов получалось число высоты то в ячейке выбивало значение число которое соответствует этой высоте. То есть если G3=A3 то выбивало бы значение В3. То есть то которое соответствует этой высоте.

665

Создание красивого отчёта

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

Создание красивого отчёта Excel, Отчет, Таблица, Полезное, Лайфхак, Длиннопост

Вы создали небольшую сводную таблицу (Вставка – Сводная таблица):

Создание красивого отчёта Excel, Отчет, Таблица, Полезное, Лайфхак, Длиннопост

Но вашему начальнику не нравится внешний вид отчёта, и он хочет видеть что-то похожее на это:

Создание красивого отчёта Excel, Отчет, Таблица, Полезное, Лайфхак, Длиннопост

То есть мы имеем несколько ощутимых трудностей:

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

2. Из всей сводной для отчёта вам нужны не все данные, а только конкретные модели Ford по Питеру - придётся руками фильтровать.

3. Стандартные итоги в сводной нам не подходят, т.к. нужны суммы по выручке в зелёных ячейках, но среднее по месяцу в итогах - сводная так не умеет.

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

5. Нужно построить по результатам хитрую диаграмму (обычные сводные диаграммы имеют много ограничений).


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


1 способ. Прямая ссылка на ячейку в сводной


Это, что называется, решение проблемы «в лоб». Сделаем на отдельном листе заготовку отчёта:

Создание красивого отчёта Excel, Отчет, Таблица, Полезное, Лайфхак, Длиннопост

Теперь в ячейку D7 можно вручную прописать ссылку:

=Лист1!B8


Где Лист1 – имя листа со сводной таблицей, а B8 – нужная нам ячейка в сводной с данными по продажам Ford Fiesta за январь.


При внешней простоте и очевидности у этого способа есть две проблемы:

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

2. Завтра, после обновления, структура сводной таблицы может измениться - например, Fiesta может оказаться уже не третьей, а седьмой строкой, Focus переехать во вторую и т.д. И тогда все ссылки придётся переделывать.


2 способ. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ


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


Чтобы её использовать, убедитесь, что при выделении любой ячейки сводной таблицы на вкладке Анализ (Analysis) или Параметры (Options) в выпадающем списке Параметры (Options) включена галочка Создать GetPivotData:

Создание красивого отчёта Excel, Отчет, Таблица, Полезное, Лайфхак, Длиннопост

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


=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

("Выручка";'Сводная для отчета'!$A$4;

"Наименование";$C7;"Дата";1)

Создание красивого отчёта Excel, Отчет, Таблица, Полезное, Лайфхак, Длиннопост

Давайте разберём её подробно:

1. Первый её аргумент ("Выручка") – это имя извлекаемого поля.

2. Второй (Лист1!$A$4) - это адрес первой ячейки сводной таблицы, откуда мы берём данные. Этот параметр нужен, т.к. на листе может быть несколько сводных и Excel должен понимать, из какой именно нужно вытащить число.

3. Все остальные аргументы начиная с третьего – это попарно название поля и его значение, т.е. в нашем случае это имя модели (Наименование="Fiesta") и временной период (Дата=1). Поскольку в сводной была применена группировка дат по месяцам, то в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ мы получили не имя месяца, а его номер. Если бы в исходной базе данных был столбец не с датой, а с названием месяца, то группировка была бы не нужна и вместо единички был бы просто "январь".


У этой функции есть несколько серьёзных преимуществ перед обычной ссылкой на B8, которая приводила бы, на первый взгляд, к тому же результату. Главный плюс в том, что если завтра после обновления в сводной таблице изменится количество строк/столбцов или Ford Fiesta станет не третьей, а пятой строкой, то нам об этом волноваться уже не придётся – функция корректно извлечёт нужное нам значение. Достаточно только обновить сводную правой кнопкой мыши – и наша красивая форма отчёта «для шефа» пересчитается автоматически.


Далее, замените в формуле "Fiesta" на $С7 (т.е. на ячейку с названием модели), а единичку на D$5 (т.е. ячейку с номером месяца) и допишите в конце формулы деление на 1000, т.к. нам нужно отобразить данные в тысячах. Затем нажмите на Enter и протяните формулу на оставшиеся зелёные ячейки.


=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

("Выручка";'Сводная для отчета'!$A$4;"Наименование";

$C7;"Дата";D$5)/1000

Создание красивого отчёта Excel, Отчет, Таблица, Полезное, Лайфхак, Длиннопост

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


Теперь с данными в диапазоне D7:F10 можно работать как с обычными формулами, а не как со сводной таблицей с её жёсткими ограничениями. Дальше можно спокойно считать любые итоги, динамику, прогнозы, строить любую диаграмму и т.д. Как украсить документ можно посмотреть здесь Минидиаграммы в ячейках


Формула для прогноза: =ПРЕДСКАЗ($G$5;D7:F7;$D$5:$F$5)

=FORECAST($G$5;D7:F7;$D$5:$F$5)


Формула для среднего значения: =СРЗНАЧ(D7:D10)

AVERAGE(D7:D10)


Формула для динамики: =D13/D14-1


Ссылка на файл в комментариях. Желаю всем без проблем сдать отчёты)) Кто знает способы лучше, можете поделиться опытом в комментариях.

Показать полностью 5
323

Диаграмма «план-факт»

Диаграмма «план-факт»


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


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

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

Выделяем таблицу без заголовка и на вкладке «Вставка» - «Диаграммы» выбираем «График с маркерами»:

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

Дважды нажав левой клавишей мыши на диаграмму, в появившемся разделе «Конструктор» можно выбрать различные стили диаграммы.

Так как при просмотре диаграммы визуально идёт сравнение данных, добавим для наглядности «Полосы повышения и понижения» во вкладке «Добавить элемент диаграммы»

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

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

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

Второй способ, это применение диаграммы с областями.


Для этого к имеющейся таблице добавим столбец «Разность», в который вписав формулу =C4-B4 получим разницу между фактом и планом.

Далее, удерживая Ctrl выделим содержимое столбцов A, B и D, на вкладке «Вставка» выбираем диаграмму «С областями и накоплением»:

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

Теперь выделяем столбцы «План» и «Факт», копируем их и вставляем в диаграмму, в результате получаем своеобразные горы:

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

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

Далее также нажатием правой клавиши мыши по диаграмме выбираем «Изменение типа диаграммы», где в открывшимся окошке для «плана и факта» выбираем «График с маркерами». Нажимаем ОК.

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

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

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост

Теперь можно навести красоту окрасив её в подходящие цвета, удалить в легенде ненужные пояснения, а также задать название нажав на заголовок и в строке состояния ввести формулу =Лист2!$A$1, в результате не придётся заново писать название диаграммы.

Диаграмма «план-факт» Excel, Диаграмма, Таблица, Полезное, Длиннопост
Показать полностью 7
10

Отсутствует работа формул и функций в Excel

Прошу помощи сообщества Excel.

Суть вопроса.

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

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

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

Заранее всем спб за помощь.

ПЫСЫ : образец проблемного файла можно скачать на  https://dropmefiles.com/IAFQe

462

Скрытие и отображение ненужных строк и столбцов

Вот и наступил декабрь, у кого-то началась подготовка к празднованию Нового года, а кто-то, сидя в офисе, готовится к сдаче годового отчёта.


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


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

Скрытие и отображение ненужных строк и столбцов Excel, Таблица, Лайфхак, Полезное, На заметку, Гифка

Пример принципа работы группировки таблицы в гифке:

Скрытие и отображение ненужных строк и столбцов Excel, Таблица, Лайфхак, Полезное, На заметку, Гифка

Чтобы создать группировку таблицы выделяем нужное количество строк или столбцов, а затем выбираем на вкладке Данные – Группировать (Data – Group), либо нажатием сочетания клавиш Alt+Shift+стрелка вправо, а для разгруппировки Alt+Shift+стрелка влево, соответственно. В результате они будут сгруппированы.

Скрытие и отображение ненужных строк и столбцов Excel, Таблица, Лайфхак, Полезное, На заметку, Гифка

В итоге у нас получится вот такая таблица

Скрытие и отображение ненужных строк и столбцов Excel, Таблица, Лайфхак, Полезное, На заметку, Гифка

Нажатием на «+/-» или цифры можно сворачивать и разворачивать ячейки и столбцы. Группы можно делать вложенными одна в другую (до 8 уровней вложенности).

Скрытие и отображение ненужных строк и столбцов Excel, Таблица, Лайфхак, Полезное, На заметку, Гифка

Также, если в таблице имеются итоговые строки или столбцы с функцией суммирования соседних ячеек, то есть шанс (не 100%-ный), что Excel сам создаст все нужные группировки в таблице одним движением – с помощью команды Данные – Группировать – Создать структуру (Data – Group – Create Outline). Однако, данная функция работает весьма непредсказуемо на сложных таблицах и порой выдаёт полный бред, но проверить можно.

Показать полностью 1
73

Лайфхак для экселя

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

597

Использование срезов для поиска и фильтрации

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

Использование срезов для поиска и фильтрации Excel, Таблица, Лайфхак, Полезное, Длиннопост, Гифка

Для этого, уже имеющуюся таблицу с данными, необходимо отформатировать как таблицу «Главная» - «Форматировать как таблицу» (Home – Format as Table), где выбираем любой дизайн. В моей таблице 3000 строк, ссылку на файл оставлю в комментариях.


Теперь, поставив курсор на ячейку в шапке, переходим на появившуюся динамическую вкладку Работа с таблицами: Конструктор (Table Tools: Design) и нажимаем кнопку Вставить срез (Insert Slicer). В открывшемся окне отмечаем флажками названия столбцов, по которым будем фильтровать.

Использование срезов для поиска и фильтрации Excel, Таблица, Лайфхак, Полезное, Длиннопост, Гифка

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

Если срез содержит очень много элементов (кнопок), то их можно расположить в несколько столбцов, как на срезах «Месяц» и «Бригадир». Для этого выделите срез и увеличьте для него количество столбцов на вкладке Инструменты для среза: Параметры (Slicer Tools: Options). Здесь же можно выбрать Стили срезов.

Использование срезов для поиска и фильтрации Excel, Таблица, Лайфхак, Полезное, Длиннопост, Гифка

Дополнительные параметры выделенного среза можно настроить с помощью кнопки Настройка среза (Slicer Settings) на той же вкладке:

Использование срезов для поиска и фильтрации Excel, Таблица, Лайфхак, Полезное, Длиннопост, Гифка

Удерживая клавиши Ctrl или Shift можно выделять сразу несколько элементов среза. Для сброса фильтра нажимаем кнопку Удалить фильтр (Clear Filter) в правом верхнем углу среза.


Также, чтобы скрыть срезы при печати документа нажимаем правой клавишей мыши по срезу и в разделе Размер и свойства - «Свойства» снимаем галочку с «Выводить объект на печать».

Использование срезов для поиска и фильтрации Excel, Таблица, Лайфхак, Полезное, Длиннопост, Гифка

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

Использование срезов для поиска и фильтрации Excel, Таблица, Лайфхак, Полезное, Длиннопост, Гифка

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

Показать полностью 5
369

Поиск данных в нескольких таблицах

Допустим, что у нас имеется четыре одинаковых по конструкции таблицы с данными по заказам товаров в трех странах:

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

Рассмотрим значение используемых формул по отдельности.

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

=ПОИСКПОЗ(W3;Q4:Q8;0)

=MATCH(W3;Q4:Q8;0)


=ПОИСКПОЗ(W4;R3:T3;0)

=MATCH(W4;R3:T3;0);W5)

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

Дальше используем функцию ИНДЕКС, чтобы извлечь данные из набора нескольких таблиц

=ИНДЕКС((C4:E8;H4:J8;M4:O8;R4:T8);X3;X4;W5)

=INDEX((C4:E8;H4:J8;M4:O8;R4:T8);X3;X4;W5)

В результате в ячейке X4 получаем данные из таблиц:

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

Можно же обойтись без дополнительных формул и сразу ввести следующую формулу в удобную для вас ячейку:


=ИНДЕКС((C4:E8;H4:J8;M4:O8;R4:T8);

ПОИСКПОЗ(W3;Q4:Q8;0);

ПОИСКПОЗ(W4;R3:T3;0);W5)


=INDEX((C4:E8;H4:J8;M4:O8;R4:T8);

MATCH(W3;Q4:Q8;0);

MATCH(W4;R3:T3;0);W5)


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

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

Сперва на вкладке «Формулы» - «Диспетчер имен» создадим именованные диапазоны, которые на них указывают:

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

Далее используем формулу для поиска номера строки товара:

=ПОИСКПОЗ(W4;ИНДЕКС(ДВССЫЛ(W3);0;1);0)

=MATCH(W4;INDEX(INDIRECT(W3);0;1);0)

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

Для тех, кто предпочитает знать как всё устроено, разберём её подробно))


Во-первых, функция ДВССЫЛ(W3) в данном случае представляет собой ссылку на именованный диапазон 4-го квартала. Прямую ссылку на ячейку с именем W3 использовать нельзя, т.к. Excel будет воспринимать ее как текст. Чтобы превратить текст «Квартал4» в живую ссылку на именованный диапазон «Квартал4», и нужна функция ДВССЫЛ (INDIRECT).


Во-вторых, фрагмент: ИНДЕКС(ДВССЫЛ(W3);0;1)

… представляет собой ссылку на первый столбец именованного диапазона «Квартал4», т.е. на Q3:Q10.


Как это получилось?

Классический вариант использования функции ИНДЕКС на одной двумерной таблице, напомним, предполагает три аргумента: =ИНДЕКС(диапазон; номер_строки; номер_столбца)

В этом случае мы получим содержимое ячейки в указанном диапазоне с пересечения строки и столбца с заданными номерами.

Хитрость в том, что если номер строки равен 0, то ИНДЕКС выдает уже не содержимое ячейки, а ссылку на весь столбец с указанным номером, т.е. на первый столбец именованного диапазона заданного ДВССЫЛ(W3), т.е. на ячейки Q3:Q10.

Ну а затем функция ПОИСКПОЗ (MATCH) ищет в этом диапазоне требуемый товар (Пиво) и возвращает его позицию (4 строка, т.к. пустая Q3 тоже считается).


Аналогично можно найти номер столбца с нужной страной:

=ПОИСКПОЗ(W5;ИНДЕКС(ДВССЫЛ(W3);1;0);0)

=MATCH(W5;INDEX(INDIRECT(W3);1;0);0)

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

Только в этом случае нулю равен не номер строки, а номер столбца, чтобы получить ссылку на первую строку именованного диапазона «Квартал4», где затем функция ПОИСКПОЗ будет искать «KZ».


И, последним останется вытащить количество заказов функцией ИНДЕКС:

=ИНДЕКС(ДВССЫЛ(W3);X4;X5)

=INDEX(INDIRECT(W3);X4;X5)

Поиск данных в нескольких таблицах Excel, Лайфхак, Таблица, Полезное, Длиннопост

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


P.S. Ссылка на файл примера в комментариях.


P.P.S. Уважаемые подписчики, Вас уже больше 7000 человек, это приятно радует и вдохновляет на создание новых постов)) В комментариях Вы писали, что я один из немногих, на кого Вы подписаны, кто-то впервые подписался на меня, а кто-то специально зарегистрировался, чтобы подписаться)) Это очень приятно и я не хочу Вас разочаровывать.

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

Показать полностью 5
688

Сумма ячеек по цвету, шрифту, формату и т.д

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

1. Сохраняем документ с поддержкой макросов, хотя его там и не будет, но в данном случае так надо.

2. Нажатием Ctrl+F3 или на вкладке «Формулы» выбираем «Диспетчер имён», где в строке «Имя» вводим название формулы ЦветЯчейки, а в поле «Диапазон» саму формулу: =ПОЛУЧИТЬ.ЯЧЕЙКУ(63;ДВССЫЛ("RC[-1]";0))

=GET.CELL(63;INDIRECT("RC[-1]";0))

Сумма ячеек по цвету, шрифту, формату и т.д Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Я не буду грузить читателя разъяснением значений этой формулы, главное, чтобы всё работало, не так ли?)) Отмечу лишь, что 63 это код цвета заливки, заменяя который, можно подсчитывать ячейки по формату, шрифту, выделению курсивом и т.п.

Вот таблица с кодами и их значениями:

Сумма ячеек по цвету, шрифту, формату и т.д Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

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

Сумма ячеек по цвету, шрифту, формату и т.д Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

4. Теперь с помощью формулы =СУММЕСЛИ(B2:B12;10;A2:A12) можно посчитать сумму цветных ячеек, где 10 это код цвета, для жёлтых - 6:

=SUMIF(B2:B12;10;A2:A12)

Сумма ячеек по цвету, шрифту, формату и т.д Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Созданная формула =ЦветЯчейки определяет 56 цветов. На некоторые цветовые гаммы он реагирует некорректно, так что любителям 50 оттенков серого придётся воспользоваться хардкорным чёрным или серым))

Вот пример палитры «твёрдых» цветов:

Сумма ячеек по цвету, шрифту, формату и т.д Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Следует учесть, что Excel при изменении цвета ячейки автоматически не совершит пересчёт данных, для этого необходимо заново ввести формулу, либо нажать Ctlr+Alt+F9, что гораздо проще и быстрее.


Также на этот случай есть макрос,


1. Нажимаем Alt+F11 и в открывшемся окне вводим следующий текст


Public Function SumByColor(DataRange As Range, ColorSample As Range) As Double

Dim Sum As Double

Application.Volatile True


For Each cell In DataRange

If cell.Interior.Color = ColorSample.Interior.Color Then

Sum = Sum + cell.Value

End If

Next cell

SumByColor = Sum

End Function


2. В разделе «Формулы» - «Вставить функцию» выбираем категорию «Определенные пользователем», где указываем нашу функцию, которой задаём диапазон подсчёта и образец цвета:

Сумма ячеек по цвету, шрифту, формату и т.д Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

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

Сумма ячеек по цвету, шрифту, формату и т.д Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Чтобы макрос учитывал не цвет заливки фона, а цвет шрифта ячейки, в шестой строке заменяем свойство Interior на Font в обеих частях выражения.


Для подсчёта количества цветных ячеек, а не суммы, заменяем в седьмой строке Sum = Sum + cell.Value на Sum = Sum + 1


Как и в первом варианте, макрос также не может подсчитать сумму ячеек автоматически, поэтому после произведённых изменений нажимаем клавишу F9.


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

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


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


P.S. Уважаемые подписчики, посты про Excel будут выходить по понедельникам
Показать полностью 5
Мои подписки
Подписывайтесь на интересные вам теги, сообщества,
пользователей — и читайте персональное «Горячее».
Чтобы добавить подписку, нужно авторизоваться.
Отличная работа, все прочитано!