Как взять данные с другого листа excel

Excel At Excel вып.3: Собираем данные с разных листов ⁠ ⁠

Дано: 22 таблицы унифицированного формата с перечнем помещений от 22 дочерних предприятий.

Задача: сделать сводную таблицу с данными всех 22 таблиц

Итак, решение. Есть три варианта решения данной задачи. Первый — использовать встроенную (начиная с excel 2013) надстройку PowerPivot (об этом методе мы поговорим позднее). Второй — посредством сводной таблицы через несколько диапазонов консолидации. Не люблю этот метод, т.к. такая сводная таблица имеет существенно более ограниченный функционал по сравнению с простой сводной таблицей. Наконец, третий вариант — это создать общую таблицу с использованием функции INDIRECT (ДВССЫЛ). Этому методу и посвятим сегодня наш пост.

Что мы имеем? 22 одинаковых по формату таблицы на отдельных листах и с разным количеством строк в каждой. Соответственно, для построения общей таблицы нам надо решить следующие «проблемы»:

1) как заставить общую таблицу «переключаться» с одного листа данных на другой?;

2) как заставить ее делать это в нужный момент (когда закончились строки на одном листе)?

Начнем со второго вопроса, т.к. ответ на него мы уже знаем. Это циклы, о которых мы подробно говорили в выпуске 2 «Циклы в Excel без VBA». Для решения проблемы достаточно в наш файл добавить лист с перечнем всех обществ, соответствующими названиями листов и количеством подсчетом количества строк на каждом таком листе.

Обратите внимание на формулу в столбце Кол-во записей. Мы могли бы использовать просто COUNTA (СЧЁТА) со ссылкой на каждый лист. Но это потребует времени для «линковки» каждого листа. Плюс мы с вами готовим универсальные решения, которые будут работать в независимости от количества листов.

Именно для этого в функцию COUNTA (СЧЁТА) и внедряется эта чуднАя функция INDIRECT (ДВССЫЛ). Остановимся на ней подробнее.

Функция имеет, по сути, единственный параметр — ref_text (ссылка_на_текст). Что делает эта функция? Всего-навсего преобразует текст в ссылку на ячейку. То есть она преобразует текст, например, «А1» в ссылку и возвращает значение ячейки А1.

Соответственно, зная несложные правила работы с текстом в Excel, мы можем легко сделать текст внутри изменяемым, а значит можем сделать и изменяемыми ссылки, которые будет возвращать функция INDIRECT (ДВССЫЛ).

Разберем пример выше. Функция INDIRECT (ДВССЫЛ) имеет следующий вид:

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

‘Название листа’!A1

Соответственно, в нашем случае у нас неизменен столбец B:B, а вот название листа меняется. Для «автоматизации» формулы заменяем Название листа на ссылку на название листа с данными по соответствующему обществу (столбец Название листа). Получаем следующее:

» ‘ » & A1 & « ‘!B:B «

где все, что подчеркнуто — текст, а выделено жирным — ссылки.

ВАЖНО! Обратите внимание на кавычки и конкатенацию («склеивание») при помощи амперсента. Не забудьте, что текст вносится в кавычках и соединяется со ссылками при помощи символа &.

Получается, что INDIRECT (ДВССЫЛ) получает название листа из ячеек в столбце А:А (Название листа), а диапазон у нас прописан фиксированно текстом (В:В). COUNTA (СЧЁТА) же просто считает количество строк в столбце В:В на соответствующем листа за вычетом заголовка.

Перейдем к формированию общей таблицы. Как вы уже наверное прикинули, у нас будет два вложенных цикла: один — количество обществ, второй — количество записей на листе данных по соответствующему обществу. Соответственно, нам потребуются два вспомогательных столбца (определим их в столбцы А и В). Ровно так же, как мы делали на примере в выпуске 2 «Циклы в Excel без VBA», пишем формулу первого цикла:

=IF(A2=»»;»»;IF(COUNTIF($A$2:A2;A2)=OFFSET(‘Список обществ’!$E$2;A2-1;0);IF(A2+1>COUNTA(‘Список обществ’!A:A)-1;»»;A2+1);A2))

Затем пишем формулу второго цикла:

Далее при помощи уже знакомой нам функции OFFSET (СМЕЩ) заполняем столбцы из таблицы на листе Список обществ.

Осталось подтянуть данные с 22 листов по обществам. Тут нам опять поможет OFFSET (СМЕЩ), но уже в паре с INDIRECT (ДВССЫЛ).

OFFSET (СМЕЩ) передает в формулу название листа, а конструкция B2+1 — номер строки в столбце Е на том листе.

ВАЖНО! Обратите внимание, что OFFSET (СМЕЩ) вкладывается внутрь INDIRECT (ДВССЫЛ), а не наоборот.

Вот, что получается:

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

«22 таблицы унифицированного формата» — мечты, мечты:) В свое время пришлось вести похожую «БД» в екселе. как ни печально, но по факту каждая из 22 организаций пишет и называет по своему. куча несоотвествий, пробелы в конце названия, английские «с» в место русских «с» — этого много, и из-за этого все впр-ы, индекс-поискпозы накрываются медным тазом) Было бы неплохо еще расскрыть тему контроля ввода даных, списки, маски ввода — вот это вот все.

90-ми повеяло, институтом:)

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

Расскажите лучше как убрать кросс-документационную отмену, вымораживает что п****ц.

Мой метод усмирения авиадебоширов⁠ ⁠

Рейс Хургада-Москва. В кабину залетает стюардесса и докладывает: «Там пассажир на 14С буянит, пьянющий, девчонок матом кроет, на замечания не реагирует, требует пересадить в бизнес»

Типичная ситуация для курортных и вахтовых рейсов.

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

Пилотам нельзя выйти из кабины и помочь, только на земле.

Если после первого объявления в салон человек не успокаивается, я делаю следующее:

Узнаю на каком месте сидит авиасвин.🐷

Сообщаю в салон:

«Дамы и господа, говорит капитан. Пассажир на месте 14С создает угрозу безопасности полета. Я буду вынужден произвести посадку в ближайшем аэропорту. Мы прибудем в аэропорт назначения с задержкой в 3-4 часа»

Пассажиры понимают, что опоздают на стыковочные рейсы, автобусы, поезда. Да и терять 3-4 часа из-за пьяной свиньи никому не охота.

В этот момент на дебошира обрушивается шквал конструктивной критики.

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

Надеюсь этот способ поможет кому-то из моих коллег, читателей.

С вами был летчик Миша. Лидер рок-группы SAHALIN. Спасибо за внимание!

Настоящее человеколюбие⁠ ⁠

Зашёл в пятницу вечером в алкомаркет, вижу картину.
Молодая пара лет 25-30. Девушка берёт четыре банки пива из холодильника, а затем идёт к «тёплой полке» и берёт там еще четыре.
Я не понял этого манёвра, ведь в холодильнике были еще холодненькие банки. Парень тоже не понял и спросил, почему девушка не взяла сразу всё из холодильника.
Ответ девушки поднял мне настроение и веру в человечество, наверное, на всю жизнь.
— Ну, понимаешь, мы сейчас придём домой, выпьем по пару баночек. А к тому времени, как мы их допьём, тёплые банки уже охладятся в нашем холодильнике.
Нам нет никакого смысла забирать все холодные банки из магазина. Мы ничего от этого не выиграем. А кто-то другому будет приятно найти холодненькую баночку.
От меня не убудет, а кто-то порадуется.

Сказать, что я охерел, это ничего не сказать. У девчули какой-то запредельный уровень человеколюбия.
Я теперь тоже стал так делать. Пару банок беру из холодильника, остальное беру тёплое. Мелочь, а ведь действительно кому то будет приятно.

Правозащитники забили тревогу из-за засилия мигрантов: «Россия идет по пути Европы» «Этнические анклавы создаются очень легко и быстро»⁠ ⁠

Больше половины детей мигрантов в российских школах не знают ни слова по-русски. Такую печальную статистику озвучил глава СПЧ Валерий Фадеев. Но это только верхушка айсберга. 70 процентов всех трудовых мигрантов, осевших в России, находятся в «серой зоне». Государство не знает, кто они, сколько их, откуда и зачем они приехали. Зато некоторые мигранты свое дело знают твердо: закрытые этнические анклавы, которые приезжие умудряются сколачивать еще со школьной скамьи, стали огромной проблемой для правоохранительных органов.

Фото: Наталья Мущинкина

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

— Среди детей с мигрантской историей от половины до трех четвертей не знают русский язык совсем. Российский закон таков, что все дети должны учиться в школе. Но как их учить, если они не говорят по-русски? – недоумевает Валерий Фадеев.

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

Что со всем этим делать, российское правозащитное сообщество пока не очень-то понимает. «Вопрос надо изучать, обдумывать», — пока формула решения мигрантского вопроса, озвученная Валерием Фадеевым, выглядит не особо обнадеживающей. Хотя кое-какие конкретные идеи в СПЧ уже созрели. Например, обучать детей мигрантов русскому языку на специальных курсах. Бесплатно.

— Не бесплатно! Это будет из бюджета, значит платить будем мы, — добавил в идею Фадеева ложку дегтя глава Национального антикоррупционного комитета Кирилл Кабанов.

Впрочем, позитивные идеи на этом и закончились. Дальше — только закручивание гаек. Валерий Фадеев специально оговорился: не следует такие предложения считать нарушением прав человека. Не знающие русского языка мигранты — вот настоящее нарушение прав и их самих, и всех окружающих.

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

Во-вторых, учителей члены СПЧ тоже решили пожалеть.

Но есть проблема куда серьезнее. Детей мигрантов в классе можно хотя бы пересчитать по головам. А вот сколько у нас в стране тех, кто когда-то приехал на заработки и остался насовсем, — это вообще темный лес. Статистики не знает ни одно ведомство, — рассказал Кирилл Кабанов.

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

Понятна лишь схема, по которой мигранты уже даже не просачиваются, а заплывают мощным потоком в нашу страну без какой-либо «дамбы». Сначала приезжает один труженик, привозит с собой семью. Как только семейство обустраивается на новом месте, весь аул (кишлак, хутор) получает отмашку: можно ехать. В результате целые районы городов, села и деревни в России оказываются под мягкой оккупацией мигрантов. В основном едут из Таджикистана, Киргизии и Узбекистана. Как отметили в СПЧ, эти страны – основные поставщики рабочей силы для большого бизнеса. В первую очередь – для строек.

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

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

— Этнические анклавы создаются очень легко и быстро, а разрушить их уже невозможно. География этих процессов – вся Россия. Мы сейчас идем тем путем, который уже прошла Европа, — заключил Кирилл Кабанов.

«Ведро с крабами» в действии⁠ ⁠

На волне требований предоставить табуретки кассирам в «пятерочке» и в «Красном и белом», обратил внимание, что даже на Пикабу (островке адекватности) огромное количество всякого быдла, которое отстаивает наше исконное «жить в говне как жили предки».

Вот эти вот аргументы: а на заводах работают стоя, а давайте еще дневальному в армии дадим стул, автомеханики и курьеры тоже целый день на ногах…

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

Блин, вы приходите в алкашку около дома, за дешевым пойлом, а требуете к себе отношение, словно вы прилетели в 7* отель в Дубае. Чтобы вас стоя встречали и обслуживали. Что за быдло-замашки?

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

Удавиться за 50 рублей⁠ ⁠

Продаю на Авито прогулочный шар для грызунов за 350 р. Позвонил мужчина, по голосу и манере общения — пенсионер. Договорились о встрече, я назвала полный адрес: улица, дом, номер квартиры. В назначенное время с номера покупателя звонит ребёнок и говорит: "Мы подъехали за шариком". Я прошу позвонить в домофон, она в ответ: мы не знаем, как звонить, мы не знаем куда звонить (мужчина на заднем фоне то же самое повторяет — мы, мол, не умеем пользоваться домофоном). Я объяснила, что сама спуститься не смогу, и рассказала, к какому подъезду подойти, что набрать на домофоне — все вроде поняли. Через три минуты звонок на телефон, говорит мужчина:
— Мы что, не туда подъехали?
Смотрю в окно — стоит незнакомый автомобиль возле подъезда. Говорю, что вижу машину.
— А нам что, самим подниматься? А почему вы сразу не сказали? А какая квартира? Ой, как высоко, я не пойду. — И говорит ребенку: — Иди одна.
Ребёнку лет 7-8 максимум. Он её одну отправил в незнакомый дом к незнакомой тётке. Ну да ладно, как говорится.
Девочка поднялась, я протягиваю ей шарик, а она мне скомканные купюры, и говорит:
— У нас только 300 рублей есть.
И я чот так разозлилась. Отвечаю ей:
— Нет, так не годится. Об этом нужно было заранее предупредить. Сейчас я позвоню дедушке.
Она отвечает: "Ну, хорошо", и убегает обратно. Я в этот момент звоню мужчине:
— Я не согласна продавать товар дешевле, чем указано в объявлении. Вы могли предупредить об этом заранее, я бы не отказалась сделать скидку.
И начинается цирк. Ой, да что же вы за зверь, ребёнок плачет! Я сам объявление не видел, внучка сама его нашла и мне такую цену сказала. Ну что ты плачешь (это уже ребенку), тебя поругали? Ну не переживай, мы найдем другой шарик. Ты этот хочешь? Ну на отнеси ей 400 рублей, только не плачь. Она из-за полтинника будет теперь е**ло воротить, удавится на**й.
После этого предложения я попрощалась и сбросила звонок. Осталась принципиальной тварью, которая отказалась скинуть 50 рублей. А я просто не люблю, когда меня хотят нагреть или надавить на жалость с помощью детей. А потом ещё и матерят.

Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую

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

Даже если вы годами используете функцию ВПР, то с высокой долей вероятности эта статья будет вам полезна и не оставит равнодушным. Я, например, будучи IT-специалистом, а потом и руководителем в IT, пользовался VLOOKUP 15 лет, но разобраться со всеми нюансами довелось только сейчас, когда я на профессиональной основе стал обучать людей Excel.

ВПР — это аббревиатура от вертикального просмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP). ГПР, в противоположность ВПР, производит горизонтальный поиск, однако концепция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни.

Синтаксис

Функция ВПР имеет четыре параметра:

=ВПР( <ЧТО> ; <ГДЕ> ; <НОМЕР_СТОЛБЦА> [;<ОТСОРТИРОВАНО>] ), тут:

<ЧТО> — искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев);

<ГДЕ> — ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра <ЧТО>;

<НОМЕР_СТОЛБЦА> — номер столбца в диапазоне, из которого будет возвращено значение;

<ОТСОРТИРОВАНО> — это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона <ГДЕ>. В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.

Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0) либо же о диапазонном просмотре (ИСТИНА или 1).

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

Как же конкретно работает формула ВПР

• Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
• Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром <ЧТО>, в противном случае возвращается код ошибки #Н/Д (#N/A).

Схемы работы формул

ВПР тип I

ВПР тип II

Следствия для формул вида I

1. Формулы можно использовать для распределения значений по диапазонам.
2. Если первый столбец <ГДЕ> содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
3. Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
4. Данный вид вернёт ошибку #Н/Д, только если не найдёт значения меньше или равное искомому.
5. Понять, что формула возвращает неправильные значения, в случае если ваш массив не отсортирован, довольно затруднительно.

Следствия для формул вида II

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

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

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

• мне нужен более мощный компьютер;
• мне нужна более быстрая формула, например, многие знают про ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая якобы быстрее на жалкие 5–10%.

И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Не на 57%, а на 5 700%. Данный факт я проверил вполне надёжно.

Секрет такой быстрой работы кроется в том, что на отсортированном массиве можно применять чрезвычайно эффективный алгоритм поиска, который носит название бинарного поиска (метод деления пополам, метод дихотомии). Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще. То же самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР (LOOKUP), которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus 1-2-3.

Недостатки формулы

Недостатки ВПР очевидны: во-первых, она ищет только в первом столбце указанного массива, а во-вторых, только справа от данного столбца. А как вы понимаете, вполне может случиться так, что столбец, содержащий необходимую информацию, окажется слева от столбца, в котором мы будем искать. Этого недостатка лишена уже упомянутая связка формул ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), что делает её наиболее гибким решением по извлечению данных из таблиц в сравнении с ВПР (VLOOKUP).

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

Диапазонный поиск

Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа.

Поиск текстовых строк

Безусловно, ВПР ищет не только числа, но и текст. При этом надо принимать во внимание, что регистр символов формула не различает. Если использовать символы подстановки, то можно организовать нечёткий поиск. Есть два символа подстановки: «?» — заменяет один любой символ в текстовой строке, «*» — заменяет любое количество любых символов.

Борьба с пробелами

Часто поднимается вопрос, как решить проблему лишних пробелов при поиске. Если справочную таблицу ещё можно вычистить от них, то первый параметр формулы ВПР не всегда зависит от вас. Поэтому если риск засорения ячеек лишними пробелами присутствует, то можно применять для очистки функции СЖПРОБЕЛЫ (TRIM).

Разный формат данных

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

=ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если D7 содержит текст, а таблица — числа;

=ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — и наоборот.

Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:

• Двойное отрицание —D7.
• Умножение на единицу D7*1.
• Сложение с нулём D7+0.
• Возведение в первую степень D7^1.

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

Как подавить выдачу #Н/Д

Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).

Например: =ЕСЛИОШИБКА( ВПР(D7; Продукты!$A$2:$C$5; 3; 0); «»).

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

Массив <ГДЕ>

Часто забывают ссылку массива сделать абсолютной, и при протягивании массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5.

Хорошей идеей является размещение справочного массива на отдельном листе рабочей книги. Не путается под ногами, да и сохраннее будет.

Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона.

Многие пользователи при указании массива используют конструкцию вида A:C, указывая столбцы целиком. Этот подход имеет право на существование, так как вы избавлены от необходимости отслеживать тот факт, что ваш массив включает все необходимые строки. Если вы добавите строки на лист с первоначальным массивом, то диапазон, указанный как A:C, не придётся корректировать. Безусловно, эта синтаксическая конструкция заставляет Excel проводить несколько большую работу, чем при точном указании диапазона, но данными накладными расходами можно пренебречь. Речь идёт о сотых долях секунды.

Ну и на грани гениальности — оформить массив в виде умной таблицы.

Использование функции СТОЛБЕЦ для указания колонки извлечения

Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов. При этом все ВПР-формулы будут одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого параметра координата столбца абсолютная.

Создание составного ключа через &»|»&

Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем «Код»), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.

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

Как взять данные с другого листа excel

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

Значения ВПР с другого листа

Этот раздел покажет вам, как использовать vlookup значения из другого листа в Excel.

Общая формула

=VLOOKUP( lookup_value , sheet_range , col_index ,[ range_lookup ])

аргументы

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

Нажмите, чтобы узнать больше о Функция ВПР.

В этом случае мне нужно найти значения в диапазоне B3: C14 рабочего листа с именем «Продажи», и верните соответствующие результаты в Итоги рабочий лист.

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

=VLOOKUP($B5,Sales!B3:C14,2,0)

Ноты:

• B5 ссылка на ячейку, содержащую искомое значение;
• Продажа это имя листа, из которого вы будете искать значение;
• B3: C14 содержит ли диапазон столбец значений поиска и столбец значений результатов;
• 2 означает, что значение результата находится во втором столбце диапазона B3: C14;
• 0 здесь означает, что функция VLOOKUP вернет точное совпадение. Если точное совпадение не может быть найдено, будет возвращено значение ошибки # Н / Д.

2. Затем перетащите Ручка заполнения вниз, чтобы получить все результаты.

Значения ВПР из другой книги

Предполагая, что существует книга Wokbook с названием «Отчет о продажах», для прямого поиска значений на конкретном листе этой книги, даже если она закрыта, выполните следующие действия.

Общая формула

=VLOOKUP( lookup_value , [workbook]sheet!range , col_index ,[ range_lookup ])

аргументы

• Lookup_value (обязательно): значение, которое вы ищете. Он должен быть в первом столбце диапазона листов.
• [workbook]sheet!range (обязательно): диапазон ячеек на листе в определенной книге, который содержит два или более столбца, в которых находятся столбец значения поиска и столбец значения результата.
• Col_index (обязательно): конкретный номер столбца (это целое число) table_array, из которого вы вернете совпадающее значение.
• Range_lookup (необязательно): это логическое значение, которое определяет, будет ли функция ВПР возвращать точное или приблизительное совпадение.

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

=VLOOKUP($B5,'[Sales report.xlsx]Sales’!B3:C14,2,0)

Примечание: Если точное совпадение не может быть найдено, функция ВПР вернет значение ошибки # Н / Д.

2. Затем перетащите маркер заполнения вниз, чтобы применить формулу к другим ячейкам и получить все результаты. Смотрите скриншот:

Связанная функция

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

Родственные формулы

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

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

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

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

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

Лучшие инструменты для работы в офисе

Kutools for Excel — Помогает вам выделиться из толпы

Хотите быстро и качественно выполнять свою повседневную работу? Kutools for Excel предлагает 300 мощных расширенных функций (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.

Разбор функции ДВССЫЛ (INDIRECT) на примерах

На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку — в полноценную ссылку. Т.е. если нам нужно сослаться на ячейку А1, то мы можем либо привычно сделать прямую ссылку (ввести знак равно в D1, щелкнуть мышью по А1 и нажать Enter), а можем использовать ДВССЫЛ для той же цели:

Обратите внимание, что аргумент функции — ссылка на А1 — введен в кавычках, поэтому что, по сути, является здесь текстом.

«Ну ОК», — скажете вы. «И что тут полезного?».

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

Пример 1. Транспонирование

пазон в горизонтальный (транспонировать). Само-собой, можно использовать специальную вставку или функцию ТРАНСП (TRANSPOSE) в формуле массива, но можно обойтись и нашей ДВССЫЛ:

Логика проста: чтобы получить адрес очередной ячейки, мы склеиваем спецсимволом «&» букву «А» и номер столбца текущей ячейки, который выдает нам функция СТОЛБЕЦ (COLUMN) .

Обратную процедуру лучше проделать немного по-другому. Поскольку на этот раз нам нужно формировать ссылку на ячейки B2, C2, D2 и т.д., то удобнее использовать режим ссылок R1C1 вместо классического «морского боя». В этом режиме наши ячейки будут отличаться только номером столбца: B2=R1C 2 , C2=R1C 3 , D2=R1C 4 и т.д.

Тут на помощь приходит второй необязательный аргумент функции ДВССЫЛ. Если он равен ЛОЖЬ (FALSE) , то можно задавать адрес ссылки в режиме R1C1. Таким образом, мы можем легко транспонировать горизонтальный диапазон обратно в вертикальный:

Пример 2. Суммирование по интервалу

Мы уже разбирали один способ суммирования по окну (диапазону) заданного размера на листе с помощью функции СМЕЩ (OFFSET) . Подобную задачу можно решить и с помощью ДВССЫЛ. Если нам нужно суммировать данные только из определенного диапазона-периода, то можно склеить его из кусочков и превратить затем в полноценную ссылку, которую и вставить внутрь функции СУММ (SUM) :

Пример 3. Выпадающий список по умной таблице

Иногда Microsoft Excel не воспринимает имена и столбцы умных таблиц как полноценные ссылки. Так, например, при попытке создать выпадающий список (вкладка Данные — Проверка данных) на основе столбца Сотрудники из умной таблицы Люди мы получим ошибку:

Если же «обернуть» ссылку нашей функцией ДВССЫЛ, то Excel преспокойно ее примет и наш выпадающий список будет динамически обновляться при дописывании новых сотрудников в конец умной таблицы:

Пример 4. Несбиваемые ссылки

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

Если ставить обычные ссылки (в первую зеленую ячейку ввести =B2 и скопировать вниз), то потом при удалении, например, Даши мы получим в соответствующей ей зеленой ячейке ошибку #ССЫЛКА! (#REF!). В случае применения для создания ссылок функции ДВССЫЛ такой проблемы не будет.

Пример 5. Сбор данных с нескольких листов

Предположим, что у нас есть 5 листов с однотипными отчетами от разных сотрудников (Михаил, Елена, Иван, Сергей, Дмитрий):

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

Собрать данные со всех листов (не просуммировать, а положить друг под друга «стопочкой») можно всего одной формулой:

Как видите, идея та же: мы склеиваем ссылку на нужную ячейку заданного листа, а ДВССЫЛ превращает ее в «живую». Для удобства, над таблицей я добавил буквы столбцов (B,C,D), а справа — номера строк, которые нужно взять с каждого листа.

Подводные камни

При использовании ДВССЫЛ (INDIRECT) нужно помнить про ее слабые места: