Excel подстановка значений из другой таблицы
То, что вы описали – относится к довольно популярным задачам, которые относительно просто и быстро решать с помощью Excel. Достаточно загнать в программу две ваши таблицы, и воспользоваться функцией ВПР . О ее работе ниже.
Ставим указатель мышки в ячейку B2 – то бишь в первую ячейки столбца, где у нас нет значения и пишем формулу:
A2 – значение из первого столбца первой таблицы (то, что мы будем искать в первом столбце второй таблицы);
$E$1:$F$7 – полностью выделенная вторая таблица (в которой хотим что-то найти и скопировать). Обратите внимание на значок «$» – он необходим, чтобы при копировании формулы не менялись ячейки выделенной второй таблицы;
2 – номер столбца, из которого буем копировать значение (обратите внимание, что у нас выделенная вторая таблица имеет всего 2 столбца. Если бы у нее было 3 столбца – то значение можно было бы копировать из 2-го или 3-го столбца);
ЛОЖЬ – ищем точное совпадение (иначе будет подставлено первое похожее, что явно нам не подходит).
Собственно, можете готовую формулу подогнать под свои нужды, слегка изменив ее. Результат работы формулы представлен на картинке ниже: цена была найдена во второй таблице и подставлена в авто-режиме. Все работает!
Чтобы цена была проставлена и для других наименований товара – просто растяните (скопируйте) формулу на другие ячейки. Пример ниже.
Растягиваем формулу (копируем формулу в другие ячейки)
После чего, как видите, первые столбцы у таблиц будут сравнены: из строк, где значения ячеек совпали – будут скопированы и подставлены нужные данные. В общем-то, понятно, что таблицы могут быть гораздо больше!
Примечание : должен сказать, что функция ВПР достаточно требовательна к ресурсам компьютера. В некоторых случаях, при чрезмерно большом документе, чтобы сравнить таблицы может понадобиться довольно длительное время. В этих случаях, стоит рассмотреть либо другие формулы, либо совсем иные решения (каждый случай индивидуален).
ВПР в Excel – очень полезная функция, позволяющая подтягивать данные из одной таблицы в другую по заданным критериям. Это достаточно «умная» команда, потому что принцип ее работы складывается из нескольких действий: сканирование выбранного массива, выбор нужной ячейки и перенос данных из нее.
Весь процесс просмотра и выборки данных происходит за доли секунды, поэтому результат мы получаем моментально.
Excel Выбрать Значение Ячейки по Дате
- искомое значение – то, что предполагается искать в таблице;
- таблица – массив данных, из которых подтягиваются данные. Примечательно, таблица с данными должна находиться правее исходной таблицы, иначе функций ВПР не будет работать;
- номер столбца таблицы, из которой будут подтягиваться значения;
- интервальный просмотр – логический аргумент, определяющий точность или приблизительность значения (0 или 1).
Или: используйте сочетание клавиш: нажмите Ctrl + ; (точка с запятой на английской раскладке), затем отпустите клавиши и нажмите Ctrl + Shift + ; (точка с запятой на английской раскладке).
Excel Выбрать Значение Ячейки по Дате
При работе с данными время от времени возникают ситуации, когда нам необходимо найти значение ячейки на пересечении определенных строки и столбца.
В данной статье я Вам расскажу о трёх способах осуществления этой задачи.
Данная функция позволяет извлечь значение из диапазона ячеек по номеру строки и столбца.
Массив – ссылка на диапазон ячеек, в котором нам нужно осуществить поиск;
Номер_строки – строка, из которой нужно извлечь значения;
Номер_столбца – столбец, из которого нужно извлечь значение.
Функция ИНДЕКС также может извлекать значение из разных выделенных областей, но в данной статье я не буду останавливаться на этой возможности.
Для автоматического поиска строки/столбца функцию ИНДЕКС, как правило, дополняют двумя функциями ПОИСКПОЗ (MATCH).
=ИНДЕКС($C$7:$F$9;ПОИСКПОЗ($I$5;$B$7:$B$9;0);ПОИСКПОЗ($I$6;$C$6:$F$6;0))
При смене параметров поиска, значение пересекающейся ячейки будет изменяться автоматически.
Очень интересная функция, которой можно найти много применений. Она перемножает соответствующие элементы заданных массивов и возвращает сумму произведений.
Для решения нашей задачи формула будет выглядеть так:
Простыми словами синтаксис функции можно представить так:
=СУММПРОИЗВ((условие_1)*(условие_2)*(что_суммировать))
Сумма умножений 0, 1 и значений таблицы извлечёт нам искомое значение.
Третий способ, который лично мне очень нравится своей простотой. Единственное условие для его работы – названия в столбцах и строках должны быть без пробелов (их можно удалить или заменить на нижнее подчеркивание «_»).
Функция ДВССЫЛ возвращает ссылку, заданную текстовой строкой. Ссылки немедленно вычисляются для вывода их содержимого. Функция ДВССЫЛ используется, если требуется изменить ссылку на ячейку в формуле без изменения самой формулы.
Если на первый взгляд функция выглядит простой и малополезной, то при дальнейшем её изучении Вы узнаете, что с её помощью мы можем перемещаться по листам, транспонировать таблицы, отбирать чётные (нечётные) строки и многое другое. Ведь для нее аргумент Ссылка_на_ячейку – всего лишь текстовая строка, которую можно изменять формулами.
Чтобы найти значение ячейки на пересечении определенных сроки и столбца нам понадобится сразу две функции ДВССЫЛ.
Для начала нам необходимо создать диапазоны для строк и столбцов.
Выделяем столбцы с заголовками и нажимаем комбинацию клавиш Ctrl+Shift+F3
Excel даёт нам возможность использовать несколько вариантов решения для одной задачи. Там, где невозможно применить один способ, практически всегда есть возможность подобрать другой. Настоятельно рекомендую Вам изучить функции, рассмотренные в данной статье и Ваша работа с данными, станет намного эффективнее.
Извлечь первые N символов ячейки Excel! SEMTools для Excel
Обладая такими существенными знаниями, мы можем смело использовать функцию ПОИСК, которая находит нужный нам текст в текстовой строке и возвращают начальную позицию нужного нам текста внутри всей текстовой строки.
