Функции СУММЕСЛИ и СУММЕСЛИМН одни из самых популярных в Excel. Но они не работают с закрытыми файлами. Обойти можно, заменив СУММЕСЛИ на СУММПРОИЗВ. Она справится.
#УР2 #Применение_встроенных_функций

При работе со справочником и функциями ВПР или ИНДЕКС+ПОИСКПОЗ пользователи часто применяют функцию ЕСЛИОШИБКА, чтобы вместо ошибки НД выводить, например, текст "Нет в справочнике". У этого подхода есть одна проблема.

ЕСЛИОШИБКА обрабатывает любую ошибку. Если значения действительно нет в справочнике, то всё в порядке. Мы получаем нужный текст-заменитель. Но если в справочнике значение есть, а вот результат, который надо вернуть, является ошибкой - то текст "Нет в справочнике" будет не корректен. Ведь ошибка не в этом. В таких случая лучше использовать специальные функции для обработки именно ошибки НД. Используйте ЕНД или ЕСНД (если у вас Excel 2013 или новее).

#УР2 #Примеры_формул

Вводить время в ячейки Excel очень просто. Достаточно указать часы, минуты и секунды через двоеточие. Но если значение времени нужно использовать в какой-то формуле, то такой способ ввода не сработает. Формула просто не поймёт, что мы пытаемся работать со временем.

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

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

И помните - любую констанут из формулы можно вынест в ячейку и ссылаться на неё.

#УР2 #Примеры_формул

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

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

Ну а если очень хочется именно кнопки в столбцах значений, то испоьзуйте приём из этого урока https://t.me/excel_everyday/957

#УР2 #Сводные_таблицы

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

- различные значения - все варианты значений, встречающиеся в диапазоне. В примере выше это Арбуз, Дыня, Яблоко, Банан
- уникальные значения - те значения, которые в списке встречаются только один раз. В примере выше это Яблоко и Банан.

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

#УР2 #Примеры_формул

В Excel есть функция, которая делает первую букву заглавной во всех словах в ячейке. Чтобы сделать заглавной только букву первого слова, надо написать мини-формулу
#УР2 #Применение_встроенных_функций

Дополнительные вычисления в сводной могут помочь при расчете нарастающего итога. Например, можно легко подсчитать количество продаж (чеков) по датам в отдельности и накопительно.
Тут главное - следить за сортировкой того поля, по которому будет считаться нарастающий итог. Он всегда будет рассчитываться сверху вниз.
#УР2 #Сводные_таблицы

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

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

#УР2 #Сводные_таблицы

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

Придется писать формулу массива. Один из вариантов - использование функции СОВПАД. Она умеет сравнивать два значения, принимая во внимание различие строчных и прописных букв. Ну а работать она будет, разумеется, в связке с ИНДЕКС и ПОИСКПОЗ, как это обычно и бывает в подобных задачах.

#УР2 #Примеры_формул

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

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

#УР2 #Сводные_таблицы

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

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

#УР2 #Условное_форматирование

В сводной таблице есть возможность вручную группировать значения в строках или столбцах. Достаточно выделить нужные значения (используя CTRL или SHIFT), кликнуть на них правой кнопкой мыши и выбрать команду "Группировать". Останется только поменять название созданной группы.

Для многоуровневых группировок следует соблюдать порядок создания. Сначала создаюстя группы самого глубокого уровня вложенности. Затем - родительские для них и т.д. Использование группировок позволяет мгновенно увидеть в сводной таблице промежуточные итоги по созданным группам.

#УР2 #Сводные_таблицы

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

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

#УР2 #Проверка_данных

У нас на канале уже был урок про то, как получить из даты название месяца. Но что, если имеется только номер месяца (от 1 до 12)? И нужно по этому номеру получить название.

Вариантов решения есть множество. ВПР с небольшой справочной табличкой, функция ВЫБОР и т.д. Показываем пару вариантов на основе функции ТЕКСТ. Вся суть в том, чтобы из номера месяца получить любую дату этого месяца. А уже из нее ТЕКСТ легко извлечет название.

#УР2 #Примеры_формул

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

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

#УР2 #Сводные_таблицы

Стандартные функции подсчета и суммирования (СУММ, МАКС, МИН, СРЗНАЧ) не справляются, если диапазон содержит ошибки. На выходе они выдают ошибки.

Но есть функция, которая умеет проводить все описанные подсчеты, да еще и с возможность пропуска ошибок. Это функция АГРЕГАТ. При вводе первого и второго аргументов, выбирайте, что считать и как считать. И функция всё сделает.
#УР2 #Применение_встроенных_функций

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

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

Решение можно применить такое: превратить искомое значение из "числа как текст" в число прямо внутри ВПР (в первом аргументе), применив, например, двойное отрицание "--". Это сразу решает проблему.

#УР2 #Примеры_формул

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

#УР2 #Сводные_таблицы

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

#УР2 #Сводные_таблицы

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

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

#УР2 #Сводные_таблицы