Ребята, всем привет! 👋 Сегодня я покажу Вам трюк Excel связанный с двойным отрицанием, а так же расскажу 3️⃣ СПОСОБА как создать список не содержащий пустых значений.
Файл с решением можно скачать 📩 по ссылке в конце статьи
✨ А прежде, чем мы начнем 📣 напомню, теперь у нас на канале есть удобный рубрикатор 👉 Быстрый поиск решения. Путеводитель по Excel, а все видео 📽 предыдущих уроков доступны и на YouTube.
📚 Немного теории....
В математике унарная операция - это операция только с одним операндом (вводом).
Двойной унарный или двойное отрицание - это операция, используемая для принудительного преобразования значений TRUE / FALSE в 1 (единицы) и 0 (нули).
Иными словами, двойной унарный - это двойной минус "--" который заставляет Excel выполнять математические вычисления по результатам остальной части формулы.
👩🏫 Чтобы было понятней рассмотрим пример:
👉 Дано: таблица, содержащая сведения по группе слушателей (обучающихся) с оценками по темам:
👉 Найти:
- ❓ Какое число учащихся (слушателей) получили max балл ⬆️ по всем программам;
- ❓ Какое число учащихся (слушателей) получили наименьший балл ⬇️ по всем программам;
- ❓ Какое число учащихся (слушателей) не допущены ❌ к экзамену
А также:
- ❗️ создать отдельную таблицу 📄 содержащую сведения (ФИО) слушателей (обучающихся) не допущенных к экзамену
✅ РЕШЕНИЕ:
Для начала выполним предварительные настройки:
- Максимальный балл (в условиях примера) = 15, заносим в ячейку F2;
- Наименьший балл который получил хотя бы один из слушателей (обучающихся) определяем формулой: =НАИМЕНЬШИЙ(Таблица_Слушатели[итоговый балл];1), заносим в ячейку F3;
- Пороговый балл для допуска к зачету (в условиях примера) = 13, заносим в ячейку F3;
- Итоговый бал по столбцу Таблица_Слушатели[[# Все];[итоговый балл]] определяем формулой: =СУММ(Таблица_Слушатели[@[Программа 1]:[Программа i]])
- Критерий допущен/не допущен будем определять формулой: =ЕСЛИ(ИЛИ([@[итоговый балл]]<$F$4;[@[итоговый балл]]="*");"не допущен";"допущен") /почему так? разъясняем ниже
- А также создадим дополнительный столбец, где задаем условие: =ЕСЛИ([@[Допуск к экзамену]]="не допущен";[@[Учащийся (слушатель)]];""):
📽 ВИДЕО ОБЗОР см. внизу статьи
🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽
Итак, для того, чтобы подсчитать совпадения (истинные значения) равные 15 воспользуемся функцией СУММПРОИЗВ()
... НО в "чистом виде" функция СУММПРОИЗВ()/[SUMPRODUCT] будет игнорировать нечисловые значения, поэтому результат будет 0:
Поэтому мы и используем "двойное отрицание" для изменения значений TRUE / FALSE на 1 (единицы) и 0 (нули):
🔶 Требуемая формула имеет вид:
=СУММПРОИЗВ(--(Таблица_Слушатели[итоговый балл]=F2))
🔔 Как это работает:
Искомое значение 15 сравнивается со значениями диапазона Таблица_Слушатели[итоговый балл], которое создает массив из значений TRUE/FALSE:
Т.к. мы применили "двойное отрицание", то Excel автоматически присваивает TRUE / FALSE значение 1/0.
Результат внутри СУММПРОИЗВ() выглядит следующим образом:
Альтернативным решением будет формула:
=СУММПРОИЗВ(--(C13:C19=5);--(D13:D19=5);--(E13:E19=5))
где 5 - это максимальный балл по каждой теме (естественно, данное значение так же можно вывести в отдельную ячейку)
☑️ Вывод: функция СУММПРОИЗВ(--(массив1;[массив2];[массив3];...)) возвращает сумму всех элементов удовлетворяющих условию
Таким образом мы смогли определить, что число учащихся (слушателей) получивших max балл по всем программам = 3.
Аналогично поступаем далее изменив условие:
- число учащихся (слушателей) получили наименьший балл по всем программам:
=СУММПРОИЗВ(--(Таблица_Слушатели[итоговый балл]=F3))
- число учащихся (слушателей) не допущены к экзамену:
=СУММПРОИЗВ(--(Таблица_Слушатели[итоговый балл]<F4))
➡️ Наш результат:
Итак, с количеством мы разобрались...
Далее необходимо составить пофамильный список слушателей (обучающихся) не допущенных к экзамену. И вот тут есть 3 СПОСОБА!!!
✅ СПОСОБ №1. Сводная таблица
Создаем сводную таблицу и фильтруем по значению "не допущен":
⚠️ Но учтите нюанс:
при данных условиях может сложиться ситуация когда нет слушателей (обучающихся) не допущенных к экзамену
Поэтому и идем на хитрость: в нашей таблицы верхнюю строку делаем "справочной" (далее ее скроим) , где как указывалось в предварительных условиях критерий "не допущен"определен формулой:
=ЕСЛИ(ИЛИ([@[итоговый балл]]<$F$4;[@[итоговый балл]]="*");"не допущен";"допущен")
также в ячейке A12 можно поставить "***"
⛔️ Какие минусы: при изменении данных в искомой таблицы сводную требуется обновлять.
🔷 Для обновления данных в сводной таблице "в ручную" следует:
- "кликнуть" правой кнопкой мыши по любой выделенной ячейке сводного. В открывшемся меню выбираем «Обновить»
- или активизируем нужное поле сводного отчета – становится доступен инструмент Работа со сводными таблицами. Открываем вкладку «Параметры». В группе Данные нажимаем кнопку Обновить
- или создадим макрос:
Для этого запускаем редактор Visual Basic
о том как запустить редактор Visual Basic см. здесь
🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽
▶️ В открывшемся редакторе пишем код:
▶️ Далее, на листе с данными создаем любой объект выполняющий функцию кнопки (картинку, фигуру ит.п. не важно) и данному объекту назначаем наш макрос:
➡️ Наш результат:
Теперь "кликнув" на "кнопку" наша сводная будет обновляться..
... но это вряд ли можно назвать удобным способом, ведь каждый раз (при изменении данных) сводную таблицу необходимо обновлять. А потому... есть способ №2
✅ СПОСОБ №2. Автообновляемая сводная таблица
Здесь нам потребуется модуль листа.
Модуль листа принадлежит только объекту Worksheet (Рабочий лист).
Открытый модуль доступен во вкладке Window главного меню.
▶️ Открыть модуль листа можно:
- или перейдя из рабочей книги, кликнув правой кнопкой мыши по ярлыку этого листа и выбрав в контекстном меню Просмотреть код:
- или через редактор Visual Basic:
- или дважды кликнув в проводнике по его имени
- или открыв на нем правой кнопкой мыши контекстное меню и выбрав View Code
🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽🔽
▶️ В модуле листа пишем код:
➡️ Наш результат:
Теперь Ваша сводная таблица будет обновляться автоматически
Удобно ?.. - "Безусловно да!" Но... есть и способ №3
✅ СПОСОБ №3. Пользовательская функция
Здесь нам так же потребуется запустить редактор Visual Basic ... и создаем новый модуль:
▶️ В модуле пишем код:
▶️ Теперь наша функция RemoveSpaces() доступна для использования.
Наша задача лишь выделить нужный диапазон и добавить в него формулу.
Как вы помните мы создавали дополнительный столбец, где указывали условие: =ЕСЛИ([@[Допуск к экзамену]]="не допущен";[@[Учащийся (слушатель)]];"")
Вот им сейчас и воспользуемся...
Итак, определяем диапазон для формирования списка 📄 содержащего сведения (ФИО) слушателей (обучающихся) не допущенных к экзамену. Пусть это будет диапазон О12:О24. Выделяем данный диапазон:
... и строке формул вставляем функцию RemoveSpaces с ссылкой на диапазон содержащий пустые значения =RemoveSpaces(Таблица_Слушатели[ФИО])
🔔 ВАЖНО!!! Помним, что это функция массива, а поэтому ввод формулы завершаем нажатием Ctrl+Shift+Enter:
➡️ Готово!!! Наш результат:
👏 Не правда ли здорово и удобно?!! 🙂
Возможно Вы знаете иные способы решения данной задачи?
📝 Нам очень важна Ваша обратная связь - пишите об этом в комментариях.
А на этом сегодня все. Применяйте данные способы на практике и это позволит упростить работу и экономить время 👏
📽 ВИДЕО ОБЗОР
Продолжение следует...
Подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel. Следите за нашими новостями и вы узнаете больше о VBA и Excel в частности.
В следующих уроках более подробно рассмотрим:
☑ форматирование сводной таблицы;
☑ настройка макета сводной таблицы по умолчанию
☑ условное форматирование в сводных таблицах
☑ обновление сводных таблиц и сводных диаграмм
и др.
За лайк 👍 и репост 🔁 данного поста благодарочка 💖 и респект 🤝 каждому!
📩 СКАЧАТЬ файл с примером
#excel #двойной унарный #Пользовательская функция #эксель #фишки excel #примеры excel #сводная таблица excel #СУММПРОИЗВ excel #макросы excel #список не содержащий пустых значений
