Впр расшифровка в excel: Функция (формула) «ВПР» в программе «Excel»

Функция ВПР в Excel для чайников

Автор Антон Андронов На чтение 5 мин Опубликовано 01.04.2015

Многие наши ученики говорили нам, что очень хотят научиться использовать функцию ВПР (VLOOKUP) в Microsoft Excel. Функция ВПР – это очень полезный инструмент, а научиться с ним работать проще, чем Вы думаете. В этом уроке основы по работе с функцией ВПР разжеваны самым доступным языком, который поймут даже полные «чайники». Итак, приступим!

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

Содержание

1. Что такое ВПР?
2. Добавляем аргументы
3. Как работает функция ВПР?
4. Другой пример

Что такое ВПР?

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

Сейчас мы найдём при помощи ВПР цену товара Photo frame. Вероятно, Вы и без того видите, что цена товара $9.99, но это простой пример. Поняв, как работает функция ВПР, Вы сможете использовать ее в более сложных таблицах, и тогда она окажется действительно полезной.

Мы вставим формулу в ячейку E2, но Вы можете использовать любую свободную ячейку. Как и с любой формулой в Excel, начинаем со знака равенства (=). Далее вводим имя функции. Аргументы должны быть заключены в круглые скобки, поэтому открываем их. На этом этапе у Вас должно получиться вот что:

=VLOOKUP(
=ВПР(

Добавляем аргументы

Теперь добавим аргументы. Аргументы сообщают функции ВПР, что и где искать.

Первый аргумент – это имя элемента, который Вы ищите, в нашем примере это Photo frame. Так как аргумент текстовый, мы должны заключить его в кавычки:

=VLOOKUP("Photo frame"
=ВПР("Photo frame"

Второй аргумент – это диапазон ячеек, который содержит данные. В нашем случае данные содержатся в диапазоне A2:B16. Как и с любой другой функцией Excel, Вы должны вставить разделитель между аргументами (запятая в англоязычной версии Excel или точка с запятой – в русифицированной версии).

=VLOOKUP("Photo frame",A2:B16
=ВПР("Photo frame";A2:B16

Важно помнить, что ВПР всегда ищет в первом левом столбце указанного диапазона. В этом примере функция будет искать в столбце A значение Photo frame. Иногда Вам придётся менять столбцы местами, чтобы нужные данные оказались в первом столбце.

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

=VLOOKUP("Photo frame",A2:B16,2
=ВПР("Photo frame";A2:B16;2

Четвёртый аргумент сообщает функции ВПР, нужно искать точное или приблизительное совпадение. Значением аргумента может быть TRUE (ИСТИНА) или FALSE (ЛОЖЬ). Если TRUE (ИСТИНА), формула будет искать приблизительное совпадение. Данный аргумент может иметь такое значение, только если первый столбец содержит данные, упорядоченные по возрастанию. Так как мы ищем точное совпадение, то наш четвёртый аргумент будет равен FALSE (ЛОЖЬ). На этом аргументы заканчиваются, поэтому закрываем скобки:

=VLOOKUP("Photo frame",A2:B16,2,FALSE)
=ВПР("Photo frame";A2:B16;2;ЛОЖЬ)

Готово! После нажатия Enter, Вы должны получить ответ: 9. 99.

Как работает функция ВПР?

Давайте разберёмся, как работает эта формула. Первым делом она ищет заданное значение в первом столбце таблицы, выполняя поиск сверху вниз (вертикально). Когда находится значение, например, Photo frame, функция переходит во второй столбец, чтобы найти цену.

ВПР – сокращение от Вертикальный ПРосмотр, VLOOKUP – от Vertical LOOKUP.

Если мы захотим найти цену другого товара, то можем просто изменить первый аргумент:

=VLOOKUP("T-shirt",A2:B16,2,FALSE)
=ВПР("T-shirt";A2:B16;2;ЛОЖЬ)

или:

=VLOOKUP("Gift basket",A2:B16,2,FALSE)
=ВПР("Gift basket";A2:B16;2;ЛОЖЬ)

Другой пример

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

Чтобы определить категорию, необходимо изменить второй и третий аргументы в нашей формуле. Во-первых, изменяем диапазон на A2:C16, чтобы он включал третий столбец. Далее, изменяем номер столбца на 3, поскольку категории содержатся в третьем столбце.

=VLOOKUP("Gift basket",A2:C16,3,FALSE)
=ВПР("Gift basket";A2:C16;3;ЛОЖЬ)

Когда Вы нажмёте Enter, то увидите, что товар Gift basket находится в категории Gifts.

Если хотите попрактиковаться, проверьте, сможете ли Вы найти данные о товарах:

• Цену coffee mug
• Категорию landscape painting
• Цену serving bowl
• Категорию scarf

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

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://www.gcflearnfree.org/excel-tips/how-to-use-excels-vlookup-function/full
Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Функция ВПР в Excel для чайников и не только

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

Очень удобная и часто используемая. Т.к. сопоставить вручную диапазоны с десятками тысяч наименований проблематично.

Как пользоваться функцией ВПР в Excel

Допустим, на склад предприятия по производству тары и упаковки поступили материалы в определенном количестве.

Стоимость материалов – в прайс-листе. Это отдельная таблица.

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

Алгоритм действий:

1. Приведем первую таблицу в нужный нам вид. Добавим столбцы «Цена» и «Стоимость/Сумма». Установим денежный формат для новых ячеек.
2. Выделяем первую ячейку в столбце «Цена». В нашем примере – D2. Вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или нажав комбинацию горячих клавиш SHIFT+F3. В категории «Ссылки и массивы» находим функцию ВПР и жмем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и выбрать из выпадающего списка «Ссылки и массивы».
3. Откроется окно с аргументами функции. В поле «Искомое значение» — диапазон данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен найти во второй таблице.
4. Следующий аргумент – «Таблица». Это наш прайс-лист. Ставим курсор в поле аргумента. Переходим на лист с ценами. Выделяем диапазон с наименованием материалов и ценами. Показываем, какие значения функция должна сопоставить.
5. Чтобы Excel ссылался непосредственно на эти данные, ссылку нужно зафиксировать. Выделяем значение поля «Таблица» и нажимаем F4. Появляется значок $.
6. В поле аргумента «Номер столбца» ставим цифру «2». Здесь находятся данные, которые нужно «подтянуть» в первую таблицу. «Интервальный просмотр» — ЛОЖЬ. Т.к. нам нужны точные, а не приблизительные значения.

Нажимаем ОК. А затем «размножаем» функцию по всему столбцу: цепляем мышью правый нижний угол и тянем вниз. Получаем необходимый результат.

Теперь найти стоимость материалов не составит труда: количество * цену.

Функция ВПР связала две таблицы. Если поменяется прайс, то и изменится стоимость поступивших на склад материалов (сегодня поступивших). Чтобы этого избежать, воспользуйтесь «Специальной вставкой».

1. Выделяем столбец со вставленными ценами.
2. Правая кнопка мыши – «Копировать».
3. Не снимая выделения, правая кнопка мыши – «Специальная вставка».
4. Поставить галочку напротив «Значения». ОК.

Формула в ячейках исчезнет. Останутся только значения.



Быстрое сравнение двух таблиц с помощью ВПР

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

1. В старом прайсе делаем столбец «Новая цена».
2. Выделяем первую ячейку и выбираем функцию ВПР. Задаем аргументы (см. выше). Для нашего примера: . Это значит, что нужно взять наименование материала из диапазона А2:А15, посмотреть его в «Новом прайсе» в столбце А. Затем взять данные из второго столбца нового прайса (новую цену) и подставить их в ячейку С2.

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

Функция ВПР в Excel с несколькими условиями

До сих пор мы предлагали для анализа только одно условие – наименование материала. На практике же нередко требуется сравнить несколько диапазонов с данными и выбрать значение по 2, 3-м и т.д. критериям.

Таблица для примера:

Предположим, нам нужно найти, по какой цене привезли гофрированный картон от ОАО «Восток». Нужно задать два условия для поиска по наименованию материала и по поставщику.

Дело осложняется тем, что от одного поставщика поступает несколько наименований.

1. Добавляем в таблицу крайний левый столбец (важно!), объединив «Поставщиков» и «Материалы».
2. Таким же образом объединяем искомые критерии запроса:
3. Теперь ставим курсор в нужном месте и задаем аргументы для функции: . Excel находит нужную цену.

Рассмотрим формулу детально:

1. Что ищем.
2. Где ищем.
3. Какие данные берем.

Функция ВПР и выпадающий список

Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.

Сначала сделаем раскрывающийся список:

1. Ставим курсор в ячейку Е8, где и будет этот список.
2. Заходим на вкладку «Данные». Меню «Проверка данных».
3. Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.
4. Когда нажмем ОК – сформируется выпадающий список.

Теперь нужно сделать так, чтобы при выборе определенного материала в графе цена появлялась соответствующая цифра. Ставим курсор в ячейку Е9 (где должна будет появляться цена).

1. Открываем «Мастер функций» и выбираем ВПР.
2. Первый аргумент – «Искомое значение» — ячейка с выпадающим списком. Таблица – диапазон с названиями материалов и ценами. Столбец, соответственно, 2. Функция приобрела следующий вид: .
3. Нажимаем ВВОД и наслаждаемся результатом.

Изменяем материал – меняется цена:

Скачать пример функции ВПР в Excel

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

Зашифровать и расшифровать шифр Цезаря в Excel с помощью Power Query

Зашифровать и расшифровать шифр Цезаря в Excel с помощью Power Query

За последние несколько месяцев я заинтересовался криптографией. Криптография — это искусство написания и разгадывания ключей. Часто решение подсказок включает в себя расшифровку какого-то скрытого сообщения. Шифровать и расшифровывать сообщения очень весело.

Шифр ​​— это последовательность шагов, предпринимаемых для шифрования или расшифровки сообщения, которое вы, возможно, захотите скрыть. Одним из первых известных шифров является шифр Цезаря. Юлий Цезарь использовал это, чтобы скрыть военные сообщения. Это базовая форма шифрования, в которой буквы просто сдвигаются на 3 назад по алфавиту. Теперь дети младшего школьного возраста с легкостью взламывают такие коды. Тем не менее, это привело к более безопасным шифрованиям, таким как шифр Виженера и ROT13.

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

Поиск в Google дал несколько хороших результатов, однако они были довольно старыми и не использовали функции Excel Power Tool. Итак, чтобы модернизировать существующие ресурсы, я создал шифр Цезаря, используя Excel и Power Query Excel в Office 365. В этой модели вы можете как шифровать, так и расшифровывать текстовую строку, используя сдвиг алфавита.

Вы можете скачать модель Excel в конце этого поста.

Шифрование с помощью шифра сдвига в Excel

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

Первая таблица Text, в которую мы вводим текст, который вы хотите зашифровать.

Во второй таблице вы определяете количество букв для сдвига текста. Что касается расшифровки, это настроено на сдвиг вперед или вверх по алфавиту. Что касается шифрования, он настроен на возврат вниз по алфавиту.

После ввода данных для шифрования текста дважды выберите Обновить ВСЕ на ленте данных.

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

Шифр ​​Цезаря в Excel. Как это работает

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

В столбцах A:B находится наша справочная таблица. Внизу по строкам столбца А находится каждая буква алфавита. Столбец B использует функцию OFFSET для создания реструктурированного алфавита на основе сдвига. В этом примере у нас есть 5 сдвигов, поэтому f становится a и так далее.

В столбце E есть таблица, возвращаемая Power Query. мы создали эту таблицу, загрузив таблицу Text из входных данных в запрос питания при обновлении. Затем он разбивает текст по символам и перемещает его из столбца в строку. Каждый символ текста теперь находится в ячейке, идущей вниз по столбцу. Эта таблица загружается в Excel.

Столбец G, Возврат, затем выполняет ВПР, ссылаясь на таблицу Алфавита, возвращая сдвинутые буквы. Эта функция ВПР комбинируется с другими функциями для учета пробелов, знаков препинания и ошибок. Эта же таблица также загружается в Power Query (при втором обновлении). В power query преобразования переносят текст из строк в столбцы, а затем объединяют все столбцы вместе. Затем преобразованная таблица загружается обратно в Excel в качестве выходной таблицы.

Модель также имеет встроенную кассу. Столбцы J и K меняют сдвиг, используемый в алфавите. Затем он используется для создания контрольного столбца (столбец m) с использованием ВПР. Затем эта таблица загружается в power query, где она транспонируется, а столбцы объединяются для возврата контрольной выходной таблицы на основной лист.

Шифр ​​Цезаря в Excel-модели Ограничения

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

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

Учись и зарабатывай

Расшифруй следующий фрагмент зашифрованного текста. Комментарий ниже со скрытым сообщением

extkg tgw xtkg hyyxk : 50% vtla wblvhngm hg tgr mktbgbgz vhnklxl yhk hger 10 lmxxf    

(возможно, вам придется попробовать несколько смен, прежде чем вы найдете правильный ответ)

, комментарии или более эффективные шаги, пожалуйста, напишите мне комментарий ниже. Это тоже может быть наградой!!!!!

Подпишитесь на мою рассылку – Не волнуйтесь, я не буду спамить. Только полезные советы и рекомендации по Excel и Power BI в папку «Входящие»   с действиями Earn and Learn. ЗАРЕГИСТРИРУЙТЕСЬ СЕЙЧАС

Теперь обучение в Excel Club и наши мероприятия STEEM «Учись и зарабатывай» имеют ценность.

Мы являемся первым в мире блогом Excel, PowerBI и DAX, где вы можете зарабатывать, пока учитесь.

Узнать больше и начать зарабатывать, изучая Excel и Power BI

Нравится:

Нравится Загрузка…

Защита и безопасность в Excel

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Больше…Меньше

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

Предупреждение: 

• Если вы забудете или потеряете свой пароль, Microsoft не сможет восстановить его для вас.

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

• Защита на уровне рабочего листа не предназначена для обеспечения безопасности. Это просто не позволяет пользователям изменять заблокированные ячейки на листе.

Ниже приведены различные варианты защиты данных Excel:

• Уровень файла : это относится к возможности заблокировать файл Excel, указав пароль, чтобы пользователи не могли его открыть или изменить. Здесь у вас есть два варианта:

  • Шифрование файла : При выборе этого параметра вы указываете пароль и блокируете файл Excel. Это предотвращает открытие файла другими пользователями. Дополнительные сведения см. в разделе Защита файла Excel.

  • Установка пароля для открытия или изменения файла : Вы указываете пароль для открытия или изменения файла. Используйте этот параметр, когда вам нужно предоставить доступ только для чтения или редактирования другим пользователям. Дополнительные сведения см. в разделе Защита файла Excel.

  • Пометить как окончательный : Используйте этот параметр, если вы хотите пометить файл Excel как окончательный вариант и предотвратить дальнейшие изменения другими пользователями. Дополнительные сведения см. в статье Добавление или удаление защиты документа, книги или презентации.

  • Ограничение доступа : Если в вашей организации настроены разрешения с использованием управления правами на доступ к данным (IRM), вы можете применить к своему документу любое доступное разрешение IRM. Дополнительные сведения см. в статье Добавление или удаление защиты документа, книги или презентации.

  • Цифровая подпись : Вы можете добавлять цифровые подписи в файл Excel. Дополнительные сведения см. в статье Добавление или удаление цифровой подписи в файлах Office.

    Примечание. Чтобы добавить цифровую подпись, необходим действующий сертификат центра сертификации (ЦС).

• Уровень рабочей книги : Вы можете заблокировать структуру своей рабочей книги, указав пароль. Блокировка структуры рабочей книги не позволяет другим пользователям добавлять, перемещать, удалять, скрывать и переименовывать рабочие листы. Дополнительные сведения о защите книг см. в статье Защита книги.

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

Какой уровень защиты следует использовать?

• org/ListItem»>

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

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

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

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

• org/ListItem»>

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

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