7. Полезняшки Excel

Поиск последнего вхождения на основе пользовательской функции ВПР2

Рубрика: 7. Полезняшки Excel

В Excel довольно широко представлены функции поиска – ВПР, ГПР, ПОИСКПОЗ в связке с ИНДЕКС, ПРОСМОТР, и некоторые другие. Все они относятся к категории функций Ссылки и массивы. При этом штатно они ищут первое встретившееся в диапазоне значение, отвечающее условиям поиска. Как было бы замечательно, если бы функция ВПР имела пятый аргумент, позволяющий выбрать направление поиска: с начала диапазона или с конца! Но увы, такой возможности нет. Ранее я описал изящный трюк, позволяющий с помощью функции ПРОСМОТР решить такую задачу. А недавно обнаружил довольно простую пользовательскую функцию, которую немного доработал и назвал ВПР2.

Ris. 1. Okno Argumenty funktsii VPR2

Рис. 1. Окно Аргументы функции ВПР2

Читать полностью

Замена некоторых формул значениями

Рубрика: 7. Полезняшки Excel

В силу различных обстоятельств иногда возникает потребность заменить формулы в ячейках значениями. Если это связный прямоугольный диапазон, сделать такую замену весьма просто. Выделите диапазон, нажмите Ctrl+C. Не снимая выделения, кликните правой кнопкой мыши в любом месте выделенного диапазона, выберите Специальная вставка, и в открывшемся окне выберите значения. Нажмите Ok. Если вы любите клавиатурные сокращения, то для специальной вставки используйте Alt+Я+М+З (версия для Excel 2016 и позднее; все буквы русские).

Ris. 1. Vstavit tolko znacheniya

Рис. 1. Вставить только значения

Читать полностью

Приложения. SSAS Tabular, формулы кубов, сообщения об ошибках

Рубрика: 7. Полезняшки Excel

Это окончание перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую начать сначала.

Предыдущая глава        Содержание

В книге мы сделали несколько смелых заявлений о влиянии Power Pivot на вашу карьеру. Мы использовали такие слова, как программист, инженер и разработчик, чтобы описать вашу меняющуюся роль. Однако, в какой-то момент вы, возможно, почувствуете, что PowerPivot уже недостаточно: ваши файлы становятся слишком большими или вы переросли модель доступа «всё или ничего»… Ну что ж, познакомьтесь с большим братом Power Pivot – SSAS Tabular (не путайте с табличными службами SSAS, которые используют тот же движок, что и Power Pivot).

Ris. P.1. Krivaya obucheniya Excel PowerPivot i SSAS Tabular

Рис. П.1. Кривая обучения Excel, PowerPivot и … SSAS Tabular

Читать полностью

Глава 28. Серверное/облачное размещение данных для модели DAX

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую начать сначала.

Предыдущая глава        Содержание    Следующая глава

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

Ris. 28.1. Vse chto my sozdaem v Power BI Desktop sohranyaetsya v fajle pbix v Power Pivot xlsx

Рис. 28.1. Все, что мы создаем в Power BI Desktop, сохраняется в файле *.pbix; в Power Pivot – *.xlsx

Читать полностью

Глава 27. Новые функции и переменные DAX

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую начать сначала.

Предыдущая глава        Содержание    Следующая глава

Всё, что описано в этой главе будет работать, начиная с Excel 2016, или в Power BI Desktop.

Ris. 27.1. Vychislyaemyj stolbtse na osnove funktsiya DATEDIFF v Power BI Desktop

Рис. 27.1. Вычисляемый столбце на основе функция DATEDIFF() в Power BI Desktop

Читать полностью

Глава 26. Продвинутые вычисляемые столбцы в Power Pivot

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую начать сначала.

Предыдущая глава        Содержание    Следующая глава

Вычисляемые столбцы не являются сильной стороной DAX. Меры – это да! (поэтому почти вся книга посвящена им). А вот столбцы есть и в обычном Excel, поэтому, по большому счету, здесь мало что добавишь. На самом деле, вычисляемые столбцы в Power Pivot даже немного сложнее, чем обычные столбцы Excel, потому что Power Pivot не хватает ссылки в стиле "A1". А начнем мы с относительно простых трюков.

Ris. 26.1. Prodazhi v zavisimosti ot temperaturnogo diapazona

Рис. 26.1. Продажи в зависимости от температурного диапазона

Читать полностью

Глава 25. Пользовательский календарь в Power Pivot

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую начать сначала.

Предыдущая глава        Содержание    Следующая глава

Мы очень любим создавать пользовательские календари в DAX. Тем не менее, нам потребовалось некоторое время, чтобы открыть волшебную формулу. Сейчас мы поделимся с вами нашим секретом.

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

Ris. 25.1. Primer polzovatelskogo kalendarya 4 5 4

Рис. 25.1. Пример пользовательского календаря 4/5/4, используемого в розничной торговле[1]

Читать полностью

Глава 24. Нюансы функций CALCULATE() и FILTER()

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую начать сначала.

Предыдущая глава        Содержание    Следующая глава

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

О том, что аргумент <filter> функции CALCULATE() переопределяет контекст фильтра, заданный координатами сводной таблицы, мы уже говорили в главе 8. Обсудим это подробнее. Для облегчения восприятия воспользуемся визуальным подходом. Предположим, у нас есть мера – Красные велосипеды, купленные женщинами:

В сводной таблице установлены фильтры по цвету, полу и году:

Ris. 24.1. Kontekst filtra svodnoj tablitsy

Рис. 24.1. Контекст фильтра сводной таблицы

Читать полностью

Глава 23. Прояснение понятий «контекст фильтра» и «контекст строки»

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую начать сначала.

Предыдущая глава       Содержание    Следующая глава

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

Ris. 23.1. Dlya vychislyaemyh stolbtsov kontekst stroki opredelyaetsya avtomaticheski

Рис. 23.1. Для вычисляемых столбцов контекст строки определяется автоматически; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Читать полностью

Глава 22. Сложные связи модели данных Power Pivot

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую начать сначала.

Предыдущая глава        Содержание    Следующая глава

В DAX связи очень важны. Но некоторые связи, как бы это сказать, сложнее, чем другие))

Несколько связей между двумя таблицами

Рассмотрим таблицы Sales и Calendar. Они связаны Calendar[Date] –> Sales[OrderDate]. Файл примера – ch22A_ComplicatedRelationships.xlsx.

Ris. 22.1. Mogut li eti dve tablitsy imet bolee odnoj svyazi

Рис. 22.1. Могут ли эти две таблицы иметь более одной связи?

Читать полностью