0 added
3 removed
Original
2026-01-01
Modified
2026-02-21
1
<p><a>#Руководства</a></p>
1
<p><a>#Руководства</a></p>
2
<ul><li>6 апр 2022</li>
2
<ul><li>6 апр 2022</li>
3
<li>0</li>
3
<li>0</li>
4
</ul><p>Как перенести данные из одной таблицы в другую, если строки идут не по порядку? Разбираемся на примере каталога авто - переносим цены.</p>
4
</ul><p>Как перенести данные из одной таблицы в другую, если строки идут не по порядку? Разбираемся на примере каталога авто - переносим цены.</p>
5
<p>Иллюстрация: Meery Mary для Skillbox Media</p>
5
<p>Иллюстрация: Meery Mary для Skillbox Media</p>
6
<p>Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры - пять лет в банке и три - в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.</p>
6
<p>Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры - пять лет в банке и три - в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.</p>
7
<p>ВПР (Vlookup, или вертикальный просмотр) - поисковая функция в Excel. Она находит значения в одной таблице и переносит их в другую. Функция ВПР нужна, чтобы работать с большими объёмами данных - не нужно самостоятельно сопоставлять и переносить сотни наименований, функция делает это автоматически.</p>
7
<p>ВПР (Vlookup, или вертикальный просмотр) - поисковая функция в Excel. Она находит значения в одной таблице и переносит их в другую. Функция ВПР нужна, чтобы работать с большими объёмами данных - не нужно самостоятельно сопоставлять и переносить сотни наименований, функция делает это автоматически.</p>
8
<p>Разберёмся, зачем нужна функция и как её использовать. В конце материала расскажем, что делать, если нужен поиск данных сразу по двум параметрам.</p>
8
<p>Разберёмся, зачем нужна функция и как её использовать. В конце материала расскажем, что делать, если нужен поиск данных сразу по двум параметрам.</p>
9
-
<p><strong>11 полезных материалов от издательства "МИФ" - раздаём бесплатно в Telegram</strong></p>
10
-
<p>Дарим книги для буста карьеры, 12 инструментов для повышения качества жизни, энергобокс, гайд для внезапных удалёнщиков, инфографику "Супермен по привычке" и много других полезных материалов.</p>
11
-
<p><a>Забрать бесплатно</a></p>
12
<p>Представьте, что вы продаёте автомобили. У вас есть каталог с характеристиками авто и их стоимостью. Также у вас есть таблица с данными клиентов, которые забронировали эти автомобили.</p>
9
<p>Представьте, что вы продаёте автомобили. У вас есть каталог с характеристиками авто и их стоимостью. Также у вас есть таблица с данными клиентов, которые забронировали эти автомобили.</p>
13
Это каталог автомобилей с ценамиЭто список клиентов: указаны забронированные авто, но нет цен<p>Вам нужно сообщить покупателям, сколько стоят их авто. Перед тем как обзванивать клиентов, нужно объединить данные: добавить во вторую таблицу колонку с ценами из первой.</p>
10
Это каталог автомобилей с ценамиЭто список клиентов: указаны забронированные авто, но нет цен<p>Вам нужно сообщить покупателям, сколько стоят их авто. Перед тем как обзванивать клиентов, нужно объединить данные: добавить во вторую таблицу колонку с ценами из первой.</p>
14
<p>Просто скопировать и вставить эту колонку не получится. Искать каждое авто вручную и переносить цены - долго.</p>
11
<p>Просто скопировать и вставить эту колонку не получится. Искать каждое авто вручную и переносить цены - долго.</p>
15
<p>ВПР автоматически сопоставит названия автомобилей в двух таблицах. Функция скопирует цены из каталога в список забронированных машин. Так напротив каждого клиента будет стоять не только марка автомобиля, но и цена.</p>
12
<p>ВПР автоматически сопоставит названия автомобилей в двух таблицах. Функция скопирует цены из каталога в список забронированных машин. Так напротив каждого клиента будет стоять не только марка автомобиля, но и цена.</p>
16
<p>Ниже пошагово и со скриншотами разберёмся, как сделать ВПР для этих двух таблиц с данными.</p>
13
<p>Ниже пошагово и со скриншотами разберёмся, как сделать ВПР для этих двух таблиц с данными.</p>
17
<p><strong>Важно!</strong></p>
14
<p><strong>Важно!</strong></p>
18
<p>ВПР может не работать, если таблицы расположены в разных файлах. Тогда лучше собрать данные в одном файле, на разных листах.</p>
15
<p>ВПР может не работать, если таблицы расположены в разных файлах. Тогда лучше собрать данные в одном файле, на разных листах.</p>
19
<p>ВПР работает по следующему принципу. Функция просматривает выбранный диапазон первой таблицы вертикально сверху вниз до искомого значения‑идентификатора. Когда видит его, забирает значение напротив него из нужного столбца и копирует во вторую таблицу.</p>
16
<p>ВПР работает по следующему принципу. Функция просматривает выбранный диапазон первой таблицы вертикально сверху вниз до искомого значения‑идентификатора. Когда видит его, забирает значение напротив него из нужного столбца и копирует во вторую таблицу.</p>
20
<p>Подробнее о том, как определить все эти значения, поговорим<a>ниже</a>. А пока разберёмся на примере с продажей авто, где найти функцию ВПР в Excel и с чего начать работу.</p>
17
<p>Подробнее о том, как определить все эти значения, поговорим<a>ниже</a>. А пока разберёмся на примере с продажей авто, где найти функцию ВПР в Excel и с чего начать работу.</p>
21
<p>Сначала нужно построить функцию. Для этого выделяем ячейку, куда функция перенесёт найденное значение.</p>
18
<p>Сначала нужно построить функцию. Для этого выделяем ячейку, куда функция перенесёт найденное значение.</p>
22
<p>В нашем случае нужно перенести цены на авто из каталога в список клиентов. Для этого добавим пустой столбец "Цена, руб." в таблицу с клиентами и выберем ячейку напротив первого клиента.</p>
19
<p>В нашем случае нужно перенести цены на авто из каталога в список клиентов. Для этого добавим пустой столбец "Цена, руб." в таблицу с клиентами и выберем ячейку напротив первого клиента.</p>
23
Выделяем ячейку, в которую нужно перенести данные<p>Дальше открываем окно для построения функции ВПР. Есть два способа сделать это. Первый - перейти во вкладку "Формулы" и нажать на "Вставить функцию".</p>
20
Выделяем ячейку, в которую нужно перенести данные<p>Дальше открываем окно для построения функции ВПР. Есть два способа сделать это. Первый - перейти во вкладку "Формулы" и нажать на "Вставить функцию".</p>
24
Нажимаем сюда, чтобы открыть окно построения<p>Второй способ - нажать на "fx" в строке ссылок на любой вкладке таблицы.</p>
21
Нажимаем сюда, чтобы открыть окно построения<p>Второй способ - нажать на "fx" в строке ссылок на любой вкладке таблицы.</p>
25
<p>Справа появляется окно "Построитель формул". В нём через поисковик находим функцию ВПР и нажимаем "Вставить функцию".</p>
22
<p>Справа появляется окно "Построитель формул". В нём через поисковик находим функцию ВПР и нажимаем "Вставить функцию".</p>
26
Нажимаем сюда, чтобы открылась функция ВПР<p>Появляется окно для ввода аргументов функции. Как их заполнять - разбираемся ниже.</p>
23
Нажимаем сюда, чтобы открылась функция ВПР<p>Появляется окно для ввода аргументов функции. Как их заполнять - разбираемся ниже.</p>
27
Так выглядит окно для ввода аргументов<p>Последовательно разберём каждый аргумент: искомое значение, таблица, номер столбца, интервальный просмотр.</p>
24
Так выглядит окно для ввода аргументов<p>Последовательно разберём каждый аргумент: искомое значение, таблица, номер столбца, интервальный просмотр.</p>
28
<p><strong>Искомое значение</strong> - название ячейки с одинаковыми данными для обеих таблиц, по которым функция будет искать данные для переноса. В нашем примере это модель авто. Функция найдёт модель в таблице с каталогом авто, возьмёт оттуда стоимость и перенесёт в таблицу с клиентами.</p>
25
<p><strong>Искомое значение</strong> - название ячейки с одинаковыми данными для обеих таблиц, по которым функция будет искать данные для переноса. В нашем примере это модель авто. Функция найдёт модель в таблице с каталогом авто, возьмёт оттуда стоимость и перенесёт в таблицу с клиентами.</p>
29
<p>Порядок действий, чтобы указать значение, выглядит так:</p>
26
<p>Порядок действий, чтобы указать значение, выглядит так:</p>
30
<ul><li>Ставим курсор в окно "Искомое значение" в построителе формул.</li>
27
<ul><li>Ставим курсор в окно "Искомое значение" в построителе формул.</li>
31
<li>Выбираем первое значение столбца "Марка, модель"<strong>в таблице с клиентами</strong>. Это ячейка<em>A2.</em></li>
28
<li>Выбираем первое значение столбца "Марка, модель"<strong>в таблице с клиентами</strong>. Это ячейка<em>A2.</em></li>
32
</ul><p>Выбранное значение переносится в построитель формул и одновременно появляется в формуле строки ссылок:<em>fx=ВПР(A2).</em></p>
29
</ul><p>Выбранное значение переносится в построитель формул и одновременно появляется в формуле строки ссылок:<em>fx=ВПР(A2).</em></p>
33
Указать номер ячейки можно и вручную, но проще нажать на неё<p><strong>Таблица</strong>- это диапазон ячеек, из которого функция будет брать данные для искомого значения. В этот диапазон должны войти столбцы с искомым значением и со значением, которое нужно перенести в первую таблицу.</p>
30
Указать номер ячейки можно и вручную, но проще нажать на неё<p><strong>Таблица</strong>- это диапазон ячеек, из которого функция будет брать данные для искомого значения. В этот диапазон должны войти столбцы с искомым значением и со значением, которое нужно перенести в первую таблицу.</p>
34
<p>В нашем случае нужно перенести цены автомобилей. Поэтому в диапазон обязательно нужно включить столбцы "Марка, модель" (искомое значение) и "Цена, руб." (переносимое значение).</p>
31
<p>В нашем случае нужно перенести цены автомобилей. Поэтому в диапазон обязательно нужно включить столбцы "Марка, модель" (искомое значение) и "Цена, руб." (переносимое значение).</p>
35
<p><strong>Важно!</strong></p>
32
<p><strong>Важно!</strong></p>
36
<p>Для правильной работы ВПР искомое значение всегда должно находиться в первом столбце диапазона. У нас искомое значение находится в ячейке A2, поэтому диапазон должен начинаться с A.</p>
33
<p>Для правильной работы ВПР искомое значение всегда должно находиться в первом столбце диапазона. У нас искомое значение находится в ячейке A2, поэтому диапазон должен начинаться с A.</p>
37
<p>Порядок действий для указания диапазона:</p>
34
<p>Порядок действий для указания диапазона:</p>
38
<ul><li>Ставим курсор в окно "Таблица" в построителе формул.</li>
35
<ul><li>Ставим курсор в окно "Таблица" в построителе формул.</li>
39
<li>Переходим<strong>в таблицу "Каталог авто"</strong>.</li>
36
<li>Переходим<strong>в таблицу "Каталог авто"</strong>.</li>
40
<li>Выбираем диапазон, в который попадают столбцы "Марка, модель" и "Цена, руб.". Это<em>A2:E19.</em></li>
37
<li>Выбираем диапазон, в который попадают столбцы "Марка, модель" и "Цена, руб.". Это<em>A2:E19.</em></li>
41
<li>Закрепляем выбранный диапазон. На Windows для этого выбираем значение диапазона в строке ссылок и нажимаем клавишу F4, на macOS - выбираем значение диапазона в строке ссылок и нажимаем клавиши Cmd + T. Закрепить диапазон нужно, чтобы можно было протянуть функцию вниз и она сработала корректно во всех остальных строках.</li>
38
<li>Закрепляем выбранный диапазон. На Windows для этого выбираем значение диапазона в строке ссылок и нажимаем клавишу F4, на macOS - выбираем значение диапазона в строке ссылок и нажимаем клавиши Cmd + T. Закрепить диапазон нужно, чтобы можно было протянуть функцию вниз и она сработала корректно во всех остальных строках.</li>
42
</ul><p>Выбранный диапазон переносится в построитель формул и одновременно появляется в формуле строки ссылок:<em><em>fx=ВПР(A2;'каталог авто'!$A$2:$E$19)</em>.</em></p>
39
</ul><p>Выбранный диапазон переносится в построитель формул и одновременно появляется в формуле строки ссылок:<em><em>fx=ВПР(A2;'каталог авто'!$A$2:$E$19)</em>.</em></p>
43
Так выглядит диапазон, в котором функция будет искать искомые и переносимые значения<p><strong>Номер столбца</strong> - порядковый номер столбца в первой таблице, в котором находится переносимое значение. Считается по принципу: номер 1 - самый левый столбец, 2 - столбец правее и так далее.</p>
40
Так выглядит диапазон, в котором функция будет искать искомые и переносимые значения<p><strong>Номер столбца</strong> - порядковый номер столбца в первой таблице, в котором находится переносимое значение. Считается по принципу: номер 1 - самый левый столбец, 2 - столбец правее и так далее.</p>
44
<p>В нашем случае значение для переноса - цена - находится в пятом столбце слева.</p>
41
<p>В нашем случае значение для переноса - цена - находится в пятом столбце слева.</p>
45
Если столбцы не пронумерованы, посчитайте их вручную<p>Чтобы задать номер, установите курсор в окно "Номер столбца" в построителе формул и введите значение. В нашем примере это 5. Это значение появится в формуле в строке ссылок:<em>fx=ВПР(A2;'каталог авто'!$A$2:$E$19;5).</em></p>
42
Если столбцы не пронумерованы, посчитайте их вручную<p>Чтобы задать номер, установите курсор в окно "Номер столбца" в построителе формул и введите значение. В нашем примере это 5. Это значение появится в формуле в строке ссылок:<em>fx=ВПР(A2;'каталог авто'!$A$2:$E$19;5).</em></p>
46
<p><strong>Интервальный просмотр</strong>- условное значение, которое настроит, насколько точно сработает функция:</p>
43
<p><strong>Интервальный просмотр</strong>- условное значение, которое настроит, насколько точно сработает функция:</p>
47
<ul><li>Если нужно точное совпадение при поиске ВПР, вводим 0.</li>
44
<ul><li>Если нужно точное совпадение при поиске ВПР, вводим 0.</li>
48
<li>Если нужно приближённое соответствие при поиске ВПР, вводим 1.</li>
45
<li>Если нужно приближённое соответствие при поиске ВПР, вводим 1.</li>
49
</ul><p>В нашем случае нужно, чтобы функция подтянула точные значения цен авто, поэтому нам подходит первый вариант.</p>
46
</ul><p>В нашем случае нужно, чтобы функция подтянула точные значения цен авто, поэтому нам подходит первый вариант.</p>
50
<p>Ставим курсор в окно "Интервальный просмотр" в построителе формул и вводим значение: 0. Одновременно это значение появляется в формуле строки ссылок:<em>fx=ВПР(A2;'каталог авто'!$A$2:$E$19;5;0).</em>Это окончательный вид функции.</p>
47
<p>Ставим курсор в окно "Интервальный просмотр" в построителе формул и вводим значение: 0. Одновременно это значение появляется в формуле строки ссылок:<em>fx=ВПР(A2;'каталог авто'!$A$2:$E$19;5;0).</em>Это окончательный вид функции.</p>
51
Так выглядят настроенные аргументы функции<p>Чтобы получить результат функции, нажимаем кнопку "Готово" в построителе формул. В выбранной ячейке появляется нужное значение. В нашем случае - цена первой модели авто.</p>
48
Так выглядят настроенные аргументы функции<p>Чтобы получить результат функции, нажимаем кнопку "Готово" в построителе формул. В выбранной ячейке появляется нужное значение. В нашем случае - цена первой модели авто.</p>
52
Формула сработала для одной строки. <p>Дальше нужно протянуть это значение вниз до конца таблицы, чтобы функция нашла и перенесла цены всех оставшихся моделей. Для этого мы закрепляли интервал, когда заполняли аргументы.</p>
49
Формула сработала для одной строки. <p>Дальше нужно протянуть это значение вниз до конца таблицы, чтобы функция нашла и перенесла цены всех оставшихся моделей. Для этого мы закрепляли интервал, когда заполняли аргументы.</p>
53
<p>Получилась таблица с ценами - можно звонить клиентам и сообщать о стоимости авто. Данные перенесены без ошибок, а потратили мы на это несколько минут.</p>
50
<p>Получилась таблица с ценами - можно звонить клиентам и сообщать о стоимости авто. Данные перенесены без ошибок, а потратили мы на это несколько минут.</p>
54
Так выглядит результат: настраивали ВПР несколько минут, а она перенесла цены за мгновение<p>В нашем примере модели автомобилей в таблицах не повторяются, поэтому мы ищем только по одному критерию - названию модели. Но бывают случаи, когда такой поиск не подходит.</p>
51
Так выглядит результат: настраивали ВПР несколько минут, а она перенесла цены за мгновение<p>В нашем примере модели автомобилей в таблицах не повторяются, поэтому мы ищем только по одному критерию - названию модели. Но бывают случаи, когда такой поиск не подходит.</p>
55
<p>Например, у нас несколько одинаковых моделей с разным цветом.</p>
52
<p>Например, у нас несколько одинаковых моделей с разным цветом.</p>
56
Таблица с ценами на модели разных цветов<p>И по традиции есть таблица с клиентами, которые эти модели забронировали.</p>
53
Таблица с ценами на модели разных цветов<p>И по традиции есть таблица с клиентами, которые эти модели забронировали.</p>
57
Сюда нужно перенести цены автомобилей<p>Если идти по классическому пути ВПР, получится такая функция:<em>fx=ВПР(A29;'каталог авто'!$A$29:$E$35;5;0).</em>В таком виде ВПР найдёт первую совпавшую модель и подтянет её стоимость. Параметр цвета не будет учтён.</p>
54
Сюда нужно перенести цены автомобилей<p>Если идти по классическому пути ВПР, получится такая функция:<em>fx=ВПР(A29;'каталог авто'!$A$29:$E$35;5;0).</em>В таком виде ВПР найдёт первую совпавшую модель и подтянет её стоимость. Параметр цвета не будет учтён.</p>
58
<p>Соответственно, цены у всех Nissan Juke будут 1 850 000 рублей, у всех Subaru Forester - 3 190 000 рублей, у всех Toyota C-HR - 2 365 000 рублей.</p>
55
<p>Соответственно, цены у всех Nissan Juke будут 1 850 000 рублей, у всех Subaru Forester - 3 190 000 рублей, у всех Toyota C-HR - 2 365 000 рублей.</p>
59
Такой результат получится, если использовать обычную функцию ВПР<p>Поэтому в этом варианте нужно искать стоимость авто сразу по двум критериям - модель и цвет. Для этого нужно изменить формулу вручную. В строке ссылок ставим курсор сразу после искомого значения.</p>
56
Такой результат получится, если использовать обычную функцию ВПР<p>Поэтому в этом варианте нужно искать стоимость авто сразу по двум критериям - модель и цвет. Для этого нужно изменить формулу вручную. В строке ссылок ставим курсор сразу после искомого значения.</p>
60
<p>Дописываем в формулу фразу<em>ЕСЛИ('каталог авто'!$B$29:$B$35=B29</em>, где:</p>
57
<p>Дописываем в формулу фразу<em>ЕСЛИ('каталог авто'!$B$29:$B$35=B29</em>, где:</p>
61
<ul><li><em>'каталог авто'!$B$29:$B$35</em> - закреплённый диапазон цвета автомобилей в таблице, откуда нужно перенести данные. Это весь столбец с ценами.</li>
58
<ul><li><em>'каталог авто'!$B$29:$B$35</em> - закреплённый диапазон цвета автомобилей в таблице, откуда нужно перенести данные. Это весь столбец с ценами.</li>
62
<li><em>B29</em> - искомое значение цвета автомобиля в таблице, куда мы переносим данные. Это первая ячейка в столбце с цветом - дополнительным параметром для поиска.</li>
59
<li><em>B29</em> - искомое значение цвета автомобиля в таблице, куда мы переносим данные. Это первая ячейка в столбце с цветом - дополнительным параметром для поиска.</li>
63
</ul><p>Итоговая функция такая:<em>fx=ВПР(A29;ЕСЛИ('каталог авто'!$B$29:$B$35=B29;'каталог авто'!$A$29:$E$35);5;0)</em>. Теперь значения цен переносятся верно.</p>
60
</ul><p>Итоговая функция такая:<em>fx=ВПР(A29;ЕСЛИ('каталог авто'!$B$29:$B$35=B29;'каталог авто'!$A$29:$E$35);5;0)</em>. Теперь значения цен переносятся верно.</p>
64
Так выглядит таблица, в которую ВПР переносит данные на основе двух совпадений<p>Как использовать ВПР в "Google Таблицах"? В них тоже есть функция Vlookup, но нет окна построителя формул. Поэтому придётся прописывать её вручную. Перечислите через точку с запятой все аргументы и не забудьте зафиксировать диапазон. Для фиксации поставьте перед каждым символом значок доллара. В готовой формуле это будет выглядеть так: =ВПР(A2;'Лист1'!<strong>$A$2:$C$5</strong>;3;0).</p>
61
Так выглядит таблица, в которую ВПР переносит данные на основе двух совпадений<p>Как использовать ВПР в "Google Таблицах"? В них тоже есть функция Vlookup, но нет окна построителя формул. Поэтому придётся прописывать её вручную. Перечислите через точку с запятой все аргументы и не забудьте зафиксировать диапазон. Для фиксации поставьте перед каждым символом значок доллара. В готовой формуле это будет выглядеть так: =ВПР(A2;'Лист1'!<strong>$A$2:$C$5</strong>;3;0).</p>
65
<a>Курс-тренажёр: "Excel + "Google Таблицы" с нуля до PRO" Узнать о курсе</a>
62
<a>Курс-тренажёр: "Excel + "Google Таблицы" с нуля до PRO" Узнать о курсе</a>