Похожие статьи

Аналог функции впр в excel

Новейший выпуск «Бизнесхака на любой денек» — опять о Гугл Таблицах (и про Excel тоже): в их столько способностей, что говорить и говорить. Сейчас научу наглядно представлять огромные данные, чтоб их было комфортно ассоциировать, переносить данные из одной таблицы в другую, также вставлять в ячейки таблиц изображения — полезно и наглядно, мы в МИФе сиим нередко пользуемся.

Приятное представление для длинноватых списков и таблиц: чередующиеся строчки

Если в вашей таблице 10-ки и сотки строк («Операции», «Сотрудники», «Клиенты», «Продукты», «Сделки» и т.д.) и несколько столбцов — для вас будет тяжело ассоциировать примыкающие строчки, они станут соединяться. Выход — чередующаяся заливка строк, которую можно создать как в Excel при помощи встроенного инструмента (Основная → Форматировать как таблицу), так и в Гугл Таблицах (Формат → Чередование цветов):

Функция VLOOKUP (перенос данных из таблицы в таблицу)

Это функция — аналог функции ВПР в Excel. Она нужна для переноса данных из одной таблицы в другую. У нее последующие аргументы:

VLOOKUP (разыскиваемое значение; таблица; номер столбца; интервальный_просмотр)

Разыскиваемое — то значение, которое мы будем находить в иной таблице. Принципиально отметить: если значение встречается пару раз, функция отыщет 1-ое вхождение — и подтянет данные из соответственной ему строчки таблицы.

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

Аргумент «Номер столбца» описывает, из какого столбца ТАБЛИЦЫ (а не листа — это принципиально!) вы будете брать данные.

«Интервальный_просмотр» обычно равен нулю — в таком случае будет вестись четкий, а не ориентировочный поиск.

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

Примечание. Если начальная таблица будет в будущем расширяться, указывайте спектр не как в примере — $A$2:$C$13, а без строк — $A:$C. По другому уже из 14-й строчки, когда она добавится, формула не будет подтягивать данные.

Вставляем в ячейки Гугл Таблиц изображения: функция IMAGE

Функция IMAGE дозволяет добавлять в ячейки Гугл Таблиц изображения. У функции последующий синтаксис:

IMAGE(URL, [mode], [height], [width])

URL — единственный неотклонимый аргумент. Это ссылка на изображение. Ссылку можно указать впрямую в формуле, взяв в кавычки:

Либо же поставить ссылку на ячейку, в которой ссылка хранится:

Аргумент mode может принимать четыре значения (если его пропустить, по дефлоту будет 1-ое):

— 1: изображение растягивается до размеров ячейки с сохранением соотношения сторон;

— 2: изображение растягивается без сохранения соотношения сторон, полностью заполняя ячейку;

— 3: изображение вставляется с необычным размером;

Интересно почитать: Lookup функция в эксель

— 4: вы указываете размеры изображения в 3-ем и четвертом аргументах функции [height] и [width].

[height], [width] соответственно необходимы лишь при значении аргумента mode = 4. Они задаются в пикселях.

Итак. Допустим, у нас есть перечень книжек, и мы желаем добавить обложки:

Сам текст формул указан правее. 2-ой аргумент — mode — равен 2, потому обложки будут полностью растягиваться до размеров ячеек (может быть весьма маленькое искажение пропорций, потому что 2-ой режим это допускает):

Отлично. А если б мы указали mode = 1?

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

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

Припоминаю, что как и обычно, все данные в примерах вымышлены. Еще более нужных советов на все случаи жизни — в нашей с Игорем Манном книжке «Бизнесхак на любой денек». Читайте, планируйте, действуйте!

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

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы побеседуем про функции RELATED и RELATEDTABLE в Power BI и Power Pivot.

Конкретно эти функции разрешают, находясь в одной таблице, достать до значений в иной таблице через внутренние связи DAX, настроенные во вкладке «Связи» в Power BI либо Excel (Power Pivot).

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

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

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

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

RELATED () — находясь в одной таблице, дозволяет в рамках контекста строчки получить связанное значение из 2-ой таблицы по связи «Почти все к одному».

Синтаксис: RELATED ([Столбец])

Разглядим пример DAX формулы с ролью RELATED.

В Power BI Desktop имеются 2 начальные таблицы «Менеджеры Реализации» и «Менеджеры Отделы»:

Интересно почитать: Функция смещ в excel примеры

Меж ними настроена связь по полю «Менеджер» по типу «Почти все к одному», другими словами, много менеджеров может находится в одном отделе:

Попробуем в таблицу «Менеджеры Реализации» через связь добавить 3-ий столбец [Отделы]:

Для этого сделаем в Power BI Desktop во вкладке «Моделирование» вычисляемый столбец и попытаемся в его формуле прописать столбец [Отделы] из связанной таблицы «Менеджеры Отделы»:

И по данной формуле у нас всплывает ошибка:

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

Давайте перепишем формулу вычисляемого столбца с внедрением RELATED:

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

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

Другими словами, нам необходимо подсоединиться через связь к таблице «Менеджеры Реализации» и там посчитать количество продаж всякого менеджера. Это количество мы можем посчитать с помощью DAX функции COUNTWROS, которая считает количество строк. Ну а подсоединяться к иной таблице через связь мы будем при помощи RELATED.

Пропишем данную формулу:

И у нас снова вышла ошибка:

По сути ошибка закономерна, потому что функция RELATED работает по связи «Почти все к одному», что у нас было соблюдено в первом примере и что мы нарушили на данный момент. Потому что в данном примере у нас уже связь иная, а конкретно «Один ко почти всем» (один отдел может в для себя содержать много менеджеров) и с данной связью RELATED уже не работает.

Здесь нам на помощь может придти 2-ая функция работы по связям в DAX: RELATEDTABLE.

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

RELATEDTABLE () — находясь в одной таблице, возвращает связанную таблицу значений из 2-ой таблицы по связи «Один ко почти всем», где одна строчка соответствуем почти всем строчкам.

Синтаксис: RELATEDTABLE (‘Таблица’)

Функция RELATEDTABLE, в отличие от RELATED, уже не возвращает некое одно скалярное значение, она возвращает конкретно связанную таблицу значений, с которыми мы можем что-то создать, к примеру посчитать количество строк:

Давайте доработаем формулу предшествующего примера и исправим там ошибку, а конкретно, заменим функцию RELATED на RELATEDTABLE:

Сейчас у нас все отлично, пример формулы с RELATEDTABLE отработал непревзойденно и посчитал нам количество продаж по любому менеджеру:

Интересно почитать: Функция расцепить в excel примеры

На этом, с разбором функций связи языка DAX в Power BI и Power Pivot — RELATED и RELATEDTABLE, все.

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

Фурроров Для вас, друзья!
С почтением, Будуев Антон.
Проект «BI — это просто»

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

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

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

Что еще поглядеть / почитать?

DATESYTD, DATESQTD и DATESMTD — DAX функции группы time intelligence, возвращающие даты текущего периода в Power BI и Power Pivot

Текстовые функции в DAX: BLANK, FIXED, LEN, REPT и TRIM в Power BI (Power Pivot)

Функции округления в DAX: INT, TRUNC, ROUND, ROUNDDOWN, ROUNDUP, MROUND, FLOOR, CEILING, ISO.CEILING в Power BI и Power Pivot

Добавить комментарий

Подписывайтесь на наши социальные сети

Конкретно в их оперативно и любой денек Для вас будут доступны наши животрепещущие фишки, секреты, выработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel. )

Присоединяйтесь к нашим соц сетям

Наша группа в VK

Мы в Инстаграме

Наш YouTube канал

Крайние видео на нашем YouTube канале:

Оставляя индивидуальные данные (email, имя, логин) в формах на страничках данного веб-сайта «BI — это просто», Вы автоматом подтверждаете свое согласие на обработку собственных индивидуальных данных

Данный веб-сайт «BI — это просто» при собственной работе употребляет файлы cookie. Продолжая употреблять веб-сайт, Вы даете свое согласие на работу с этими файлами.

Справочник DAX функций для Power BI и Power Pivot

на российском языке с подробными примерами формул на практике

ищете подробное описание DAX функций для Power BI либо Power Pivot на российском языке
нуждаетесь в примерах формул и их демонстрации на практике
утомились разбираться с функциями без помощи других
тратите большущее количество времени на создание формул способом «тыка»

то, справочник DAX функций для Power BI и Power Pivot — это то, что Для вас необходимо!

+ БОНУС (видеокурс по DAX)