ВПР - это потрясающая и моя любимая функция
Эти таблицы не только упрощают обновление данных, но и упрощают чтение формул! Единственное, что вам нужно сделать, это нажать Ctrl + T перед написанием формулы.
Вернемся к приведенной выше формуле ВПР. На этот раз преобразуйте таблицу товаров и таблицу покупок в таблицу Excel с помощью Ctrl + T с самого начала! Чтобы упростить задачу, дайте каждой таблице понятное имя на вкладке «Работа с таблицами»:
Теперь снова введите VLOOKUP, не делая ничего иначе, чем обычно, ваша формула в C2 теперь =VLOOKUP((@Item),Items,2,0)вместо =VLOOKUP(B2,$E$5:$F$10,2,0)!
Даже если таблица Items находится на другом листе, формула остается той же, а не менее читаемой =VLOOKUP(B2,Items!$A$2:$B$7,2,0).
(@Item) в формуле относится к ячейке в столбце Item этой таблицы (в той же строке, что и формула) и, следовательно, одинаков во всем столбце. А Items относится ко всей таблице элементов (без заголовков). Лучше всего то, что вам не нужно ничего вводить. Если это таблица, Excel поместит эти имена в формулу при выборе ячеек / диапазонов!
Давайте сделаем еще один шаг вперед. Добавьте еще один столбец в таблицу продаж, чтобы рассчитать доход по формуле =(@Price)*(@Qty). Если теперь вы хотите , чтобы вычислить общий доход, формула =SUM(Sales(Revenue)); что действительно легко понять, независимо от того, где находятся данные и сколько строк они охватывают!
Смотреть видео
- ВПР - это потрясающая и моя любимая функция
- Ненавистники ВПР жалуются, что она хрупкая из-за третьего аргумента
- Если форма вашей таблицы поиска изменится, ответы могут измениться
- Один способ обхода - заменить третий аргумент на MATCH.
- Но представьте, что вы выполняете ПОИСКПОЗ для 1000 строк ВПР.
- Превратите вашу таблицу поиска в таблицу перед выполнением ВПР.
- Ссылка на структурированную таблицу будет обработана, если форма таблицы изменится.
- Кроме того, это не требует повторения МАТЧА снова и снова.
- Питер Альберт отправил этот совет
Стенограмма видео
Learn Excel for Podcast, Episode 2003 - Readable References
Не забудьте подписаться на плейлист XL. Я буду подкастировать всю эту книгу.
Хорошо, сегодняшний совет от Питера Альберта. Питер Альберт. Теперь поговорим о ВПР. Я большой поклонник ВПР. Для меня VLOOKUP - это разделительная линия. Если вы можете выполнять ВПР, все остальное в Excel будет для вас легко. Таким образом, ВПР позволяет нам найти цену в этой таблице, и мы поговорим о ВПР более подробно позже.
Так что скопируйте это, и все работает нормально, но я должен вам сказать. Я их видел. Я с ними разговаривал. Я с ними встречался. Есть ненавистники ВПР. Люди, которые ненавидят, когда вы смотрите вверх, и какие еще жалобы на то, что он настолько хрупкий, этот третий аргумент, где мы сказали, что мы хотим третий столбец, что если кто-то позже решит, что нам нужно новое поле здесь, может быть, размер . Хорошо, во-первых, похоже, что это какая-то ошибка, из-за которой Excel не пересчитывает все это. Позвольте мне отменить, отменить, а затем повторить. Вот и все. Это странно, я должен доложить об этом команде Excel, но вы видите, что там, где мы получали цену, теперь она становится цветной, потому что было жестко запрограммировано, что они хотят третий столбец. Хорошо, и что люди делают, чтобы обойти эту безумную штуку с = MATCH.Найдите слово Price в первой строке таблицы, F4,0, и оно скажет нам, что цена на данный момент является четвертой колонкой. Так что они действительно сделают = ВПР. Мы ищем A104 в этой таблице. F4, а затем вместо того, чтобы жестко кодировать число четыре, они делают МАТЧ, и МАТЧ будет привязан к цене. Итак, F4, дважды поставить $ перед 1, и он будет просматривать первую строку таблицы. Упс, дважды F4, запятая, пропущена запятая. Хорошо, нажмите F4, запятую 0 для точного совпадения с совпадением, а затем запятую для точного совпадения с VLOOKUP. Да, да, это отлично работает, и здесь у меня их всего шесть, так что в этом нет ничего страшного.в этой таблице. F4, а затем вместо того, чтобы жестко кодировать число четыре, они делают МАТЧ, и МАТЧ будет привязан к цене. Итак, F4, дважды поставить $ перед 1, и он будет просматривать первую строку таблицы. Упс, дважды F4, запятая, пропущена запятая. Хорошо, нажмите F4, запятую 0 для точного совпадения с совпадением, а затем запятую для точного совпадения с VLOOKUP. Да, да, это отлично работает, и здесь у меня их всего шесть, так что в этом нет ничего страшного.в этой таблице. F4, а затем вместо того, чтобы жестко кодировать число четыре, они делают МАТЧ, и МАТЧ будет привязан к цене. Итак, F4, дважды поставить $ перед 1, и он будет просматривать первую строку таблицы. Упс, дважды F4, запятая, пропущена запятая. Хорошо, нажмите F4, запятую 0 для точного совпадения с совпадением, а затем запятую для точного совпадения с VLOOKUP. Да, да, это отлично работает, и здесь у меня их всего шесть, так что в этом нет ничего страшного.Хорошо, нажмите F4, запятую 0 для точного совпадения с совпадением, а затем запятую для точного совпадения с VLOOKUP. Да, да, это отлично работает, и здесь у меня их всего шесть, так что в этом нет ничего страшного.Хорошо, нажмите F4, запятую 0 для точного совпадения с совпадением, а затем запятую для точного совпадения с VLOOKUP. Да, да, это отлично работает, и здесь у меня их всего шесть, так что в этом нет ничего страшного.
Посмотрите, вставлю ли я новый, он автоматически отрегулирует и продолжит получать цену, но только представьте, если бы у вас была тысяча ВПР, и каждая отдельная ВПР будет повторять это совпадение, чтобы вычислить эти цены в пятом или четвертом столбце. Это ужасно. Таблицы просто решают эту проблему. Итак, вот моя таблица ВПР. Я собираюсь пойти сюда задолго до того, как я что-нибудь сделаю, и нажму CTRL T, чтобы превратить ее в настоящую таблицу. Они будут называть это таблицей 1, а я буду называть ее ProductTable, все одним словом, без пробелов: ProductTable. Итак, теперь у него есть имя. Хорошо, теперь у нас есть таблица с именем ProductTable. Затем мы подходим сюда и говорим, что собираемся сделать = ИНДЕКС этих цен. Какую цену мы хотим? Нам нужен результат совпадения A104 в этих элементах. Точное совпадение, закрытые круглые скобки для ИНДЕКС.Это только одно совпадение. Это не совпадение, а ВПР. Вроде, будет намного быстрее. Скопируйте это. Хорошо, а потом, если мы вставим размер, так что вставляем столбец, размер все продолжает работать, потому что он ищет столбец с именем Price и допустим, что если мы изменим его на List Price, эта формула будет переписана. Так, намного безопаснее, безопаснее.
Хорошо, столько крутых трюков в таблицах. Посмотрите эту книгу Кевина Джонса и Зака Барресса о таблицах Excel. В этой переполненной книге есть все виды трюков и все, что мы подкастируем в августе и сентябре. Плюс много удовольствия. Шутки Excel. Коктейли Excel. Твиты Excel. Приключения Excel. Варенье полноцветное. Проверьте это, купите эту книгу. Я был бы очень признателен.
Хорошо, сегодняшний выпуск. ВПР - потрясающая функция, и это моя любимая функция, но есть ненавистники ВПР, которые жалуются, что она хрупкая из-за этого третьего аргумента, если форма вашей таблицы ВПР изменится, ответы изменятся. Один обходной путь - заменить этот третий аргумент на MATCH, но, черт возьми, представьте, что вы выполняете MATCH для тысячи строк VLOOKUP. Поэтому превратите ВПР в таблицу, прежде чем выполнять ВПР. Ссылки на таблицы структуры будут обработаны, если форма таблицы изменится. К тому же вы не делаете ВПР и совпадение. Всего одно совпадение с ИНДЕКСОМ и ИНДЕКСОМ - это молниеносно.
Спасибо Питеру Роберту за этот совет и спасибо, что заглянули. Увидимся в следующий раз, чтобы посмотреть еще одну трансляцию от.
Скачать файл
Загрузите образец файла здесь: Podcast2003.xlsx
