Excel подстановка значений из другой таблицы

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

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

A2 – значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);

$E$1:$F$7 – полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок «$» – он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;

2 – номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца – то значение можно было бы копировать из 2-го или 3-го столбца);

ЛОЖЬ – ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).

Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!

Чтобы цена была проставлена и для других наименований товара – просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.

Растягиваем формулу (копируем формулу в другие ячейки)

После чего, как видите, первые столбцы у таблиц будут сравнены: из строк, где значения ячеек совпали – будут скопированы и подставлены нужные данные. В общем-то, понятно, что таблицы могут быть гораздо больше!

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

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

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

Аргументы «Просматриваемый_вектор» и «Вектор_результата» можно записать в форме массива – в этом случае не придется выводить их в отдельную таблицу на листе Excel.
В этом случае функция будет выглядеть следующим образом:
Вывод результата в ячейку B3:
=ПРОСМОТР(E3;;)

Вопрос 6. работа с таблицей как с базой данных — Информатика, информационные технологии

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

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

Выполнение операций со списками (базами данных) в табличном процессоре Excel

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

Название столбца при работе с базой данных называется именем поля.

1. Имя поля (название столбца) должно занимать ровно одну ячейку.

2. Имена полей должны занимать верхнюю строку списка.

3. Между именами полей и данными не должно быть пустых строк.

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

Сортировка данных в списке – это расположение данных в определенном порядке. Сортировать данные списка в Excel можно по одному, двум или трем полям.

Рассмотрим базу данных Студенты вида:

Например, если поля для сортировки выбраны так: 1. Номер группы; 2. Год рождения; 3. Фамилия; то данные сначала будут отсортированы по номеру группы; внутри группы по году рождения и, наконец, при одинаковых номере группы и годе рождения, по фамилиям в алфавитном порядке. Результат такой сортировки приведен ниже:

Логическое И означает требование одновременного выполнения всех заданных условий, логическое ИЛИ означает требование выполнения хотя бы одного из условий.

Рассмотрим таблицу базы данных Студенты вида:

Задание 1. Пусть необходимо из списка Студенты п. 3.3 выбрать студентов 12-й группы, получающих стипендию 800 рублей и более. Ход выполнения работы:

Выбрать пункт меню Данные – Фильтр – Автофильтр. В заголовках столбцов списка появятся кнопки раскрывающегося списка ( ).

В столбце Группа щелкнем по такой кнопке и выберем из списка 12.Фильтрация по данному полю сразу будет произведена.

Примечание. Отменить результаты фильтрации данных в таблице можно, щелкнув и выбрав Показать все.

Задание 2. Пусть из списка Студенты п. 3.3 необходимо выбрать студентов 11-й и 12-й групп 1987-го года рождения.

Предварительно отменив результаты предыдущей фильтрации, выполним следующее:

Пояснение: условия – студенты 11-й и 12-й групп – связаны логическим ИЛИ (а не логическим И), т.к. номер группы 11 или 12, а не 11 и 12 одновременно.

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

Фильтр покажет записи, удовлетворяющие таким условиям.

3.3.2. Фильтрация данных с помощью расширенного фильтра

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

ПРАВИЛА формирования диапазона условий для расширенного фильтра:

1. Между ячейками данных списка и ячейками диапазона условий должна быть хотя бы однапустая строка или столбец.
2. Для формирования диапазона условий необходимо скопировать заголовки столбцов, для которых задаются условия выборки (можно скопировать заголовки всех столбцов, но под заголовками тех столбцов, для которых условия фильтрации не задаются, ничего не записывается, т.е. ячейки остаются пустыми).
3. Под каждым заголовком с помощью знаков операций сравнения (>, =) записывается условие фильтрации по данному полю. В одной ячейке записывается только одно условие.
4. Если условия фильтрации связаны логическим И, ячейки, в которые записываются эти условия, располагают в одной строке, а если условия связаны логическимИЛИ, они должны быть расположены в разных строках.

Задание 1. Пусть из базы данных Студенты п. 3.3 необходимо выбрать студентов 11-й и 12-й групп 1987-го года рождения.

Для формирования диапазона условий (правило № 2) в ячейки А12 и В12 копируем заголовки нужных нам столбцов – группаи год рождения соответственно. 11-ю строку оставляем пустой (правило № 1).

Теперь запишем условия для отбора студентов 12-й группы. С условиями для студентов 11-й группы они связаны логическим ИЛИ, поэтому должны быть записаны в другой строке (правило № 4). В 14-й строке записываем условия: для столбцагруппа (ячейка А14) – 12, а для столбца год рождения(ячейка В14 ) – 1987.

Диапазон условий сформирован:

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

Выполняем команду меню Данные – Фильтр – Расширенный фильтр.

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

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

Устанавливаем курсор в строке Диапазон условий и мышью в окне таблицы выделяем блок ячеек А12:В14 (или А12:В13 , если выбран второй вариант записи условия для номера группы), в которые мы записали условия фильтрации.

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

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

Нажать ОК, и список будет отфильтрован в соответствии с заданными условиями.

Примечание. Отменить результаты фильтрации данных в таблице можно, выполнив команду меню Данные – Фильтр — Показать все.

Задание 2. Пусть из базы данных Студенты п. 3.3 нужно выбрать студентов 11-й и 12-й групп, получающих стипендию в размере от 500 до 800 рублей включительно.

Для формирования диапазона условий в ячейки А12 и В12 копируем заголовки столбцов группа и стипендия соответственно (правило № 2). 11-ю строку оставляем пустой (правило № 1).

Аналогичным образом записываем условия для отбора студентов 12-й группы, которые связаны логическим ИЛИ с условиями для студентов 11-й группы, поэтому (правило № 4) должны быть записаны в другой строке (в нашем случае 14-й).

Диапазон условий сформирован:

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

Выполняем команду меню Данные – Фильтр – Расширенный фильтр,указываем Исходный диапазон(А1:F10)и Диапазон условий(А12:С14 или А12:С13). Нажимаем ОК. Фильтрация по заданным условиям будет выполнена.

Задание 3. Пусть из приведенного списка необходимо выбрать студентов 11-й группы:

В остальном ход выполнения работы аналогичен рассмотренному в Задании 1.

3.3.3. Использование в расширенном фильтре вычисляемого критерия

Вычисляемый критерий представляет собой формулу, записанную в строке области условий, которая может принимать одно из логических значений ИСТИНА или ЛОЖЬ. Формула строится с использованием адресов ячеек, встроенных функций, математических знаков операций и знаков сравнения.

ПРАВИЛО № 5: Если в расширенном фильтре используется вычисляемый критерия сравнения, то имя столбца, содержащего формулу вычисляемого критерия, должно обязательно отличаться от названия столбца в списке.

Задание. Пусть необходимо из базы данных Студенты п. 3.3 выбрать студентов 11-й и 12-й групп, стипендия которых больше средней.

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

Скопируем названия столбцов (правило № 2), по которым будут записываться условия отбора (это столбцы Группа и Стипендия) в ячейки А12 и В12 соответственно.

Записываем условия по выбору номера группы: в ячейку А13 (под заголовком Группа) записываем 11, а в ячейку А14 записываем 12(по правилу № 4).

Примечание: после завершения формирования вычисляемого критерия в ячейках будет отображен результат вычисления: ИСТИНА или ЛОЖЬ, а саму формулу критерия можно просмотреть лишь в строке формул.

Выполняем команду меню Данные – Фильтр – Расширенный фильтр,указываем Исходный диапазон(А1:F10)и Диапазон условий(А12:В14). Нажимаем ОК. Фильтрация по заданным условиям будет выполнена.

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

Критерий шапиро уилка в excel — IT Новости из мира ПК

Поиск выдал мне всего одну статью на Хабре по схожей тематике — «Талмуд по формулам в Google SpreadSheet». В ней дано хорошее описание базовых вещей для работы в excel (хотя он и не 100% про сам excel).

Диапазон условий сформирован:

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

Выполнение выборки

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

Способ 1: применение расширенного автофильтра

Наиболее простым способом произвести отбор является применение расширенного автофильтра. Рассмотрим, как это сделать на конкретном примере.

1. Выделяем область на листе, среди данных которой нужно произвести выборку. Во вкладке «Главная» щелкаем по кнопке «Сортировка и фильтр». Она размещается в блоке настроек «Редактирование». В открывшемся после этого списка выполняем щелчок по кнопке «Фильтр».

Есть возможность поступить и по-другому. Для этого после выделения области на листе перемещаемся во вкладку «Данные». Щелкаем по кнопке «Фильтр», которая размещена на ленте в группе «Сортировка и фильтр».

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

Давайте в качестве примера зададим условие так, чтобы отобрать только значения, по которым сумма выручки превышает 10000 рублей. Устанавливаем переключатель в позицию «Больше». В правое поле вписываем значение «10000». Чтобы произвести выполнение действия, щелкаем по кнопке «OK».

Как видим, после фильтрации остались только строчки, в которых сумма выручки превышает 10000 рублей.

Теперь в таблице остались только строчки, в которых сумма выручки не меньше 10000 рублей, но не превышает 15000 рублей.

Как видим, наш список ещё больше сократился. Теперь в нем оставлены только строчки, в которых сумма выручки варьируется от 10000 до 15000 рублей за период с 04.05 по 06.05.2016 включительно.

Мы можем сбросить фильтрацию в одном из столбцов. Сделаем это для значений выручки. Кликаем по значку автофильтра в соответствующем столбце. В выпадающем списке щелкаем по пункту «Удалить фильтр».

Как видим, после этих действий, выборка по сумме выручки будет отключена, а останется только отбор по датам (с 04.05.2016 по 06.05.2016).

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

Кликаем по значку фильтра в наименовании столбца. Последовательно переходим по наименованиям списка «Текстовые фильтры» и «Настраиваемый фильтр…».

Как видим, в новой выборке существуют ограничения по дате (с 04.05.2016 по 06.05.2016) и по наименованию (картофель и мясо). По сумме выручки ограничений нет.

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

Второй вариант предполагает переход во вкладку «Главная». Там выполняем щелчок на ленте по кнопке «Сортировка и фильтр» в блоке «Редактирование». В активировавшемся списке нажимаем на кнопку «Фильтр».

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

Способ 2: применение формулы массива

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

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

Естественно, в каждом конкретном случае адрес ячеек и диапазонов будет свой. На данном примере можно сопоставить формулу с координатами на иллюстрации и приспособить её для своих нужд.

Так как это формула массива, то для того, чтобы применить её в действии, нужно нажимать не кнопку Enter, а сочетание клавиш Ctrl+Shift+Enter. Делаем это.

Выделив второй столбец с датами и установив курсор в строку формул, вводим следующее выражение:

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

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

В открывшемся окне форматирования открываем вкладку «Число». В блоке «Числовые форматы» выделяем значение «Дата». В правой части окна можно выбрать желаемый тип отображения даты. После того, как настройки выставлены, жмем на кнопку «OK».

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

В запустившемся окне форматирования переходим во вкладку «Шрифт» и в соответствующем поле выбираем белый цвет. После этих действий щелкаем по кнопке «OK».

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

Способ 3: выборка по нескольким условиям с помощью формулы

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

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

Каждый раз после ввода не забываем набирать сочетание клавиш Ctrl+Shift+Enter.

Способ 4: случайная выборка

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

Эта функция выводит на экран случайное число. Для того, чтобы её активировать, жмем на кнопку ENTER.

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

Теперь у нас имеется диапазон ячеек, заполненный случайными числами. Но, он содержит в себе формулу СЛЧИС. Нам же нужно работать с чистыми значениями. Для этого следует выполнить копирование в пустой столбец справа. Выделяем диапазон ячеек со случайными числами. Расположившись во вкладке «Главная», щелкаем по иконке «Копировать» на ленте.

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

После этого, находясь во вкладке «Главная», кликаем по уже знакомому нам значку «Сортировка и фильтр». В выпадающем списке останавливаем выбор на пункте «Настраиваемая сортировка».

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

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

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

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

Рис. 12.1. Подсчет количества дат (заданных порядковыми номерами) по двум критериям: году (число) и месяцу (текст)

Скачать заметку в формате Word или pdf, примеры в формате Excel2013

Рис. 12.4. Формата года в критерии Е6 (число) не соответствует формату дат в диапазоне А2:А6 (порядковый номер)

Естественно эту формулу следует выполнять в массиве. Поэтому для подтверждения ее ввода следует нажимать не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.

Выполнение операций со списками (базами данных) в табличном процессоре Excel

Теперь запишем условия для отбора студентов 12-й группы. С условиями для студентов 11-й группы они связаны логическим ИЛИ, поэтому должны быть записаны в другой строке (правило № 4). В 14-й строке записываем условия: для столбцагруппа (ячейка А14) – 12, а для столбца год рождения(ячейка В14 ) – 1987.