Впр что это эксель: инструкция на примере / Skillbox Media

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

Функция ВПР в Excel

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

• Пошаговое объяснение аргументов
• Синтаксис и аргументы
• Примеры ВПР
  Точное совпадение против приблизительного совпадения   |   ВПР с несколькими условиями
• ВПР Распространенные ошибки и решения
  # Н / Д ошибка   |   #ЗНАЧ ошибка   |   #REF-ошибка   |   Неверное значение

Пошаговое объяснение аргументов

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

Шаг 1: Запустите функцию ВПР

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

=VLOOKUP(

Шаг 2. Укажите значение поиска

Во-первых, укажите искомое значение (именно то, что вы ищете) в функции ВПР. Здесь я ссылаюсь на ячейку G6, которая содержит определенный идентификационный номер 1005.

=VLOOKUP(G6

Внимание: значение поиска должно находиться в первом столбце диапазона данных.

Шаг 3: Укажите массив таблиц

Затем укажите диапазон ячеек, содержащих как искомое значение, так и значение, которое вы хотите вернуть. В данном случае я выбираю диапазон B6:E12. Теперь формула выглядит следующим образом:

=VLOOKUP(G6,B6:E12

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

=VLOOKUP(G6,$B$6:$E$12

Шаг 4: Укажите столбец, из которого вы хотите вернуть значение

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

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

=VLOOKUP(G6,B6:E12,4

Шаг 5. Найдите приблизительное или точное совпадение

Наконец, определите, ищете ли вы приблизительное совпадение или точное совпадение.

• Чтобы найти точное совпадение, вам нужно использовать НЕПРАВДА как последний аргумент.
• Чтобы найти приблизительное совпадение, Используйте ИСТИНА в качестве последнего аргумента или просто оставьте поле пустым.

В этом примере я использую FALSE для точного соответствия. Теперь формула выглядит так:

=VLOOKUP(G6,B6:E12,4,FALSE

Нажмите клавишу Enter, чтобы получить результат

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

Синтаксис и аргументы

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

• Искомое_значение (обязательно): значение (действительное значение или ссылка на ячейку), которое вы ищете. Помните, что это значение должно быть в первом столбце table_array.
• Таблица_массив (обязательно): диапазон ячеек содержит как столбец искомого значения, так и столбец возвращаемого значения.
• Col_index (обязательно): целое число представляет номер столбца, содержащего возвращаемое значение. Он начинается с номера 1 для крайнего левого столбца table_array.
• Range_lookup (необязательно): логическое значение, определяющее, хотите ли вы, чтобы функция ВПР находила приблизительное или точное совпадение.
  • Примерное совпадение — Установите этот аргумент в ИСТИНА, 1 или оставь это пустой.
    Важнo: чтобы найти приблизительное совпадение, значения в первом столбце table_array должны быть отсортированы в порядке возрастания на случай, если функция ВПР вернет неверный результат.
  • Точное совпадение — Установите этот аргумент в НЕПРАВДА or 0.

Примеры

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

Пример 1. Точное и приблизительное соответствие в функции ВПР

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

Точное совпадение в ВПР

В этом примере я собираюсь найти соответствующие имена на основе оценок, перечисленных в диапазоне E6: E8, поэтому я ввожу следующую формулу в ячейку F6 и перетаскиваю маркер автозаполнения вниз к F8. В этой формуле последний аргумент указывается как НЕПРАВДА выполнить поиск точного совпадения.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

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

Внимание: Здесь я заблокировал массив таблиц ($B$6:$C$12) в функции ВПР, чтобы быстро сослаться на последовательный набор данных для нескольких значений поиска.

Примерное совпадение в ВПР

Все еще используя приведенный выше пример, если вы измените последний аргумент на ИСТИНА, ВПР выполнит приблизительный поиск соответствия. Если совпадений не найдено, будет найдено следующее наибольшее значение, которое меньше искомого значения, и возвращен соответствующий результат.

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

Поскольку оценка 98 не существует, функция ВПР находит следующее наибольшее значение, меньшее 98, то есть 95, и возвращает название оценки 95 как ближайший результат.

Заметки:

• В этом случае приблизительного совпадения значения в первом столбце table_array должны быть отсортированы в порядке возрастания. В противном случае функция ВПР может вернуть неверное значение.
• Здесь я заблокировал массив таблиц ($B$6:$C$12) в функции ВПР, чтобы быстро сослаться на согласованный набор данных по нескольким значениям поиска.

Пример 2. Использование функции ВПР с несколькими критериями

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

Шаг 1. Добавьте вспомогательный столбец для объединения значений из столбцов поиска.

В этом случае нам нужно создать вспомогательный столбец для объединения значений из Имя и фамилия столбец и Кафедра колонка.

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

   =C6&" "&D6

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

   • C6 это первое имя Имя и фамилия колонка для присоединения, D6 является первым отделением Кафедра столбец, чтобы присоединиться.
   • Значения этих двух ячеек объединяются пробелом между ними.
3. Выберите эту ячейку результата, затем перетащите Ручка автозаполнения вниз, чтобы применить эту формулу к другим ячейкам в том же столбце.
   

Шаг 2: Примените функцию ВПР с заданными критериями.

Выберите ячейку, в которую вы хотите вывести результат (здесь я выбираю I7), введите следующую формулу в поле Панель формул, а затем нажмите Enter.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

Результат

Заметки:

• Вспомогательный столбец должен использоваться как первый столбец диапазона данных.
• Теперь столбец зарплаты является пятым столбцом диапазона данных, поэтому мы используем число 5 как индекс столбца в формуле.
• Нам нужно присоединиться к критериям в I5 и I6 (I5& «»&I6) таким же образом, как вспомогательный столбец, и используйте объединенное значение в качестве искомое_значение аргумент в формуле.
• Вы также можете поместить два условия непосредственно в аргумент искомое_значение и разделить их пробелом (если условия текстовые, не забудьте заключить их в двойные кавычки).

  =VLOOKUP("Albee IT",B6:F12,5,FALSE)

• Лучшая альтернатива — поиск по нескольким критериям за секунды

  Наблюдения и советы этой статьи мы подготовили на основании опыта команды Поиск по нескольким условиям особенность Kutools for Excel может помочь вам легко искать по нескольким критериям за считанные секунды. Получите 30-дневную бесплатную пробную версию прямо сейчас!

Распространенные ошибки ВПР и решения

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

#Н/Д возвращается ошибка

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

Причина 1: значение поиска не находится в первом столбце table_array

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

Как показано на скриншоте ниже, я хочу вернуть имя на основе данной должности. Здесь искомое значение (менеджер по сбыту) находится во втором столбце table_array, а возвращаемое значение находится слева от столбца подстановки, поэтому функция ВПР возвращает ошибку #Н/Д.

Решения

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

• Переставить столбцы

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

• Используйте функции ИНДЕКС и ПОИСКПОЗ вместе

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

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

• Используйте функцию XLOOKUP (доступна в Excel 365, Excel 2021 и более поздних версиях).

  =XLOOKUP(F6,C6:C12,B6:B12)

Причина 2: значение поиска не найдено в столбце поиска (точное совпадение)

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

Как показано в приведенном ниже примере, мы собираемся найти имя на основе заданной оценки 98 в E6. Однако такой оценки нет в первом столбце диапазона данных, поэтому функция ВПР возвращает результат ошибки #Н/Д.

Решения

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

• Если вы хотите, чтобы функция ВПР искала следующее наибольшее значение искомого значения, измените последний аргумент НЕПРАВДА (точное совпадение) с ИСТИНА (приблизительное совпадение). Для получения дополнительной информации см. Пример 1. Сравнение точного и приблизительного совпадений с использованием функции ВПР.
• Чтобы избежать изменения последнего аргумента и получить напоминание в случае, если искомое значение не найдено, вы можете включить функцию ВПР в функцию ЕСЛИОШИБКА:

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

Причина 3: искомое значение меньше наименьшего значения в столбце поиска (приблизительное совпадение)

Как показано на снимке экрана ниже, вы выполняете приблизительный поиск соответствия. Искомое значение (в данном случае идентификатор 1) меньше наименьшего значения 2 в столбце подстановки, поэтому функция ВПР возвращает ошибку #Н/Д.

Решения

Вот два решения для вас.

• Убедитесь, что значение поиска больше или равно наименьшему значению в столбце поиска.
• Если вы хотите, чтобы Excel напоминал вам, что искомое значение не найдено, просто вложите функцию ВПР в функцию ЕСЛИОШИБКА следующим образом:

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

Причина 4: Числа отформатированы как текст

Как вы можете видеть на снимке экрана ниже, результат ошибки #N/A в этом примере возникает из-за несоответствия типа данных между ячейкой поиска (G6) и столбцом поиска (B6:B12) исходной таблицы. Здесь значение в G6 — это число, а значения в диапазоне B6:B12 — это числа, отформатированные как текст.

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

Решения

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

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

• Примените удобный инструмент для пакетного преобразования между текстом и числом

  Наблюдения и советы этой статьи мы подготовили на основании опыта команды Преобразование текста в число особенность Kutools for Excel помогает вам легко преобразовать диапазон ячеек из текста в число и наоборот. Получите 30-дневную бесплатную пробную версию прямо сейчас!

Причина 5: table_array не является постоянным при перетаскивании формулы ВПР в другие ячейки

Как показано на снимке экрана ниже, в E6 и E7 есть два значения поиска. Получив первый результат в F6, перетащите формулу ВПР из ячейки F6 в F7, возвращается результат ошибки #Н/Д. Это связано с тем, что ссылки на ячейки (B6:C12) по умолчанию являются относительными и корректируются по мере продвижения вниз по строкам. Массив таблицы был перемещен вниз к B7:C13, который больше не содержит индекса поиска 73.

Решения

Вам нужно заблокировать массив таблиц, чтобы он оставался постоянным, добавив $ Знак перед строками и столбцами в ссылках на ячейки. Чтобы узнать больше об абсолютной ссылке в Excel, ознакомьтесь с этим руководством: Абсолютная ссылка Excel (как сделать и использовать).

#VALUE возвращается ошибка

Следующие условия могут привести к тому, что функция ВПР вернет результат ошибки #ЗНАЧ.

Причина 1: искомое значение превышает 255 символов

Как показано на снимке экрана ниже, значение поиска в ячейке h5 превышает 255 символов, поэтому функция ВПР возвращает результат ошибки #ЗНАЧ.

Решения

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

• ИНДЕКС и ПОИСКПОЗ:

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=h5, 0), 0))

• Функция XLOOKUP (доступно в Excel 365, Excel 2021 и более поздних версиях):

  =XLOOKUP(h5,B5:B11,E5:E11)

Причина 2: аргумент col_index меньше 1

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

Если вы введете индекс столбца, который меньше 1 (т. е. ноль или отрицательный), функция ВПР не сможет найти столбец в массиве таблиц.

Решения

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

#REF возвращается ошибка

В этом разделе указана одна из причин, по которой функция ВПР возвращает ошибку #ССЫЛКА, и приведены решения этой проблемы.

Причина: Аргумент col_index больше, чем количество столбцов.

Как вы можете видеть на скриншоте ниже, в массиве таблицы всего 4 столбца. Однако индекс столбца, указанный вами в формуле ВПР, равен 5, что больше, чем количество столбцов в массиве таблиц. В результате функция ВПР не сможет найти столбец и в конечном итоге вернет ошибку #ССЫЛКА.

Решения

• Укажите правильный номер столбца

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

• Автоматически получать номер столбца на основе указанного заголовка столбца

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

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  Внимание: В приведенной выше формуле ПОИСКПОЗ(«Электронная почта»,B5:E5, 0) функция используется для получения номера столбца «Эл. адрес» в диапазоне дат B6:E12. Здесь результат равен 4, который используется в качестве col_index в функции ВПР.

Возвращается неверное значение

Если вы обнаружите, что ВПР не возвращает правильный результат, это может быть вызвано следующими причинами.

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

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

Решения

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

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

Причина 2: столбец вставлен или удален

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

Решения

Вот два решения для вас.

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

  =VLOOKUP(H6,B6:F12,5,FALSE)

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

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

Примечания к другим функциям

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

Читать онлайн «ВПР и Сводные таблицы Excel», Сергей Заидович Карамов – Литрес

Редактор Ольга Владимировна Карамова

Корректор Татьяна Сергеевна Карамова

© Сергей Заидович Карамов, 2021

ISBN 978-5-0055-7394-0

Создано в интеллектуальной издательской системе Ridero

Введение

Уважаемый читатель!

Представляю Вашему вниманию книгу «ВПР и Сводные таблицы Excel».

Публикация преследует несколько целей.

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

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

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

Книга написана на основе индивидуальных занятий со взрослыми слушателями по обучению их уверенной работе в Excel.

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

Основное внимание уделено практическим вопросам, т. е. решению практических задач и ответам на вопросы, которые были заданы моими учениками.

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

Операции с ячейками листа Excel

Ячейки листа

На пересечении конкретных столбца и строки активного листа Excel расположена ячейка. Пример такого пересечения показан на Рис. 1.

Рис. 1. Ячейка листа как пересечение строки и столбца

Буквенная «нумерация» столбца и цифровая нумерация строки задают однозначное положение ячейки на листе – ее координаты. Например, выделенная ячейка на имеет координаты C5. Сначала идет буква – столбец, а затем без пробела цифра – номер строки. Все буквы латинские.

Координаты ячейки – очень важное понятие в Excel. Все операции в Excel проводятся с использованием координат ячеек путем формализованных указаний. Например, число из ячейки A3 сложить с числом из ячейки В3.

Что же такое ячейка и для чего она нужна? Ячейка – это главный элемент Excel.

В обыденном понимании ячейки – это элементы, из которых состоит другой, более крупный объект. Например, когда говорят банковская ячейка – мы понимаем, что это такое. В банковской ячейке клиенты хранят какие-то вещественные ценности. А в Excel в ячейке хранятся данные. Причем, эти данные могут различаться в зависимости от определенных признаков или свойств.

Чтобы что-то хранилось в банковской ячейке, нужно это что-то туда положить. Так же и в Excel.

Для того, чтобы в ячейку разместить (ввести) информацию или данные, сначала необходимо ячейку выделить. Для выделения ячейки достаточно навести на нее курсор и один (не два) раз нажать левую кнопку мыши. Затем с клавиатуры ввести информацию (например, записать фамилию – Иванов) и нажать кнопку Ввод (Enter). Готово. Информация введена и будет храниться в ячейке до тех пор, пока пользователю это необходимо.

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

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

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

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

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

Например, размер ячейки, размер шрифта текста, расположение текста в ячейке, цвет шрифта, количество десятичных знаков числа и т. д. Все эти свойства называются форматами ячейки. Операция по изменению форматов ячейки называется форматированием.

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

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

Операции с ячейками листа

Если выделить конкретную ячейку, например С5 и вызвать контекстное меню (правой кнопкой мыши), появится список операций, которые можно проводить с выделенной ячейкой. Контекстное меню выделенной ячейки показано на Рис. 2.

Рис. 2. Контекстное меню выделенной ячейки со списком операций

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

Для этого нужно выделить ячейку C5, вызвать контекстное меню правой кнопкой мыши, выбрать из списка позицию «Копировать», затем выделить ячейку F5, снова вызвать контекстное меню и нажать… Возникает вопрос, как правильно вставить данные? Что надо нажать?

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

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

Выбор варианта вставки производится в контекстном меню в разделе «Параметры вставки». На Рис. 3 хорошо видно, что в контекстном меню возможен выбор из шести вариантов вставки.

Рис. 3. Параметры вставки данных

Пока нас будет интересовать самая первая слева позиция. Если навести на эту позицию курсор, появится название «Вставить». В дальнейшем разберемся со всеми другими вариантами вставки.

На Рис. 4 показан результат нашей операции по копированию – вставке данных.

Рис. 4. Копирование и вставка информации

Шесть позиций вставки, показанных на – это далеко не все возможные варианты вставки данных в Excel. Чтобы получить представления о том, какие возможны варианты вставки скопированных данных, нужно выбрать в контекстном меню позицию «Специальная вставка». Вид окна «Специальная вставка» показан на Рис. 5.

Рис. 5. Вид окна управления вставкой данных

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

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

Если навести курсор на нижний правый угол выделенной ячейки, то курсор изменит свой вид с белого крестика на маленький черный крестик. На Рис. 6 это позиция №2.

Рис. 6. Виды курсора

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

Для примера протянем ячейку С5 с фамилией Иванов вниз на 5 строк. Результат наших действий иллюстрирует Рис. 7.

Рис. 7. Копирование ячейки методом перетягивания

Копировать можно как одну ячейку, так и группу выделенных ячеек.

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

Рис. 8. Выделение группы ячеек

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

Рис. 9. Результат копирования перетягиванием группы ячеек

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

– введите в любую ячейку дату 01.01.2022 и протяните ее на десять строк вниз;

– введите в соседнем столбце в ячейку слово Январь и протяните на 10 строк вниз;

– введите в ячейку слово понедельник и протяните на 10 строк вниз.

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

Рис. 10. Примеры работы режима автозаполнения

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

Все эти примеры показывают возможности режима работы Excel, который называется автозаполнение. Возможности автозаполнения достаточно широки. Давайте еще раз введем в верхнюю ячейку столбца E дату и скопирует перетягиванием вниз. Справа внизу нового столбца появится информационный квадратик, который называется «Параметры автозаполнения», показанный на Рис.11. Это кнопка раскрывающегося списка.

Рис. 11. Кнопка выбора параметров автозаполнения

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

С другой стороны, бывают ситуации, когда необходимо заполнить ячейки одним и тем же значением. Такой режим тоже возможен при выборе в параметрах автозаполнения позиции «Копировать ячейки».

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

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

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

Горячие клавиши

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

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

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

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

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

Комбинации клавиш обозначают следующим образом. Например, Ctrl +Enter значит, что для выполнения определенной операции необходимо одновременно нажать две клавиши на клавиатуре – Ctrl и Enter. Сначала одну, потом удерживая ее, нажать вторую. Не надо пытаться нажать их одновременно.

Так вот, операции копирования и вставки можно выполнить с помощью горячих клавиш Ctrl+C (копировать), Ctrl+V (вставить). Если нужна специальная вставка – Ctrl+Alt+V. Выбирать нужные опции специальной вставки можно с помощью стрелок на клавиатуре и кнопки Таб. Закончив выбор, нужно нажать Enter.

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

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

Еще одна полезная клавиша. F1. Это вызов справки Excel.

Пока горячих клавиш достаточно.

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

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

Рис. 12. Задание на тренировку работы с горячими клавишами

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

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

Как можно решить эту задачу?

1 Вариант. Копируем ячейку A2 и вставляем в ячейку G2. Копируем ячейку A3 и вставляем в ячейку G3 и т. д. пока все ячейки не будут скопированы в другое место. Это самый примитивный и долгий путь.

2 Вариант. Выделяем и копируем диапазон ячеек A2:А5. Вставляем в ячейку G2. Выделяем и копируем диапазон ячеек С2:С5. Вставляем в ячейку Н2. Выделяем и копируем диапазон ячеек E2:E5. Вставляем в ячейку G2.

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

4 Вариант. Выделяем всю таблицу, заходим в меню «Главная» -«Найти и выделить» – «Выделить группу ячеек». Устанавливаем галочки, как показано на Рис. 13 и нажимаем Enter. Затем копируем выделенные ячейки и вставляем на новое место. Задача решена.

Рис. 13. Выбор позиций в окне управления группой ячеек

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

A2:А5 – это обозначение диапазона ячеек. Любой диапазон ячеек в Excel задается своими крайними значениями через двоеточие. В нашем случае это все ячейки от А2 до А5. Это столбец значений. Так можно определить любой диапазон ячеек: строку, столбец, таблицу. Например, данные нашей исходной таблицы 1 (см.) находятся в диапазоне A2:Е5. Таблица всегда однозначно определяется верхней левой и нижней правой ячейками.

Введение в анализ «что, если» — служба поддержки Майкрософт

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 для Mac 2011 Дополнительно…Меньше

Используя инструменты анализа «что, если» в Excel, вы можете использовать несколько разных наборов значений в одной или нескольких формулах для изучения всех различных результатов.

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

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

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

Три типа инструментов анализа «что, если» поставляются с Excel: Сценарии , Поиск цели и Таблицы данных . Таблицы сценариев и данных принимают наборы входных значений и определяют возможные результаты. Таблица данных работает только с одной или двумя переменными, но может принимать множество различных значений для этих переменных. Сценарий может иметь несколько переменных, но не более 32 значений. Поиск цели работает иначе, чем сценарии и таблицы данных, поскольку он берет результат и определяет возможные входные значения, которые дают этот результат.

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

Для более продвинутых моделей можно использовать надстройку Analysis ToolPak .

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

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

1. Изменение ячеек

2. Ячейка результата

1. Изменение ячеек

2. Ячейка результата

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

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

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

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

Ячейки B1, B2 и B3 содержат значения суммы кредита, срока и процентной ставки.

В ячейке B4 отображается результат формулы =ПЛТ(B3/12,B2,B1) .

Примечание. Поиск цели работает только с одним входным значением переменной. Если вы хотите определить более одного входного значения, например сумму кредита и сумму ежемесячного платежа по кредиту, вместо этого следует использовать надстройку «Поиск решения». Дополнительные сведения о надстройке «Поиск решения» см. в разделе Подготовка прогнозов и расширенных бизнес-моделей, а также перейдите по ссылкам в разделе См. также раздел .

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

Ячейка B3 содержит введенное значение.
Ячейки C3, C4 и C5 представляют собой значения, которые Excel заменяет на основе значения, введенного в ячейку B3.

Таблица данных не может содержать более двух переменных. Если вы хотите проанализировать более двух переменных, вы можете использовать сценарии. Хотя таблица данных ограничена только одной или двумя переменными, она может использовать любое количество различных значений переменных. Сценарий может иметь до 32 различных значений, но вы можете создать столько сценариев, сколько захотите.

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

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

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

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

Нужна дополнительная помощь?

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

См. также

Сценарии

поиск цели

Таблицы данных

Использование Solver для капитального бюджета

Использование Solver для определения оптимального ассортимента продуктов

Определите и решите проблему с помощью Solver

Надстройка «Пакет инструментов анализа»

Обзор формул в Excel

Как избежать неработающих формул

Обнаружение ошибок в формулах

Сочетания клавиш в Excel

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Великая электронная таблица Excel — The Daily WTF

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

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

В основе Списка рекомендаций лежала Большая электронная таблица Excel (или «G.E.S.», как ее называли внутри компании). Его существование было настолько важно, что у него даже был собственный преданный сотрудник, Хелен, редактор отчетов.

Каждую неделю танец Хелен проходил так:

1. Сначала она отправляла таблицу группе аналитиков, которые вносили коррективы на основе своего «понимания» рынка.
2. После возвращения в редактор она запускала макрос, который обновлял стоимость акций и облигаций от поставщика данных.
3. Затем эта таблица будет возвращена аналитикам для повторной проверки.
4. Если бы все было в порядке, то его бы отправляли в типографию, а затем еженедельно рассылали менеджерам по почте, а ежемесячно — их частным заказчикам.

Учитывая все это, руководство решило, что этот процесс созрел для улучшения. Итак, вооружившись копией Access для клиентской части и учетной записью SQL Server для управления всем сервером, Максим приступил к работе.

Никогда не недооценивайте силу доходности

Первоначальный разработчик G.E.S. К тому времени, когда появился Максим, его уже давно не было, но это не было большой проблемой. Среди океана финансовой чепухи он нашел хорошие комментарии и описательно названные поля данных. С технической точки зрения все было понятно, кроме одного поля — Доходность.

Информация о Yield была скорее предостерегающей, чем полезной. Описание формулы, например, просто гласило: «НИКОГДА НЕ ПЫТАЙТЕСЬ ИЗМЕНИТЬ ЭТОТ РАСЧЕТ! НИКОГДА!» В поисках полезной информации Максим обратился за помощью к аналитикам.

«О, Боже!» — скривился ведущий аналитик. — Вы не касаетесь доходности!?! О нет-нет-нет, не трогайте! Это самая важная часть всей таблицы!

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

Принцип неопределенности Гейзенберга… копирования и вставки в электронные таблицы?

Имея за плечами месячный опыт, проект Максима продвигался довольно хорошо. Всем нравился симпатичный интерфейс Access с его выпадающими списками, и вместо зловещего фасада он создал Великую электронную таблицу Excel. Даже Хелен была удовлетворена, так как теперь у нее было больше цели, чем выталкивать бумагу неделю за неделей! Однако радость была недолгой, как выяснилось на совещании в отделении неотложной помощи.

Ведущий аналитик начал: «Максим, мы обнаружили некоторые несоответствия в отчете. Некоторые значения в том, что мы считаем случайными акциями и облигациями, представлены в грубейшем искажении».

«Что вы имеете в виду?» — спросил Максим.

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

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