Функция впр пример в excel: инструкция на примере / Skillbox Media

Функция ВПР в Excel пошаговая инструкция с примерами

Функция ВПР может использоваться для поиска значения по строке в таблице в определённом массиве данных. Синтаксис нашей функции имеет следующий вид:

ВПР (искомое значение; диапазон поиска; номер столбца с входным значением; 0 (ЛОЖЬ) или 1 (ИСТИНА)).

ЛОЖЬ – точное значение, ИСТИНА – приблизительное значение.

Простейшая задача для функции ВПР. Например, у нас есть список лекарственных препаратов. Наша первая задача – найти стоимость препарата Хепилор.

В ячейке С12 начинаем писать функцию:

1. B12 – поскольку нам нужен Хепилор, выбираем ячейку с предварительно написанным названием искомого лекарства.
2. Далее выбираем диапазон данных B3:D10, где функция будет совершать поиск нужного нам значения. Крайний левый столбец диапазона должен содержать в себе искомый критерий, по которому производится поиск значения.
3. Следующий шаг – указать номер столбца в массиве B3:D10, из которого будет считана информация на одной строке с Хепилором. Столбцы нумеруются слева направо в самом диапазоне, в нашем примере первый столбец – В, но не А, поскольку А лежит вне области диапазона.

Поиск по столбце «Производитель» будет работать точно так же, нужно просто указать последовательность столбца, где находится нужная нам информация – заменяем цифру «3» в формуле (ячейка С27) на цифру «2»:

Есть определённая особенность, связанная со столбцами. Иногда в Excel-файле в таблицах некоторые ячейки объединяют. На картинке ниже в формуле на месте порядкового номера столбца у нас написана цифра «3», но результат – название производителя, а не цена, как в первом примере:

Произошел сдвиг нумерации столбцов как раз из-за наличия объединения ячеек в столбце «Лекарственное средство»: мы объединяли столбцы «H» и «I», зрительно столбец «Лекарственное средство» — это первый столбец, а «Производитель» — второй, НО формула нумерует их следующим образом:

• H – первый;
• I – второй;
• J – третий;
• K – четвертый.

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



Использование функции ВПР для работы с несколькими таблицами и другими функциями

В следующем примере рассмотрим, как ещё мы можем использовать функцию для поиска и получения информации по критериям и комбинирование функции с функцией ЕСЛИОШИБКА. Например, мы имеем два отчета – отчет о количестве товара и отчет о цене за единицу товара, которые нам необходимы для подсчета стоимости. Опять же, с небольшим количеством данных это вполне можно сделать вручную, но, когда мы имеем большой объем, справиться с этим скорее и эффективнее нам поможет функция ВПР. В ячейке D3 начинаем писать функцию:

1. B3 – критерий, по котором проводим поиск данных.
2. F3:G14 – диапазон, по котором наша функция будет осуществлять поиск совпадения критерия и данных по строке.
3. Цифра «2» — номер столбца с нужной нам информацией по критерию.
4. Цифра «0» (или можно использовать слово «ЛОЖЬ») — для точности результатов.

Таким образом, когда мы задаем формуле искомый критерий, она начинает поиск совпадений с верхней ячейки первого столбца (шаг 1 на картинке). Затем функция «читает» все критерии сверху вниз, пока не найдет точное совпадение (шаг 2). Когда ВПР дойдет до Хепилора, она отсчитает нужное количество столбцов вправо (шаг 3) и выдаст нам искомое значение для критерия – цену 86,90 (шаг 4):

Но сейчас у нас есть данные только по первому критерию. Для того чтобы заполнить третий столбец D первой таблицы до конца, нужно просто скопировать функцию до последнего критерия. Однако, на этом этапе для корректной работы диапазон, где совершается поиск, нужно закрепить, иначе массив данных «съедет» вниз и у нас ничего не получится. Для этого используем абсолютные ссылки для диапазона в ячейке D3 – выделяем курсором диапазон F3:G14 и нажимаем клавишу F4, после чего совершаем копирование формулы до конца таблицы:

В итоге мы получаем необходимый нам результат:

Однако, наш пример базировался на полном соответствии критериев с обеих таблиц – одинаковое количество товаров, одинаковые наименования. Но что, если, например, убрать последние четыре товара с отчета по ценам за упаковку? Тогда у нас будет ошибка #Н/Д в первой таблице в тех позициях, которые находятся на одной строке с искомым критерием:

Если вас не устраивает такое содержание ячеек, можно заменить значение ошибки. Для этого комбинируем функцию ВПР с функцией ЕСЛИОШИБКА. Синтаксис функции ЕСЛИОШИБКА(значение, значение_если_ошибка), таким образом значением у нас будет наша использованная функция ВПР, а значением если ошибка – то, что мы хотим видеть вместо #Н/Д, например, прочерк, но обязательно взятый в кавычки:

В результате мы получим красиво оформленную таблицу с надлежащим видом:

Использование приблизительного значения

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

Как видим, размер премии зависит от того диапазона по системе премирования, куда попал показатель выполнения продаж конкретного сотрудника. Мы видим, что если план выполнен менее, чем на 100% — премия не присваивается, а если на 107% (выше 100%, но меньше 110%), тогда сотрудник получает премию размером 10%. Описанные показатели премии нам нужно вписать с помощью функции ВПР в столбец «Премия» первой таблицы, только на этот раз критерий будет находиться в определённом диапазоне.

Для корректной работы нужно убедиться, что границы диапазонов во второй таблице крайнего левого столбца размещены по возрастанию сверху вниз (шаг 1). Формула берёт выбранный нами критерий и осуществляет поиск в первом столбце второй таблицы (шаг 2), просматривая все значения сверху вниз (шаг 3). Как только функция находит первое значение, которое превышает критерий с первой таблицы, делает «шаг назад» (шаг 4) и считывает значение, которое соответствует найденому критерию (шаг 5). Иными словами, при неточном поиске функция ВПР ищет меньшее значение для искомого критерия:

Таким образом, наша функция будет выглядеть так:

И результат использования функции ВПР с приблизительным поиском имеет вот такой результат:

Скачать все пошаговые примеры функции ВПР в Excel

Например, сотрудник Ольга имеет премию размером 0%, поскольку она выполнила 76% продаж, тоесть перевыполнила план на 0%. А сотрудник Наталья совершила продажи на 21% выше нормы и была премирована на 20%, что мы и видим, если сравнить самостоятельно данные с двух таблиц.

На этих примерах применение функции ВПР не заканчивается, есть много других задач, с которыми удобно справляться этой функцией. Она облегчает работу с большим массивом данных, минимизирует ошибки сравнительно с самостоятельными расчетами, проста в понимании и применении.

Функция ВПР

 

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

Самая простая функция ВПР означает следующее:

=ВПР(искомое значение; диапазон для поиска значения; номер столбца в диапазоне с возвращаемым значением; точное или приблизительное совпадение — указывается как 0/ЛОЖЬ или 1/ИСТИНА).

Видео инструкция:

СОВЕТ : Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (номер детали) отображалось слева от возвращаемого значения, которое нужно найти (цена детали).

Технические подробности

Начало работы

Для построения синтаксиса функции ВПР вам потребуется следующая информация:

1. Значение, которое вам нужно найти, то есть искомое значение.
2. Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
3. Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона вы указываете B2:D11, следует считать B первым столбцом, C — вторым и т. д.
4. При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

Теперь объедините все перечисленное выше аргументы следующим образом:

=ВПР(искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением; при желании укажите ИСТИНА для поиска приблизительного или ЛОЖЬ для поиска точного совпадения).

На рисунке ниже показано, как настроить функцию ВПР, чтобы получить цену тормозных дисков, равную 85,73.

1. В ячейке D13 указано искомое_значение, т. е. значение, которое вам нужно найти.
2. Ячейки B2:E11 (выделены в таблице желтым цветом) представляют собой таблицу или диапазон, в котором находится искомое значение.
3. 3 — это номер_столбца в таблице, которая содержит возвращаемое значение. В приведенном примере третьим столбцом в является столбец Цена детали, поэтому результатом формулы будет значение из этого столбца.
4. Для аргумента интервальный_просмотр указано значение ЛОЖЬ , поэтому возвращаемым значением будет точное совпадение.
5. Результатом формулы ВПР является цена тормозных дисков, равная 85,73.

Примеры

Здесь приведены еще несколько примеров использования функции ВПР.

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

Функция ВПР.

Использование функции ВПР. Excel

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

Одна из известных формул Excel — вертикальный просмотр. Использование функции VLOOK на первый взгляд кажется довольно сложным, но это только на первый взгляд.

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

При работе с формулой VLR следует учитывать, что она ищет искомое значение только по столбцам, а не по строкам. Для использования функции требуется минимальное количество столбцов — два, максимальное отсутствует.

Функция ВПР ищет в таблице заданный критерий, который может иметь любой формат (текстовый, числовой, денежный, по дате и времени и т.д.). В случае нахождения записи выводит (подставляет) значение, введенное в той же строке, но из нужного столбца таблицы, то есть соответствующее заданному критерию. Если искомое значение не найдено, то выводится ошибка #N/A (в английской версии #N/A).

Необходимо использовать

Функция ВПР приходит на помощь оператору, когда требуется быстро найти и применить в дальнейшем расчете, анализе или прогнозе определенное значение из таблицы больших размеров. Главное при использовании этой формулы – убедиться, что указанная область поиска выбрана правильно. В него должны быть включены все записи, то есть от первой до последней.

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

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

В результате работы функции ВПР создается новая таблица, в которой конкретный разыскиваемый менеджер быстро сравнивает свои суммы продаж.

Алгоритм заполнения формулы

Формула VLOOK находится на вкладке «Мастер функций» и в разделе «Ссылки и массивы». Диалог функции имеет следующий вид:

Аргументы в формулу вводятся в порядке очереди:

• Значение поиска — это то, что должна найти функция, а варианты — значения ячейки, ее адрес, имя, данное ему оператором. В нашем случае это фамилия и имя менеджера.
• Таблица — диапазон строк и столбцов, в которых ищется критерий.
• Номер столбца – это его порядковый номер, в котором находится сумма продаж, то есть результат работы формулы.
• Интервальный вид. Он содержит значение FALSE или TRUE. Причем FALSE возвращает только точное совпадение, TRUE — позволяет искать приблизительное значение.

Пример использования функции

Функция ВПР может иметь пример использования следующий: при ведении деятельности торгового предприятия в таблицах Excel в столбце А записывается наименование товара, а в столбце Б — соответствующая цена. Чтобы написать предложение в колонке C, вам нужно найти стоимость определенного товара, который вы хотите вывести в колонке D.

Хороший пример организации стола

А	В	С	Д
товар 1 9005 3	90	продукт 3	60
продукт 2	120	продукт 1	90
продукт 3	60	продукт 4	100
продукт 4	100	продукт 2	120

Формула, записанная на D, будет иметь вид: =VLP(C1,A1:B5;2;0), то есть =VLOOK(целевое значение, диапазон данных таблицы, порядковый номер столбца, 0). В качестве четвертого аргумента вместо 0 можно использовать ЛОЖЬ.

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

Зафиксировать рабочую область данных можно с помощью абсолютных ссылок. Для этого знаки $ ставятся вручную перед буквенными и числовыми значениями адресов крайних левой и правой ячеек таблицы. В нашем случае формула принимает вид: =ВПР(С1;$А$1:$В$5;2;0).

Ошибки при использовании

Функция ВПР не работает, после чего в столбце вывода результата ошибки появляется сообщение (# Н/Д или # Н/Д). Это бывает в таких случаях:

1. Формула введена, а столбец требуемых критериев не заполнен (в данном случае столбец С).
2. Столбец C имеет значение, которое отсутствует в столбце A (в диапазоне поиска данных). Чтобы проверить наличие нужного значения, выберите столбец критериев и на вкладке меню «Правка» — «Найти» вставьте эту запись, запустите поиск. Если программа его не находит, значит он отсутствует.
3. Форматы ячеек столбцов А и С (необходимые критерии) разные, например, один — текстовый, а другой — числовой. Вы можете изменить формат ячейки, если перейдете к редактированию ячейки (F2). Такие проблемы обычно возникают при импорте данных из других приложений. Во избежание подобных ошибок в формулу VLOOK можно встроить следующие функции: KEY или TEXT. Выполнение этих алгоритмов автоматически преобразует формат ячеек.
4. В коде функции есть непечатаемые символы или пробелы. Затем следует внимательно проверить формулу на наличие ошибок ввода.
5. Задан приблизительный поиск, то есть четвертый аргумент функции ВПР имеет значение 1 или ИСТИНА, а таблица не отсортирована по возрастанию. В этом случае столбец искомых критериев должен быть отсортирован по возрастанию.

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

Особенности использования в качестве интервального просмотра 1 или TRUE

Ошибка под номером 5 довольно распространена и наглядно показана на рисунке ниже.

В данном примере список имен согласно нумерации отсортирован не по возрастанию, а по убыванию. А в качестве интервального просмотра использовался критерий ИСТИНА (1), который сразу прерывает поиск при нахождении значения большего, чем искомое, поэтому выдается ошибка.

При применении 1 или TRUE в четвертом аргументе необходимо убедиться, что столбец с требуемыми критериями отсортирован в порядке возрастания. При использовании 0 или FID эта необходимость отпадает, но также отсутствует возможность интервального сканирования.

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

Другие нюансы при работе с функцией ВПР

Для удобства работы с такой формулой можно озаглавить диапазон таблицы, в которой производится поиск (второй аргумент), как показано на рисунке.

В этом случае область стола продаж имеет право. Для этого выбирается таблица, за исключением заголовков столбцов, и именуется поле имени (слева под панелью вкладок).

Еще один вариант — заголовок — подразумевает выделение диапазона данных, затем переход в меню «Вставка» — «Имя» — «Назначить».

Для того, чтобы использовать данные, размещенные на другом листе рабочей книги, с помощью функции ВПР необходимо во втором аргументе формулы указать расположение диапазона данных. Например, =ВПР(А1;Список2!$А$1:$В$5;2;0), где Лист2! — это ссылка на нужный лист книги, а $A$1:$In$5 — адрес диапазона извлечения данных.

Пример организации учебного процесса с ВПР

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

Есть две таблицы со списками учеников. Один со своими оценками, второй указывает на возраст. Необходимо сопоставить обе таблицы, чтобы наряду с возрастом учащихся были выведены их оценки, то есть ввести дополнительный столбец во второй список.

Функция VLOOK отлично справляется с решением этой задачи. В столбце Г под заголовком «Оценки» записывается соответствующая формула: = ВПР(Э4, В3: С13, 2, 0). Его необходимо скопировать на весь столбец таблицы.

В результате функция VLR выдаст оценки, полученные некоторыми учащимися.

Пример организации поисковой системы с ВПР

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

С помощью ВПР создается новая таблица, в которой легко найти вид животного по названию животного. Эти поисковики актуальны при работе с большими списками. Чтобы не просматривать вручную все записи, можно быстро воспользоваться поиском и получить нужный результат.

Создание связи между таблицами в Excel

Использовали ли вы когда-нибудь функцию ВПР для переноса столбца из одной таблицы в другую? Теперь, когда в Excel есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами данных на основе сопоставления данных в каждой таблице. Затем вы можете создавать листы Power View и создавать сводные таблицы и другие отчеты с полями из каждой таблицы, даже если таблицы взяты из разных источников. Например, если у вас есть данные о продажах клиентов, вы можете захотеть импортировать и связать данные аналитики времени, чтобы анализировать модели продаж по годам и месяцам.

Все таблицы в книге перечислены в списках полей сводной таблицы и Power View.

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

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

2. Выполните одно из следующих действий: отформатируйте данные в виде таблицы или импортируйте внешние данные в виде таблицы на новый лист.

3. Дайте каждой таблице значимое имя: В Инструменты для работы с таблицами щелкните Дизайн > Имя таблицы > введите имя.

4. Убедитесь, что столбец в одной из таблиц имеет уникальные значения данных без дубликатов. Excel может создать связь только в том случае, если один столбец содержит уникальные значения.

   Например, чтобы связать продажи клиентов с аналитикой времени, обе таблицы должны включать даты в одном и том же формате (например, 01. 01.2012), и по крайней мере в одной таблице (аналитика времени) каждая дата указана в столбце только один раз.

5. Нажмите Данные > Отношения .

Если Отношения неактивны, ваша рабочая книга содержит только одну таблицу.

1. В поле Управление связями щелкните Новый .

2. В поле Создать связь щелкните стрелку Таблица и выберите таблицу из списка. В отношении «один ко многим» эта таблица должна быть на стороне многих. Используя наш пример с данными о клиентах и ​​времени, вы должны сначала выбрать таблицу продаж клиентов, потому что многие продажи, вероятно, будут иметь место в любой день.

3. Для столбца (внешнего) выберите столбец, содержащий данные, связанные с Связанный столбец (первичный) . Например, если бы у вас был столбец даты в обеих таблицах, вы бы выбрали этот столбец сейчас.

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

5. Для Связанный столбец (первичный) выберите столбец с уникальными значениями, совпадающими со значениями в столбце, который вы выбрали для Столбец .

6. org/ListItem»>

   Нажмите OK .

Подробнее об отношениях между таблицами в Excel

• Примечания об отношениях

• Пример: привязка данных логики времени к данным о рейсах авиакомпаний

• «Могут потребоваться связи между таблицами»

  • Шаг 1. Определите, какие таблицы следует указать в отношении

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

Примечания о взаимоотношениях

• org/ListItem»>

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

• Создание связей похоже на использование ВПР: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel мог сопоставлять строки в одной таблице со строками в другой таблице. В примере с логикой операций со временем таблица Customer должна иметь значения дат, которые также существуют в таблице логики операций со временем.

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

• Типы данных в двух столбцах должны быть совместимы. Дополнительные сведения см. в разделе Типы данных в моделях данных Excel.

• Другие способы создания отношений могут быть более интуитивно понятными, особенно если вы не уверены, какие столбцы использовать. См. раздел Создание связи в представлении схемы в Power Pivot.

Пример: привязка данных временной аналитики к данным о рейсах авиакомпании

Вы можете узнать как об отношениях между таблицами, так и об аналитике времени, используя бесплатные данные в Microsoft Azure Marketplace. Некоторые из этих наборов данных очень велики и требуют быстрого подключения к Интернету для завершения загрузки данных за разумный период времени.

1. Запустите Power Pivot в надстройке Microsoft Excel и откройте окно Power Pivot.

2. Щелкните Получить внешние данные > Из службы данных > Из Microsoft Azure Marketplace . Домашняя страница Microsoft Azure Marketplace открывается в мастере импорта таблиц.

3. Под Цена , нажмите Бесплатно .

4. В категории щелкните Наука и статистика .

5. Найдите DateStream и нажмите Подписаться .

6. Войдите в свою учетную запись Microsoft и нажмите Войти . В окне должен появиться предварительный просмотр данных.

7. Прокрутите вниз и нажмите Выберите запрос .

8. Щелкните Далее .

9. Выберите BasicCalendarUS , а затем нажмите Готово , чтобы импортировать данные. При быстром интернет-соединении импорт займет около минуты. Когда закончите, вы должны увидеть отчет о состоянии 73 414 переданных строк. Щелкните Закрыть .

10. Нажмите Получить внешние данные > Из службы данных > Из Microsoft Azure Marketplace для импорта второго набора данных.

11. Под Введите , щелкните Данные .

12. Под Цена , нажмите Бесплатно .

13. Найдите Задержки рейсов авиаперевозчика США и нажмите Выберите .

14. Прокрутите вниз и нажмите Выберите запрос .

15. Щелкните Далее .

16. Щелкните Готово , чтобы импортировать данные. При быстром подключении к Интернету импорт может занять 15 минут. По завершении вы должны увидеть отчет о состоянии 2 427 284 переданных строк. Нажмите Закрыть . Теперь у вас должно быть две таблицы в модели данных. Чтобы связать их, нам понадобятся совместимые столбцы в каждой таблице.

17. Обратите внимание, что DateKey в BasicCalendarUS имеет формат 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени, FlightDate , значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные того же типа данных, и по крайней мере один из столбцов ( DateKey ) содержит только уникальные значения. В следующих нескольких шагах вы будете использовать эти столбцы для связи таблиц.

18. В окне Power Pivot щелкните Сводная таблица , чтобы создать сводную таблицу на новом или существующем листе.

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

20. Разверните BasicCalendarUS и щелкните MonthInCalendar , чтобы добавить его в область строк.

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

22. org/ListItem»>

    В списке полей в разделе «Могут потребоваться связи между таблицами» нажмите Создать .

23. В связанной таблице выберите On_Time_Performance и в связанном столбце (первичном) выберите FlightDate .

24. В таблице выберите BasicCalendarUS , а в столбце (иностранный) выберите DateKey . Нажмите OK , чтобы создать связь.

25. Обратите внимание, что сумма задержек в минутах теперь меняется для каждого месяца.

26. В BasicCalendarUS и перетащите YearKey в область строк над MonthInCalendar .

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

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

1. Убедитесь, что таблица BasicCalendarUS открыта в окне Power Pivot.

2. В домашней таблице нажмите Сортировать по столбцу .

3. В Sort выберите MonthInCalendar

4. org/ListItem»>

   В By выберите МесяцГода .

Сводная таблица теперь сортирует каждую комбинацию месяц-год (октябрь 2011 г., ноябрь 2011 г.) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, поскольку фид DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу аналитики времени, ваш шаг будет другим.

«Могут потребоваться связи между таблицами»

При добавлении полей в сводную таблицу вы будете проинформированы о том, требуется ли связь между таблицами для понимания полей, выбранных вами в сводной таблице.

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

Шаг 1: Определите, какие таблицы указать в отношении

Если ваша модель содержит всего несколько таблиц, может быть сразу очевидно, какие из них вам нужно использовать. Но для более крупных моделей вам, вероятно, может понадобиться помощь. Один из подходов — использовать представление схемы в надстройке Power Pivot. Представление диаграммы обеспечивает визуальное представление всех таблиц в модели данных. Используя представление диаграммы, вы можете быстро определить, какие таблицы отделены от остальной части модели.

Примечание.  Возможно создание неоднозначных отношений, которые недопустимы при использовании в сводной таблице или отчете Power View. Предположим, что все ваши таблицы каким-то образом связаны с другими таблицами в модели, но при попытке объединить поля из разных таблиц вы получаете сообщение «Могут потребоваться связи между таблицами». Наиболее вероятная причина заключается в том, что вы столкнулись с отношениями «многие ко многим». Если вы проследите цепочку отношений между таблицами, которые связаны с таблицами, которые вы хотите использовать, вы, вероятно, обнаружите, что у вас есть две или более связи между таблицами типа «один ко многим». Не существует простого обходного пути, подходящего для любой ситуации, но вы можете попробовать создать вычисляемые столбцы, чтобы объединить столбцы, которые вы хотите использовать, в одну таблицу.

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

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

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

Помимо сопоставления значений, есть несколько дополнительных требований для создания отношения:

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