BI — это просто

Информационные функции в DAX: ISBLANK, ISNUMBER, ISEVEN, ISTEXT, ISNONTEXT, ISLOGICAL в Power BI и Power Pivot

Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы рассмотрим информационные функции языка DAX: ISBLANK (проверка на пустое значение), ISNUMBER (проверка на число), ISEVEN (проверка на четность числа), ISTEXT (проверка на текст), ISNONTEXT (проверка на не текст), ISLOGICAL (проверка на логическое значение).

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

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.
Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

DAX функция ISBLANK в Power BI и Power Pivot

ISBLANK () — проверяет переданное в параметрах значение. Если значение пусто, то функция возвращает TRUE, в противоположном случае возвращает FALSE.

Пример формулы на основе DAX функции ISBLANK.

В Power BI Desktop имеется исходная таблица, содержащая в своих строках цифровые и пустые значения:

Создадим в этой таблице второй столбец на основе функции ISBLANK и при помощи нее проверим исходный столбец на наличие пустых ячеек. Формула будет такой:

В результате, мы получили информацию о наличии пустой ячейки, в виде значения TRUE во второй строке:

DAX функция ISNUMBER в Power BI и Power Pivot

ISNUMBER () — проверяет переданное в параметрах значение. Если значение является числом, то функция возвращает TRUE, во всех остальных случаях возвращает FALSE

Пример формулы на основе DAX функции ISNUMBER.

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

то, результат будет обратный тому результату, который мы получали в примере выше. Так как, в этом случае, ISNUMBER вернет значение TRUE по тем ячейкам, где находятся числа, а где пустое значение, там ISNUMBER уже вернет FALSE:

DAX функция ISEVEN в Power BI и Power Pivot

ISEVEN () — проверяет переданное в параметрах число. Если число четное, то функция возвращает TRUE, во всех остальных случаях возвращает FALSE

Пример формулы на основе DAX функции ISEVEN.

В Power BI имеется исходная таблица, содержащая разные числовые значения:

Создадим в этой таблице второй столбец на основе функции ISEVEN по следующей формуле:

В результате ISEVEN проинформировала нас о том, в какой ячейке четное число (TRUE), а в какой нечетное (FALSE):

DAX функции ISTEXT, ISNONTEXT в Power BI и Power Pivot

ISTEXT () — проверяет переданное в параметрах значение. Если значение является текстом, то функция возвращает TRUE, во всех остальных случаях возвращает FALSE.

ISNONTEXT () — проверяет переданное в параметрах значение. Если значение не является текстом, то функция возвращает TRUE, если значение является текстом, то возвращает FALSE

То есть, функции ISTEXT и ISNONTEXT обратные друг другу.

Примеры формул на основе DAX функций ISTEXT и ISNONTEXT.

Так как в Power BI в рамках одного столбца мы не можем размещать разные типы данных (текст и не текст), то примеры формул рассмотрим на основе мер и простых единичных значений, входящих в параметры функций ISTEXT и ISNONTEXT:

Результаты выполнения этих мер по каждой из функций ISTEXT и ISNONTEXT, естественно, будут противоположны друг другу. Там где текст, ISTEXT возвратит TRUE, а ISNONTEXT возвратит FALSE, там где число — все будет наоборот:

DAX функция ISLOGICAL в Power BI и Power Pivot

ISLOGICAL () — проверяет переданное в параметрах значение. Если значение является логическим, то функция возвращает TRUE, во всех остальных случаях возвращает FALSE

Читайте так же:

Как в ворде пронумеровать страницы видео

Пример формулы на основе DAX функции ISLOGICAL.

Точно также, как и в примере выше, так как в рамках одного столбца в Power BI разные типы данных разместить нельзя (логические и не логические), рассмотрим примеры формул в рамках простых мер:

То есть, мера 1 содержит текст, а мера 2 — логическое значение FALSE. Посмотрим, что ISLOGICAL возвратит в эти двух случаях:

Как мы видим, в мере 1, где содержится текст, ISLOGICAL возвратила значение FALSE, а в мере 2, где находится логическое значение, функция уже возвратила TRUE.

На этом, с разбором информационных функций языка DAX: ISBLANK (проверка на пустое значение), ISNUMBER (проверка на число), ISEVEN (проверка на четность числа), ISTEXT (проверка на текст), ISNONTEXT (проверка на не текст), ISLOGICAL (проверка на логическое значение) в Power BI и PowerPivot, все.

Пожалуйста, оцените статью:

Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

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

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

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

Что еще посмотреть / почитать?

CALCULATE и CALCULATETABLE в Power BI и Power Pivot — самые важные функции языка DAX

Как в Power BI рассчитать таблицу ТОП-3 не через вычисляемую таблицу, а через меру?

Функции связанных значений в DAX: RELATED и RELATEDTABLE в Power BI и Power Pivot

Как выделить пустые ячейки на листе Excel

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

Выделите столбцы или строки, в которых требуется заполнить пустоты.
Нажмите Ctrl+G или F5, чтобы отобразить диалоговое окно Go To (Переход).
Нажмите кнопку Special (Выделить).

Замечание: Если Вы вдруг забыли сочетание клавиш, откройте вкладку Home (Главная) и в разделе Editing (Редактирование) из выпадающего меню Find & Select (Найти и выделить) выберите команду Go To Special (Выделить группу ячеек). На экране появится то же диалоговое окно.

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

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

Заполнение пустых ячеек нулями с помощью замены

С помощью надстройки !SEMTools, тем не менее, это можно сделать еще быстрее:

Заполнение пустых ячеек значениями из соседних ячеек

Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек — по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками — например после копирования результатов сводных таблиц или выгрузок в Excel из внешних программ. Таким образом, возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, то бишь.

из

сделать

В общем случае, может возникнуть необходимость делать такое заполнение не только вниз, но и вверх, вправо и т.д. Давайте рассмотрим несколько способов реализовать такое.

Способ 1. Без макросов

Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12).

Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks) :

Не снимая выделения, вводим в первую ячейку знак «равно» и щелкаем по предыдущей ячейке или жмём стрелку вверх (т.е. создаем ссылку на предыдущую ячейку, другими словами):

Читайте так же:

Можно ли использовать термопасту вместо термопрокладки

И, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter . И все! Просто и красиво.

В качестве завершающего мазка я советовал бы заменить все созданные формулы на значения, ибо при сортировке или добавлении/удалении строк корректность формул может быть нарушена. Выделите все ячейки в первом столбце, скопируйте и тут же вставьте обратно с помощью Специальной вставки (Paste Special) в контекстом меню, выбрав параметр Значения (Values) . Так будет совсем хорошо.

Способ 2. Заполнение пустых ячеек макросом

Если подобную операцию вам приходится делать часто, то имеем смысл сделать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert — Module и копируем или вводим туда вот такой короткий код:

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

Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы этот макрос был доступен при работе в любом вашем файле Excel.

Способ 3. Power Query

Power Query — это очень мощная бесплатная надстройка для Excel от Microsoft, которая может делать с данными почти всё, что угодно — в том числе, легко может решить и нашу задачу по заполнению пустых ячеек в таблице. У этого способа два основных преимущества:

Если данных много, то ручной способ с формулами или макросы могут заметно тормозить. Power Query сделает всё гораздо шустрее.
При изменении исходных данных достаточно будет просто обновить запрос Power Query. В случае использования первых двух способов — всё делать заново.

Для загрузки нашего диапазона с данными в Power Query ему нужно либо дать имя (через вкладку Формулы — Диспетчер имен), либо превратить в «умную» таблицу командой Главная — Форматировать как таблицу (Home — Format as Table ) или сочетанием клавиш Ctrl + T :

После этого на вкладке Данные (Data) нажмем на кнопку Из таблицы / диапазона (From Table/Range) . Если у вас Excel 2010-2013 и Power Query установлена как отдельная надстройка, то вкладка будет называться, соответственно, Power Query.

В открывшемся редакторе запросов выделим столбец (или несколько столбцов, удерживая Ctrl ) и на вкладке Преобразование выберем команду Заполнить — Заполнить вниз (Transform — Fill — Fill Down) :

Вот и всё 🙂 Осталось готовую таблицу выгрузить обратно на лист Excel командой Главная — Закрыть и загрузить — Закрыть и загрузить в. (Home — Close&Load — Close&Load to. )

В дальнейшем, при изменении исходной таблицы, можно просто обновлять запрос правой кнопкой мыши или на вкладке Данные — Обновить всё (Data — Refresh All) .

Как заполнить пустые ячейки в Excel?

Зачастую мы имеем таблицу в Excel, в которой присутствуют пустые ячейки. Заполнение таких ячеек вручную займет огромное количество времени.

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

Способ 1: Заполнение пустых ячеек в Excel с помощью заданного значения

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

Следующие шаги иллюстрируют, как заполнить пустые ячейки в Excel с помощью указанного значения.

Шаг 1: Откройте нужный лист Excel, на котором необходимо заполнить пустые ячейки.

Шаг 2: Выберите конкретный столбец, содержащий пустые ячейки.

exl

Шаг 3: Теперь найдите кнопку Найти и выделить в меню Главная и выберите Выделить группу ячеек. Появится окно с различными вариантами. Выберите Пустые ячейки.

Шаг 4: После данного выбора все пустые ячейки в указанном диапазоне будут выделены. Теперь, не нажимая ничего, введите необходимо значение, которое нужно поместить во все пустые ячейки и нажмите Ctrl + Enter. Пустые ячейки заполнятся нужным содержимым.

exl1

exl2

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

Читайте так же:

Как в фотошопе высветлить картинку

Способ 2: Заполнение пустых ячеек в Excel значением, расположенным в ячейке выше

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

Шаг 1: Откройте нужный лист Excel, который содержит пустые ячейки.

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

exl3

Шаг 3: Теперь найдите кнопку Найти и выделить в меню Главная и выберите Выделить группу ячеек . Появится окно с различными вариантами. Выберите Пустые ячейки .

Шаг 4: В строке формул наберите знак » font-family: Verdana, Tahoma;»>все пустые ячейки заполнились значением ячейки выше. Смотрите скриншоты ниже.

exl4

exl5

exl6

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

Способ 3: Заполнение пустых ячеек Excel со значением выше с помощью макроса VBA

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

Шаг 1: Откройте рабочий лист , в котором вы хотите , чтобы заполнить все пустые ячейки со значением над ними.

Шаг 2: Теперь откройте редактор VBA, нажав Alt + F11.

Шаг 3: Щелкните правой кнопкой мыши на любом проекте и перейдите к Insert > Module. После этого вставьте следующий код VBA.

Static rngUR As Range: Set rngUR = ActiveWorkbook.ActiveSheet.UsedRange

Dim rngBlank As Range: Set rngBlank = rngUR.Find(«»)

While Not rngBlank Is Nothing

rngBlank.Value = rngBlank.Offset(-1, 0).Value

Set rngBlank = rngUR.Find(«», rngBlank)

Шаг 4: Теперь сохраните модуль с помощью клавиши Ctrl + S, появившееся диалоговое окно с предупреждением просто проигнорируйте, нажав на кнопку Нет. Теперь вы можете задать сочетание клавиш для выполнения макроса. (меню Разработчик Макросы выберите нужный макрос Параметры Сочетание клавиш (укажите букву).

exl7

Шаг 5: На этом этапе макрокоманда готова к использованию. Чтобы использовать ее, нажмите клавишу быстрого доступа, назначенную для нее, и вы увидите результат в мгновение ока.

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

Как в Excel заполнить ячейки одинаковыми значениями?

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

Это небольшая табличка только на примере и ее можно было заполнить вручную. Но в практике иногда приходится заполнять по 30 тысяч строк. Чтобы не заполнять эту исходную таблицу вручную следует создать формулу для заполнения в Excel данными – автоматически. Для этого следует выполнить ряд последовательных действий:

Перейдите на любую пустую ячейку исходной таблицы.
Выберите инструмент: «Главная»-«Найти и выделить»-«Перейти» (или нажмите горячие клавиши CTRL+G).
В появившемся окне щелкните на кнопку «Выделить».
В появившемся окне выберите опцию «пустые ячейки» и нажмите ОК. Все незаполненные ячейки выделены.
Теперь введите формулу «=A1» и нажмите комбинацию клавиш CTRL+Enter. Так выполняется заполнение пустых ячеек в Excel предыдущим значением – автоматически.
Выделите колонки A:B и скопируйте их содержимое.
Выберите инструмент: «Главная»-«Вставить»-«Специальная вставка» (или нажмите CTRL+ALT+V).
В появившемся окне выберите опцию «значения» и нажмите Ок. Теперь исходная таблица заполнена не просто формулами, а естественными значениями ячеек.

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

Внимание! В 5-том пункте таблица красиво заполнилась без ошибок, так как наша активная ячейка была по адресу A2, после выполнения 4-го пункта. При использовании данного метода будьте внимательны и следите за тем где находится активная ячейка после выделения. Важно откуда она будет брать свои значения.

Заполнение пустых ячеек в excel предыдущим значением

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

Способ 1: Заполнение пустых ячеек в Excel с помощью заданного значения

Читайте так же:

Можно ли восстановить удаленные фотографии вконтакте

Следующие шаги иллюстрируют, как заполнить пустые ячейки в Excel с помощью указанного значения.

Шаг 1: Откройте нужный лист Excel, на котором необходимо заполнить пустые ячейки.

Шаг 2: Выберите конкретный столбец, содержащий пустые ячейки.

Способ 2: Заполнение пустых ячеек в Excel значением, расположенным в ячейке выше

Шаг 1: Откройте нужный лист Excel, который содержит пустые ячейки.

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

Шаг 3: Теперь найдите кнопку Найти и выделить в меню Главная и выберите Выделить группу ячеек . Появится окно с различными вариантами. Выберите Пустые ячейки .

Шаг 4: В строке формул наберите знак «=» и укажите ячейку, содержимым которой необходимо заполнить пустые ячейки. Далее нажмите Ctrl + Enter. Вы увидите, что все пустые ячейки заполнились значением ячейки выше. Смотрите скриншоты ниже.

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

Способ 3: Заполнение пустых ячеек Excel со значением выше с помощью макроса VBA

Шаг 1: Откройте рабочий лист , в котором вы хотите , чтобы заполнить все пустые ячейки со значением над ними.

Шаг 2: Теперь откройте редактор VBA, нажав Alt + F11.

Static rngUR As Range: Set rngUR = ActiveWorkbook.ActiveSheet.UsedRange

Dim rngBlank As Range: Set rngBlank = rngUR.Find(«»)

While Not rngBlank Is Nothing

rngBlank.Value = rngBlank.Offset(-1, 0).Value

Set rngBlank = rngUR.Find(«», rngBlank)

Как быстро заполнить пустые ячейки значениями из ячеек над ними:

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

Выделяем необходимый для заполнения столбец → на вкладке Главная нажимаем Найти и Выделить → Выделить группу ячеек → В появившемся окне выбираем пустые ячейка → нажимаем «ок»

Таким образом у нас будут выделенные только пустые ячейки. Далее не снимая выделения пишем формулу ссылающююся на верхнее значение = (знак равно) → клацаем по верхней ячейки с значением (или стрелка вверх на клавиатуре) → нажимаем сочетание клавиш Ctrl+Enter

Читайте так же:

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

Далее убираем формулы так как они нам больше не понадобятся, для этого выделяем наш столбец, нажимаем «копировать» → щелкаем правой кнопкой мыши выбираем Специальная вставка → Специальная вставка → и в появившемся окне выбираем «значения» → ок

из

сделать

Способ 1. Без макросов

Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12).

Способ 2. Заполнение пустых ячеек макросом

Способ 3. Power Query

Если данных много, то ручной способ с формулами или макросы могут заметно тормозить. Power Query сделает всё гораздо шустрее.
При изменении исходных данных достаточно будет просто обновить запрос Power Query. В случае использования первых двух способов — всё делать заново.

После этого на вкладке Данные (Data) нажмем на кнопку Из таблицы / диапазона (From Table/Range) . Если у вас Excel 2010—2013 и Power Query установлена как отдельная надстройка, то вкладка будет называться, соответственно, Power Query.

Вот и всё Осталось готовую таблицу выгрузить обратно на лист Excel командой Главная — Закрыть и загрузить — Закрыть и загрузить в. (Home — Close&Load — Close&Load to. )

голоса

Рейтинг статьи