Магия Excel

Считаем количество ответов на форму... формулой Вы проводите опрос и хотите быстро посмотреть статистику: сколько раз пользователи выбирали тот или иной вариант при ответе на каждый вопрос (конечно, такое не прокатит с открытыми вопросами, а только при выборе из списка вариантов) Можно поступить так: 1. выведем список уникальных ответов (функция UNIQUE) 2. для каждого ответа посчитаем, сколько раз он встречается в столбце (COUNTIF / СЧЁТЕСЛИ) 3. Объединим в одну текстовую строку через дефис или другой разделитель: COUNTIF(диапазон;ответ)&" - "&ответ 4. Чтобы все было одной формулой, проделаем это через MAP — массивом значений для этой функции и будет список уникальных ответов. Для каждого из них мы будем считать, сколько раз он встречается, и склеивать число с текстом ответа. Можно добавить проверку на пустоту через ISBLANK / ЕПУСТО, чтобы не выводить пустые ответы и ссылаться на открытый диапазон (если ждете новых ответов на форму). =MAP(UNIQUE(диапазон с ответами);LAMBDA(ответ;IF(ISBLANK(ответ);"";COUNTIF(диапазон;ответ)&" - "&ответ)))

Теперь формулы со вспомогательными функциями LAMBDA можно записывать короче. Допустим, вы хотите посчитать среднее значение в каждой строке. Чтобы обработать каждую строку, нужно использовать BYROW. Задать в первом аргументе диапазон, во втором — LAMBDA, внутри которой будет обозначение для каждой строки и формула — что с ней делать: =BYROW(диапазон;LAMBDA(строка;СРЗНАЧ(строка))) (здесь на месте переменной "строка" может быть любое другое название — вы сами решаете, под каким именем обращаться к строке) А теперь будет работать и следующая форма: =BYROW(диапазон;СРЗНАЧ) =BYROW(диапазон;AVERAGE) Подробнее про LAMBDA и BYROW можно узнать в бесплатном мини-курсе на Stepik: https://stepik.org/course/182713

Магия Excel pinned «Хочу изучить конкретную тему в рамках Excel. Какую одну книгу мне прочитать? Excel в целом Microsoft Excel Inside Out (Office 2021 and Microsoft 365) На русском: Excel 2019. Библия пользователя — Куслейка, Александер Макросы Microsoft Excel VBA and Macros…»

Магия Excel pinned «Избранное: актуальная и обновленная подборка самых сочных материалов нашего канала Ctrl + Backspace - очень удобное сочетание клавиш для возвращения к активной ячейке Макрос для сравнения двух файлов (книг Excel) Удаляем строки с пустыми ячейками в одном…»

На горе Фернандо-По применяли мы ГРУПППО Мощная новинка в Excel: функции, которые позволяют агрегировать данные, де-факто — строить сводные, но формулами. То есть с автоматическим обновлением. К тому же функции можно использовать как аргументы других функций (и им передавать в качестве аргументов не диапазоны, а другие функции), что еще мощнее расширяет возможности. Новые функции: GROUPBY / ГРУПППО и PIVOTBY / СВОДПО. Первая позволяет группировать только в строках, вторая и в столбцах тоже. Обязательных аргументов всего три или четыре: по какому столбцу группируем, какой агрегируем, какую функцию применяем. Необязательными аргументами можно добавить промежуточные и/или общие итоги, заголовки, сортировать и фильтровать. В видео (11 мин, со звуком) обсуждаем, как вообще можно сводить данные и чем отличаются разные способы: — в любой версии Excel: формулы (не очень гибко и без обновления в случае появления новых данных) — в любой версии Excel: сводная таблица (очень гибко, но сводную надо обновлять вручную/макросом) — в 2016 и новее (а также 2010-2013 с установкой надстройки): Power Query (обычно этой надстройкой данные предварительно обрабатывают и потом строят сводную, но в ней можно сразу группировать) (относительно гибко, обновлять вручную или по расписанию раз в N минут) — пока только с бета-каналом обновлений в Microsoft 365, позже у всех подписчиков 365: новые функции (гибко и обновляется все автоматом) Видео на Youtube: https://www.youtube.com/watch?v=1xN7Hly-oc0 (про новые функции с 6 минуты)

Когда ты навертел такого в формулах, что даже Microsoft уже интересно посмотреть, что же там происходит 😺 Вот такое уведомление появилось при работе над одним мини-проектом для большой компании. До этого не встречали :) а вы?

Получаем только четные или нечетные строки Для такой задачи нам понадобится функция ВЫБОРСТРОК / CHOOSEROWS. Она может извлечь (вернуть) строки по их номерам. А вот как достать эти номера? Получается, нам нужны строки с первой (если нечетные) до последней в таблице. Число строк в таблице можно узнать функцией ЧСТРОК / ROWS. Нечетных строк в таблице половина — то есть мы поделим общее число строк пополам. Получается, что нам нужна последовательность 1, 3, 5, ..., Число строк в таблице / 2 Иначе говоря, от единицы с шагом 2, количество — половина строк в таблице. Засунем все эти параметры в функцию ПОСЛЕД / SEQUENCE — она и выдаст нам нужную последовательность: ПОСЛЕД(ЧСТРОК(Таблица)/2;1;1;2) А дальше останется использовать ее как аргумент (точнее, аргументы — ведь это много номеров строк) функции ВЫБОРСТРОК / CHOOSEROWS: =ВЫБОРСТРОК(Таблица;ПОСЛЕД(ЧСТРОК(Таблица)/2;1;1;2))

Выделяем всю строку при наличии в столбце слова (буквы, текста) Как покрасить всю строку, если в одном из столбцов встречается какое-то слово (то есть не занимает ячейку целиком, просто знаком равно = сравнивать нельзя)? Можно функцией ПОИСК / SEARCH или НАЙТИ / FIND. Первая работает без учета регистра, вторая — с. =НАЙТИ (что ищем; где ищем) На выходе — порядковый номер символа, позиция искомого значения в тексте для поиска. В тех случаях, когда они не находят искомое значение, возвращается ошибка. В случае условного форматирования при ошибках в формуле правило не сработает, а это то что нам нужно. Главное в условном форматировании — не запутаться со строками и столбцами. Формула в условном форматировании вводится один раз — при ее формировании учитывайте, что вы вводите формулу для левой верхней ячейки диапазона, а далее на все остальные ячейки она будет «протягиваться» (как если бы мы ввели формулу в B2 и далее скопировали и вставили во все остальные ячейки в трех столбцах). И ссылки на ячейки будут меняться, если они являются относительными. Смотрим на видео!

Аргументами функции ВЫБОР / CHOOSE могут и... другие функции! Можно сделать выпадающий список, в котором будет несколько вариантов вычислений. В нашем простом примере — в ячейке M8 список (проверка данных) из диапазона M4:M6 — там среднее, сумма и количество. Функция ПОИСКПОЗ / MATCH подскажет нам, каким по порядку в списке идет выбранный тип вычисления. Полученное число будет первым аргументом функции ВЫБОР. А последующие аргументы — функции. Например, если пользователь выбрал "сумму", ПОИСКПОЗ будет возвращать 2. А значит, ВЫБОР выдаст второй элемент из списка. Это функция СУММ. Получается, что мы выбираем, какую формулу вычислять на основе значения из выпадающего списка. Простой вариант добавления интерактива в отчеты!

Выбираем случайный элемент из списка Для начала надо сгенерировать случайное число — с этим поможет функция СЛУЧМЕЖДУ / RANDBETWEEN. Она выдает случайное целое число в заданном диапазоне. Например, если список из трех элементов: =СЛУЧМЕЖДУ(1;3) А чтобы выбрать элемент по сгенерированному номеру, понадобится функция ВЫБОР / CHOOSE. В первом аргументе у нее задается порядковый номер, а далее по порядку элементы, из которых нужно выбрать. =ВЫБОР(номер; первый элемент; второй; ...) В нашем случае номером будет случайное число, ну а все следующие аргументы ВЫБОРа — это элементы списка. В новой версии Excel можно сгенерировать не одно значение, а сразу целый массив, ведь теперь есть функция СЛМАССИВ / RANDARRAY: =СЛМАССИВ(число строк; число столбцов; от; до; целое) В нашем случае 10 строк, столбцы пропускаем (1 по умолчанию), от 1 до 3, целые числа. И получим список из 10 случайных элементов списка одной формулой.

План-факт через комбинированную диаграмму Вот такая диаграмма для сравнения двух показателей (план и факт, производство и продажи, ...). Как ее построить? — Тип диаграммы в целом — комбинированная, тип каждого ряда данных — гистограмма. Один из рядов данных — на вспомогательную ось, сама ось удалена (так как она не отличается по значениям от основной) — это все можно настроить, нажав "Изменить тип диаграммы" на ленте или в контекстном меню. — Делаем разные значения бокового зазора у обеих гистограмм, чтобы столбики отличались по ширине. Заливку у обеих делаем прозрачной (в примере 40%). Это настраивается в панели "Формат", выделяем столбики и нажимаем Ctrl+1. — Подписи забираем из ячеек в столбце D. Там формула (просто темп прироста, итоговый показатель — факт — делим на базисный — план — и вычитаем единицу) и пользовательский формат: +0%* 🔥;-0%* 👎 (смайлики выберите по вкусу; чтобы зайти в окно настройки формата, нажмите Ctrl+1) — Добавляем таблицу данных вместо основных подписей и легенды и убираем всякое ненужное (линии сетки, например). P.S. Файл с диаграммой прикреплен в отдельном сообщении выше — забирайте!