Размеры вставляемых и выделенных областей не соответствуют Excel

Обновлено: 21.11.2024

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

Получение случайной выборки

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

Если Столбец A еще не пуст, добавьте столбец слева от него.

В Столбце A выберите строки ячеек, которым вы хотите присвоить случайное число.
ПРИМЕР. Если ваши данные занимают 390 строк по вертикали, выберите ячейки с A2 по A390, но не выделяйте ячейку с заголовком A1.

В текстовом поле Формула введите =RAND()
СОВЕТ. Если панель формул не отображается, в меню «Вид» выберите «Панель формул» .

Нажмите [Ctrl] + [return].
ПРИМЕЧАНИЯ.
В столбце A теперь отображаются случайные числа, соответствующие каждой строке.
Однако каждая ячейка содержит формулу и не может быть отсортирована.

Выберите все ячейки, содержащие только что сгенерированные случайные числа.

В меню Правка выберите Копировать

Поместите точку вставки в первую ячейку в верхней части столбца B.

В меню "Правка" выберите Специальная вставка.
Откроется диалоговое окно Специальная вставка.

Выберите Значения

Нажмите "ОК".
ПРИМЕЧАНИЯ.
Столбец B теперь содержит случайные числовые значения, которые можно сортировать.
Числа не будут соответствовать значениям в столбце A.

Удалить столбец A.
СОВЕТ. Этот столбец содержит формулы, которые больше не нужны.

Выделите все свои данные, кроме ячеек заголовков.
ПРИМЕЧАНИЕ. Сюда входит столбец A.

В меню Данные выберите Сортировка.
Откроется диалоговое окно Сортировка.

В раскрывающемся меню Сортировать по выберите Столбец A

Выберите По возрастанию

Нажмите "ОК".
Диалоговое окно Сортировка закроется.
Ваши данные упорядочены по случайным числам.

Выберите образец, указав количество строк для нужного размера образца.
ПРИМЕР. Выберите первые 200 строк.

Создание случайного числа

Функция =RAND() создает случайное число от 0 до 1 в указанных вами ячейках.

Выберите ячейки, в которых вы хотите создать случайное число.

В текстовом поле Формула введите =СЛУЧИС()

Если вы выбрали только одну ячейку, нажмите [return].
Если вы выбрали несколько ячеек, нажмите [Ctrl] + [return].
В нужных ячейках появляется случайное число.

Создание случайного числа в указанном диапазоне

Функция =СЛУЧМЕЖДУ создает случайное число в указанном вами диапазоне. В указанных вами ячейках будет сгенерировано целое число, в отличие от функции =СЛЧИС, которая генерирует десятичное число.

ПРИМЕЧАНИЕ. Если вы используете функцию =СЛУЧМЕЖДУ для нескольких ячеек и не хотите, чтобы некоторые из сгенерированных чисел были одинаковыми, обязательно укажите диапазон, превышающий количество ячеек, к которым вы применяете функция для.

Выберите ячейки, в которых вы хотите создать случайное число.

В текстовом поле Формула введите =СЛУЧМЕЖДУ([Снизу],[Сверху])
ПРИМЕЧАНИЕ : Нижнее число — это число, которое должно быть самым низким в вашем диапазоне, Верхнее — это число, которое должно быть самым высоким в вашем диапазоне.
ПРИМЕР: Чтобы сгенерировать числа от 1 до 50, в < em>Формула введите =СЛУЧМЕЖДУ(1,50).

Была ли эта статья полезной? Да Нет

Подключиться к УВЭК

Университет Висконсина – О-Клэр

Как скопировать и вставить размеры ячеек (ширину столбцов и высоту строк) в Excel?

Как мы знаем, мы можем скопировать диапазон без потери ширины столбцов, выбрав Главная > Вставить > Специальная вставка > Ширина столбцов. Но как скопировать диапазон без потери размера ячейки (как ширины столбца, так и высоты строки) в Excel? В этой статье представлены два обходных пути, как это сделать.

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!

Копировать и вставлять размеры ячеек (ширину столбцов и высоту строк) с помощью функции копирования

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

<р>1. Перейдите на рабочий лист, где вы будете копировать размеры ячеек, щелкните стрелку в верхнем левом углу рабочей области, чтобы выбрать все ячейки на активном рабочем листе.

<р>2. Скопируйте все ячейки, нажав одновременно клавиши Ctrl+C.

<р>3. Перейдите на рабочий лист назначения, куда вы будете вставлять размеры ячеек, и щелкните стрелку в верхнем левом углу рабочей области, чтобы выбрать все ячейки на активном листе.

<р>4. Вставьте все ячейки, одновременно нажав клавиши Ctrl + V.

<р>5. Продолжайте выделять все ячейки и нажмите клавишу Delete, чтобы очистить все содержимое ячеек.

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

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

Скопируйте и вставьте размеры ячеек (ширину столбцов и высоту строк) с помощью Kutools for Excel

Этот метод представит утилиту Kutools for Excel’s Copy Ranges для копирования размеров ячеек (как ширины столбца, так и высоты строки) из одного диапазона в другой в Excel.

Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Бесплатная пробная версия сейчас!

Kutools for Excel — объединяет более 300 расширенных функций и инструментов для Microsoft Excel

<р>1. Выберите диапазон, размеры ячеек которого вы скопируете, и нажмите Kutools > Копировать диапазоны.

<р>2. В открывшемся диалоговом окне «Копировать несколько диапазонов» выберите нужный параметр в разделе «Специальная вставка», установите флажок «Включая высоту строки» и «Включая ширину столбца» и нажмите кнопку «ОК». Смотрите скриншот:

<р>3. Во втором диалоговом окне «Копировать несколько диапазонов» выберите первую ячейку целевого диапазона и нажмите кнопку «ОК».

На данный момент все размеры ячеек были скопированы из выбранного диапазона и вставлены в целевой диапазон. Смотрите скриншот:

Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия на 30 дней, кредитная карта не требуется! Получить сейчас

Демонстрация: копирование и вставка размеров ячеек (ширины столбцов и высоты строк)

Kutools for Excel включает в себя более 300 удобных инструментов для Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Загрузите и получите бесплатную пробную версию прямо сейчас!

Вы можете копировать данные внутри формы, из одной формы в другую или из другого приложения, например Microsoft Excel. (Информацию о копировании и вставке данных между Microsoft Excel и формами планирования см. в разделе Копирование и вставка данных между Microsoft Excel и формами планирования.) За одну операцию копирования и вставки можно копировать из одной ячейки в другую, из одной ячейки во многие ячеек или из многих ячеек во многие ячейки.

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

Чтобы скопировать и вставить данные:

Выберите ячейки, содержащие данные для копирования.

Щелкните правой кнопкой мыши, выберите "Изменить" и выберите нужный вариант:

Вырезать удаляет значения ячеек

Копировать копирует значения ячеек. Выберите ячейки, в которые нужно вставить данные. Щелкните правой кнопкой мыши и выберите "Вставить" .

Очистить очищает значения ячеек

О копировании и вставке данных:

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

Планирование не вставляет данные в ячейки, доступные только для чтения.

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

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

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

Копирование и вставка данных из текстового редактора (например, TextPad, Notepad или WordPad) завершается ошибкой, если данные разделены пробелами. Вместо этого используйте значения с разделителями табуляции.

Информацию о копировании и вставке данных между Microsoft Excel и формами планирования см. в разделе Копирование и вставка данных между Microsoft Excel и формами планирования.

Следующие функции поддерживаются только в браузере Internet Explorer:

Копирование данных из форм планирования и вставка их в Microsoft Excel

Копирование данных из одной формы планирования в другую

Копирование и вставка нечисловых данных, например, типов данных Smart List, Date и Text

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

Прежде чем двигаться дальше, позвольте мне кратко напомнить вам синтаксис:

Теперь, когда все находятся на одной странице, давайте подробнее рассмотрим примеры расширенных формул ВПР:

Как выполнить визуальный поиск по нескольким критериям

Функция Excel VLOOKUP очень полезна при поиске определенного значения в базе данных. Однако ему не хватает важной функции — его синтаксис допускает только одно значение поиска. Но что, если вы хотите найти несколько условий? Есть несколько различных решений на ваш выбор.

Формула 1. ВПР на основе двух значений

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

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

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

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

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

=ВПР("Джереми Свитс", A2:D11, 4, ЛОЖЬ)

Или введите критерии в отдельные ячейки (в нашем случае G1 и G2) и соедините эти ячейки:

=ВПР(G1&" "&G2, A2:D11, 4, ЛОЖЬ)

Поскольку мы хотим вернуть значение из столбца D, который является четвертым в массиве таблиц, мы используем 4 для col_index_num. Аргументу range_lookup присвоено значение FALSE для Vlookup точного совпадения. На снимке экрана ниже показан результат:

Если ваша таблица поиска находится на другом листе, включите имя листа в формулу ВПР. Например:

=ВПР(G1&" "&G2, Заказы!A2:D11, 4, ЛОЖЬ)

В качестве альтернативы создайте именованный диапазон для таблицы поиска (например, Заказы), чтобы упростить чтение формулы:

=ВПР(G1&" "&G2, Заказы, 4, ЛОЖЬ)

Примечание. Чтобы формула работала правильно, значения во вспомогательном столбце должны быть объединены точно так же, как в аргументе lookup_value. Например, мы использовали символ пробела для разделения критериев во вспомогательном столбце (B2&" "&C2) и в формуле ВПР (G1&" "&G2).

Формула 2. ВПР Excel с несколькими условиями

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

К счастью, Microsoft Excel часто предоставляет несколько способов сделать одно и то же. Для поиска нескольких критериев можно использовать либо комбинацию ПОИСКПОЗ ИНДЕКС, либо функцию XLOOKUP, недавно представленную в Office 365.

Например, для поиска по трем различным значениям (Дата, Имя клиента и Продукт) используйте одну из следующих формул. :

=ИНДЕКС(D2:D11, ПОИСКПОЗ(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))

=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)

G1 – критерий 1 (дата).
G2 – критерий 2 (имя клиента).
G3 – критерий 3 (продукт).
A2:A11 — диапазон поиска 1 (даты)
B2:B11 — диапазон поиска 2 (имена клиентов).
C2:C11 – диапазон поиска 3 (товары).
D2:D11 — диапазон возврата (количество)

Примечание. Во всех версиях, кроме Excel 365, ПОИСКПОЗ ИНДЕКС следует вводить как формулу массива CSE, нажав Ctrl + Shift + Enter. В Excel 365, который поддерживает динамические массивы, он также работает как обычная формула.

Для подробного объяснения формул см.:

Как использовать функцию ВПР для получения второго, третьего или n-го совпадения

Как вы уже знаете, функция ВПР в Excel может получить только одно совпадающее значение, точнее, первое найденное совпадение. Но что, если в вашем массиве поиска есть несколько совпадений, и вы хотите получить 2-й или 3-й экземпляр? Задача звучит довольно запутанно, но решение существует!

Формула 1. N-й экземпляр Vlookup

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

Самый простой способ — добавить вспомогательный столбец слева от таблицы, как мы сделали в первом примере. Но на этот раз мы заполним его именами клиентов и номерами экземпляров, такими как "John Doe1", "John Doe2" и т. д.

Чтобы получить вхождение, используйте функцию СЧЁТЕСЛИ со ссылкой на смешанный диапазон (первая ссылка является абсолютной, а вторая — относительной, например $B$2:B2). Поскольку относительная ссылка изменяется в зависимости от положения ячейки, в которую копируется формула, в строке 3 она станет $B$2:B3, в строке 4 — $B$2:B4 и т. д.

В сочетании с именем клиента (B2) формула принимает следующий вид:

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

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

=ВПР(F1&F2, A2:C11, 3, ЛОЖЬ)

Формула 2. Второй поиск с помощью ВПР

Если вы ищете второй экземпляр искомого значения, вы можете обойтись без вспомогательного столбца. Вместо этого создайте массив таблиц динамически, используя функцию ДВССЫЛ вместе с ПОИСКПОЗ:

=ВПР(E1, ДВССЫЛ("A"&(ПОИСКПОЗ(E1, A2:A11, 0)+2)&":B11"), 2, ЛОЖЬ)

Обратите внимание, что приведенная выше формула написана для конкретного случая, когда ячейки данных в справочной таблице начинаются со строки 2. Если ваша таблица находится где-то в середине листа, используйте эту универсальную формулу, где A1 — это верхний- левая ячейка таблицы поиска, содержащая заголовок столбца:

=ВПР(E1, ДВССЫЛ("A"&(ПОИСКПОЗ(E1, A2:A11, 0)+1+СТРОКА(A1))&":B11"), 2, ЛОЖЬ)

Как работает эта формула

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

ДВССЫЛ("A"&(ПОИСКПОЗ(E1, A2:A11, 0)+2)&":B11")

Функция ПОИСКПОЗ, настроенная на точное совпадение (0 в последнем аргументе), сравнивает целевое имя (E1) со списком имен (A2:A11) и возвращает позицию первого найденного совпадения, в нашем случае это 3. . Это число будет использоваться в качестве начальной координаты строки для диапазона vlookup, поэтому мы добавляем к нему 2 (+1, чтобы исключить первый экземпляр, и +1, чтобы исключить строку 1 с заголовками столбцов). Кроме того, вы можете использовать 1+СТРОКА(A1) для автоматического расчета необходимой корректировки на основе положения строки заголовка (в нашем случае A1).

В результате мы получаем следующую текстовую строку, которая ДВССЫЛЕННО преобразуется в ссылку на диапазон:

Этот диапазон переходит в аргумент table_array функции ВПР, заставляя его начать поиск в строке 5, пропуская первый экземпляр искомого значения:

ВПР(E1, A5:B11, 2, ЛОЖЬ)

Как выполнять визуальный поиск и возвращать несколько значений в Excel

Функция Excel VLOOKUP предназначена для возврата только одного совпадения. Есть ли способ использовать Vlookup для нескольких экземпляров? Да, есть, хоть и не из легких.Для этого требуется комбинированное использование нескольких функций, таких как ИНДЕКС, МАЛЕНЬКИЙ и СТРОКА — формула массива.

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

Есть 2 способа ввести формулу на лист:

Введите формулу в первую ячейку, нажмите Ctrl + Shift + Enter , а затем перетащите ее вниз еще на несколько ячеек.
Выделите несколько соседних ячеек в одном столбце (F1:F11 на снимке экрана ниже), введите формулу и нажмите Ctrl + Shift + Enter, чтобы завершить ее.

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

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

Как искать в строках и столбцах (двусторонний поиск)

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

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

С именами элементов в A2:A9, названиями месяцев в B1:F1, целевым элементом в I1 и целевым месяцем в I2 формула выглядит следующим образом:

=ВПР(I1, A2:F9, ПОИСКПОЗ(I2, A1:F1, 0), ЛОЖЬ)

Как работает эта формула

Основой формулы является стандартная функция ВПР, которая ищет точное совпадение с искомым значением в I1. Но поскольку мы не знаем, в каком именно столбце находятся продажи за конкретный месяц, мы не можем указать номер столбца напрямую в аргументе col_index_num. Чтобы найти этот столбец, мы используем следующую функцию ПОИСКПОЗ:

В переводе на английский формула гласит: найдите значение I2 в A1:F1 и верните его относительное положение в массиве. Указав 0 в третьем аргументе, вы указываете ПОИСКПОЗ найти значение, точно равное искомому значению (это похоже на использование FALSE для аргумента range_lookup функции ВПР).

Поскольку Mar находится в 4-м столбце в массиве поиска, функция ПОИСКПОЗ возвращает 4, что соответствует аргументу col_index_num функции ВПР:

ВПР(I1, A2:F9, 4, ЛОЖЬ)

Обратите внимание, хотя названия месяцев начинаются в столбце B, мы используем A1:I1 для массива поиска. Это делается для того, чтобы число, возвращаемое функцией ПОИСКПОЗ, соответствовало положению столбца в table_array функции ВПР.

Чтобы узнать больше о том, как выполнять матричный поиск в Excel, см. ИНДЕКС ПОИСКПОЗ ПОИСКПОЗ и другие формулы для двумерного поиска.

Как сделать множественный ВПР в Excel (вложенный ВПР)

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

На изображении ниже показана ситуация:

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

Для удобства сначала создадим пару именованных диапазонов:

Таблица поиска 1 называется Products (D3:E10)
Таблица поиска 2 называется Prices (G3:H10)

Таблицы могут находиться на одном или разных листах.

А теперь мы выполним так называемый двойной ВПР, он же вложенный ВПР.

Сначала создайте формулу ВПР, чтобы найти название продукта в таблице поиска 1 (с именем Товары) на основе идентификатора элемента (A3):

=ВПР(A3, Товары, 2, ЛОЖЬ)

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

=ВПР(ВПР(A3, Товары, 2, ЛОЖЬ), Цены, 2, ЛОЖЬ)

На снимке экрана ниже показана наша вложенная формула Vlookup в действии:

Как выполнять динамический поиск нескольких листов

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

Это может быть легче понять на примере. Допустим, у вас есть несколько региональных отчетов о продажах в одном формате, и вы хотите получить данные о продажах определенного продукта в определенных регионах:

Как и в предыдущем примере, мы начинаем с определения нескольких имен:

Диапазон A2:B5 на листе CA называется CA_Sales.
Диапазон A2:B5 на листе FL называется FL_Sales.
Диапазон A2:B5 на листе KS называется KS_Sales.

Как видите, все именованные диапазоны имеют общую часть (Продажи) и уникальные части (CA, FL, КС). Обязательно назовите свои диапазоны аналогичным образом, так как это важно для формулы, которую мы собираемся построить.

Формула 1. НЕПРЯМАЯ ВПР для динамического извлечения данных из разных листов

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

Для этого примера мы организуем сводную таблицу следующим образом:

Введите интересующие вас товары в A2 и A3. Это наши поисковые значения.
Введите уникальные части именованных диапазонов в B1, C1 и D1.

А теперь мы объединяем ячейку, содержащую уникальную часть (B1), с общей частью ("_Sales") и передаем полученную строку в ДВССЫЛ:

Функция ДВССЫЛ преобразует строку в имя, понятное Excel, и вы помещаете его в аргумент table_array функции ВПР:

=ВПР($A2, ДВССЫЛ(B$1&"_Продажи"), 2, ЛОЖЬ)

Приведенная выше формула переходит в ячейку B2, а затем вы копируете ее вниз и вправо.

Обратите внимание, что в значении поиска ($A2) мы заблокировали координату столбца с абсолютной ссылкой на ячейку, чтобы столбец оставался фиксированным при копировании формулы вправо. В справочнике B$1 мы заблокировали строку, потому что мы хотим, чтобы координата столбца изменялась, и передавали соответствующую часть имени в INDIRECT в зависимости от столбца, в который копируется формула:

Если ваша основная таблица организована по-другому, значения поиска в строке и уникальные части имен диапазонов в столбце, то вы должны заблокировать координату строки в значении поиска (B$1) и координату столбца в имени части ($A2):

=ВПР(B$1, ДВССЫЛ($A2&"_Продажи"), 2, ЛОЖЬ)

Формула 2. ВПР и вложенные операторы ЕСЛИ для поиска на нескольких листах

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

=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, НЕВЕРНО)

Где $A2 — это значение поиска (имя элемента), а B$1 — значение ключа (состояние):

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

Вот как использовать функцию ВПР в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Практическое пособие для скачивания

Вас также может заинтересовать

364 комментария к статье "Как использовать функцию ВПР в Excel: расширенные примеры формул"

Здравствуйте, я использую следующий комментарий ВПР, который отлично работает! и действительно мог бы сделать так, чтобы это работало в Hlookup, но оно не работает, есть ли другие параметры для Hlookup?

Любая помощь будет принята с благодарностью?

У меня есть данные SKu на одном листе, и у меня есть данные о sku для Индии на другом листе, на самом деле я хочу узнать количество sku на первом листе. Расположение на первом листе каждого артикула. Пожалуйста, помогите

Первый столбец — это номер позиции

Код MAS 23% 18% 15% 12,50% 10% 7,50% 5% 5%
10100 0,29 0,21 0,19 0,18 0,17 0,16 0,14 0,10
10101 0,29 0,21 0,19 0,18 0,6 0,17 0,19 >10102 0,29 0,21 0,19 0,18 0,17 0,16 0,14 0,10
10103 0,29 0,21 0,19 0,18 0,17 0,16 0,14 0,10

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

Читайте также: