usto-excel

Сортировать данные в MS Excel можно не только по значению или настраиваемому списку но и по цвету.

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

Выделяем диапазон с данными для сортировки. На вкладке Главная выбираем Сортировка и фильтр -> Настраиваемая сортировка. В появившемся окне Сортировка, в поле Столбец выбираем Сумма продаж, в поле Сортировка - Цвет ячейки, в поле Порядок выбираем зелёный цвет и из выпадающего списка выбираем значение Сверху.

Далее жмём кнопку Добавить уровень и точно также заполняем поля, только в поле Сортировка выбираем Цвет шрифта, а в поле Порядок - красный цвет и значение Снизу.



Жмём ОК и получаем отсортированный по цвету список. Профит.

Одной из часто встречаемых задач в MS Excel является поиск расхождений между двумя столбцами данных.
Зачастую, мне приходится наблюдать как пользователи пишут формулы (типа =А1=В1) для того чтобы найти эти расхождения. Хотя для этого можно воспользоваться гораздо более простым и удобным способом.
Прежде всего, выделяем столбцы с данными, которые нужно сверить.

После, на вкладке Главная выбираем Найти и выделить->Выделить группу ячеек... Откроется окно Выделить группу ячеек, в котором выбираем пункт отличия по строкам (главное не перепутайте - когда сравниваете между собою столбцы ищите отличия по строкам).

Жмём ОК и закрашиваем выделенные ячейки.


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

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

1. Центральный офис;
2. Северное подразделение;
3. Южное подразделение;
4. Западное подразделение;
5. Восточное подразделение.

Уверен, многие из Вас знают, что если в какой-нибудь ячейке написать к примеру "Январь" и потянуть за маркер заполнения вниз, то MS Excel услужливо заполнит все последующие ячейки названиями остальных месяцев.


Согласитесь, было бы замечательно, если бы MS Excel умел автоматически заполнять ячейки не только названиями дней недели или месяцев, но и любыми другими списками.
Мало кто знает, но мы действительно можем научить MS Excel новым спискам значений.

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

• Продукты питания;
• Бытовая химия;
• Канцелярские принадлежности;
• Детские игрушки;
• Мобильные устройства;
• Бытовая техника.

Файл примера


В прошлой статье мы узнали о том как создавать сквозные формулы для суммирования показателей продаж сразу из нескольких листов книги. Эта формула суммирует значения ячеек "С8" на листах "Регион 1", "Регион 2" и на всех листах находящихся между ними. То есть "Регион 1" выступает в качестве верхней границы формулы а "Регион 2" в качестве нижней границы.

Следовательно, если мы добавим в книгу новый лист, например Регион 4 то значения из этого листа не будут учтены нашей формулой, поскольку находятся за пределами её нижней границы.


Мы можем поменять местами листы "Регион 4" и "Регион 3" и тогда значения из листа "Регион 4" будут учитываться нашей формулой, то есть лист "Регион 4" окажется в пределах действия формулы.


Но что делать если начальство требует, чтобы регионы в книге располагались по порядку их открытия. Или же если Вы создаёте книгу с формулами для использования другими сотрудниками и не можете надеяться на то, что они не забудут поменять местами листы?

Чтобы решить данную проблему:
      1. Создаём пустой лист с именем "Конец" ну или любым другим понятным Вам именем;
      2. Изменяем формулу и в качестве её нижней границы указываем созданный нами лист;
      3. Кликнув правой кнопкой мышки на названии листа выбираем команду "Скрыть".

Файл примера

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


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


Очень часто мне приходится наблюдать как мои коллеги в подобной ситуации ставят знак "=",  добавляют ссылку к первому листу, ставят знак "+", добавляют ссылку ко второму листу, и так далее. А теперь представьте, что в этой книге не 3 а 10, 50 или 100 листов с данными, которые нужно просуммировать.

Скачать файл-примера



Как Вы могли убедиться из моих предыдущих постов, меры являются мощными инструментами анализа данных и позволяют производить немыслимые до этого виды расчётов. Однако, до сих пор при знакомствами с мерами мы использовали только одну таблицу t_sales. Но вся прелесть Power Pivot в том, что с его помощью можно производить расчёты, комбинируя данные из нескольких таблиц. По моему личному мнению, если бы даже Powe Pivot не имел встроенного движка функций DAX, одна только способность связывания таблиц, уже оправдывала бы его существование.

Создание связей

Так как же создаются связи между таблицами? Всё очень просто. Заходим в окно Power Pivot и в правом нижнем углу, нажимаем на иконку со всплывающей надписью "Диаграмма".


Либо по кнопке "Представление диаграммы" на вкладке "Главная".


Откроется окно представления диаграммы, в котором все таблицы нашей модели данных показаны в виде отдельных окошек, со списком колонок.


Как видим, эти таблицы между собою пока никак не связаны. Чтобы создать между ними связь, нам сначала нужно определить идентичные колонки. Идентичными колонками, называют колонки содержащие одинаковые данные. К примеру, и в таблице t_sales и в таблице t_products есть колонки КодПродукта, содержащие одинаковые данные (при этом не обязательно, чтобы названия колонок в обоих таблицах были одинаковыми). Свяжем эти две таблицы между собою кликнув по названию колонки КодПродукта в t_sales и удерживая левую кнопку мыши нажатой, перетащим эту колонку к другой колонке КодПродукта в t_products.


Точно также, связь между таблицами можно создать через команду "Создание связи" на вкладке "Конструктор".


Но можно ли создавать связь между таблицами по любым идентичным столбцам? Например столбцы "ЦенаЗаШтуку" в t_sales и "Цена" в t_products содержат одинаковые данные. Попробуем создать между ними связь путём перетаскивания. Power Pivot выдаст ошибку: "Не удалось создать связь, поскольку в каждом столбце содержатся повторяющиеся значения. Выберите по крайней мере один столбец, содержащий только уникальные значения."


То есть для того, чтобы установить связь между таблицами, один из связывающих столбцов должен содержать только уникальные, не повторяющиеся значения. К примеру, цена у нескольких продуктов может быть одинаковой (повторяться), поэтому использовать эти столбцы для создания связи между таблицами не получится. А вот "КодПродукта" в t_products содержит только уникальные значения, поэтому мы и смогли использовать его для создания связи.


Таблицы, содержащие столбцы с уникальными значениями, по которым устанавливается связывание, называются "таблицами поиска" (lookup tables).

Ниже представлена сводная таблица на основе данных таблицы t_sales.

Как это работает

Далее, коды продуктов соответствующие цвету "Red" через установленную связь, из таблицы поиска (t_products) передаются основной таблице (t_sales), которая на основании полученных данных применяет фильтры к столбцу КодПродукта.

ВАЖНО!

Фильтры, применённые к таблице поиска передаются через установленную связь основной таблице. 

Однако, фильтры применённые к основной таблице, не передаются таблице поиска. 

Функция CALCULATE () и связанные таблицы

Давайте создадим ещё одну связь между таблицами. Свяжем таблицу t_sales с таблицей t_clients.


В таблице t_sales, в столбце КоличествоДетейНаПопечении, имеется информация о том сколько отпрысков находятся на попечении каждого клиента. Создадим меру, которая бы на основании этих данных, рассчитывала сумму продаж клиентам с детьми:

                     [ПродажиРодителям]=
                     CALCULATE([ИтогоПродаж],
                     t_clients[КоличествоДетейНаПопечении]>0)

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

Данный процесс набора однотипных комбинаций слов можно автоматизировать с помощью встроенного в MS Excel функционала автозамены.

Для этого заходим в Файл→Параметры→Правописание→Параметры автозамены. Далее на вкладке "Автозамена" в поле "заменять:" вводим любой набор символов (например *ооо) а в поле "на:" пишем комбинацию слов ввод которых нужно автоматизировать (например Общество с Ограниченной Ответственностью "Рога и Копыта").


Более того, теперь Вы можете использовать указанный набор символов (*ооо) для автоматического ввода комбинации слов и во всех других приложениях MS Office.

Уверен, что многим из Вас приходилось загружать данные в Excel из текстовых файлов. Более того, уверен что многие, не любят заниматься этим, также как и я. Ситуация ещё более усугубляется, если числовые данные в текстовом файле записаны в английской нотации, то есть в качестве десятичного разделителя использована точка а запятая играет роль разделителя разрядов.


Большинство в такой ситуации, после импорта в Excel, выделяют столбцы с числовыми данными и с помощью команды Найти-Заменить сначала убирают разделители разрядов а потом меняют точки на запятые. Но данный метод не всегда гарантирует получение нужных результатов. Например, содержащееся в нашем текстовом файле число 4.50 при импорте в Excel автоматически превращается в дату, и командой Найти-Заменить это уже не исправить.


К счастью, проблема загрузки числовых данных из текстового файла довольно легко решаема. Для этого, в 3 шаге Мастера импорта, нужно кликнуть по кнопке Подробнее.



Далее, в появившемся окне Дополнительная настройка импорта указать какой символ в текстовом файле используется в качестве десятичного разделителя (в нашем примере точка) и какой в качестве разделителя разрядов (в нашем примере запятая). Жмём ОК и Готово и числовые данные загружаются уже в "правильной" нотации.

ЗАДАЧА

Дана таблица (файл книги с примером) следующего вида:



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

РЕШЕНИЕ

=МИН(121;289;165)

Но как получить этот список значений из для любого, выбранного пользователем товара?
Для этого воспользуемся связкой функций ИНДЕКС()+ПОИСПОЗ().

Чтобы понять как работает эта формула, выделем её в строке формул и будем вычислять по частям с помощью клавиши F9.
Прежде всего, обратите внимание на следующую часть формулы:

ПОИСКПОЗ(G3;B3:B10;0)

Функция ПОИСКПОЗ() находит позицию указанного пользователем товара (ячейка G3) в столбце "Код товара" в основной таблице (диапазон B3:B10). Выделим данную часть формулы и нажмём F9:

Формула вычислила позицию товара в столбце "Код товара". То есть товар с кодом "А6" находится в шестой строке столбца "Код товара".
Перейдём к следующей части формулы:

ИНДЕКС(C3:E10;6;0)

В этой части скрыт наш главный секрет. Дело в том, что функция ИНДЕКС() возвращает значения находящиеся на пересечении указанных строк и столбцов. Так если бы вместо нуля мы указали цифру 1, то формула вернула бы нам значение находящееся на пересечении 6 строки (товар А6) и 1 столбца (Поставщик1) в диапазоне C3:E10. Но так как мы не указываем какой именно столбец нам нужен, то функция ИНДЕКС() возвращает нам значения всех ячеек указанной строки.