Форматирование текста или чисел в виде надстрокового или подстрочего формата
Существует несколько способов применения надстрокового и подстрокового к тексту или числам на вашем компьютере.
Форматирование текстовых значений как надстрокового или подстрочего
Это хорошо работает, если вы хотите представить данные в визуально привлекательном формате. Для форматирование числных значений или формул для математического, химических или других технических выражений используйте символы формул и шаблоны.
Вы можете выбрать символы в ячейке или диапазоне ячеев, которые вы хотите отформать.
На вкладке Главная в группе Шрифт нажмите кнопку Параметры шрифта.
Нажмите сочетание клавиш CTRL+1.
В области Эффектывыберите надстроковые или подстроковые и нажмите кнопку ОК.
Совет: Хотя Excel этих команд нет быстрых клавиш, вы можете перемещаться по меню и диалогову окнам только с помощью клавиатуры. Используйте ALT+HFNE для надстрочника, а ALT+HFNB для подстрок.
Добавление надстрок и подстрок на панель быстрого доступа
Если у вас есть подписка Office или Office 2019, вы можете добавлять сочетания клавиш к командам надстрок и подстрок на панели быстрого доступа. При этом они всегда будут под рукой. Подробнее о настройке панели быстрого доступа
Применение надстрокного или подстроического к числовому значению
Если вы хотите представить формулу или формулу для числных значений:
Нажмите кнопку > формулу> Конструктор.
Нажмите кнопку Сценарий и выберите нужный формат.
Щелкните квадраты и введите значения.
Совет: Поле уравнения не прикреплено к ячейке, и вы можете перемещать, поворачивать и перемещать ее с помощью крепления.
Также ознакомьтесь с вариантами форматирования формул и формул с помощью инструментов для работы с формулами.
Как в Excel сделать верхний индекс?
На вкладке Главная в группе Шрифт нажмите кнопку вызова диалогового окна «параметры шрифта». Нажмите сочетание клавиш CTRL+1. В разделе эффектыустановите флажок надстрочный или подстрочный текст и нажмите кнопку ОК.
Как в Excel поставить верхний и нижний индекс?
Если необходимо несколько ячеек перевести в верхний или нижний индекс:
Как в Excel вставить верхний индекс?
На вкладке «Главная» в группе «Шрифт» нажмите кнопку запуска диалогового окна «Параметры шрифта». Нажмите сочетание клавиш CTRL+1. В области «Эффекты»выберите поле «Надстроковые» или «Подстроковые» и нажмите кнопку «ОК».
Как в Excel поставить степень над числом?
находишь закладку «вставка» выбираешь справа символ Ω, потом выделяешь число над которым необходимо вставить знак 5² (у меня 5) и выбираешь символ ², далее вставить и всё… Shurovik : Выделяете показатель степени и нажимаете комбинацию Ctrl+1.
Как сделать надстрочный знак в Excel?
Выделим слова «надстрочный шрифт» и делаем один клик правой кнопкой мыши, в контекстном меню кликаем по пункту Формат ячеек. Нам нужно видоизменить выделенный текст как надстрочный, соответственно отмечаем галочкой пункт Надстрочный и жмем Ок.
Как поставить одновременно надстрочный и подстрочный текст?
Чтобы ввести текст немного выше (надстрочный) или ниже (подстрочный) обычного текста, можно использовать сочетания клавиш.
Как в ворде поставить верхний и нижний индекс одновременно?
Комбинации «горячих» клавиш, которые значительно упростят работу в Редакторе формул: верхний индекс —Ctrl+H(от английского слова «high»), нижний индекс —Ctrl+L(«low»), оба индекса одновременно —Ctrl+J(«joint»).
Как сделать верхний индекс?
Ввести верхний индекс можно одним из следующих способов:
Как поставить коэффициент в Экселе?
Как в Excel сделать маленькую цифру?
1. Установите курсор сразу за цифрой (числом) или буквой (словом), которое требуется возвести в степень. 2. На панели инструментов во вкладке “Главная” в группе “Шрифт” найдите символ “Надстрочный знак” и нажмите на него.
Как сделать квадратный метр в Excel?
Как в excel написать м2. нажимаешь F2 в ячейке, пишешь м2, затем выделаешь двойку, щелкаешь по ней правой кнопкой мыши и выбираешь Формат ячеек. В открывшемся окошке идешь на закладку Шрифт и ставишь опцию «Надстрочный».
Как правильно возвести в квадрат?
Чтобы возвести в квадрат числа, заканчивающиеся на 5. Алгоритм прост. Число до последней пятерки, умножаем на это же число плюс единица. К оставшемуся числу приписываем 25.
Как поставить квадрат над числом?
Первый способ — удерживая клавишу Alt, набрать на цифровой клавиатуре клавиши в нужном порядке
Как сделать подстрочный текст в формуле?
Подстрочный – для формул (например H2SO4).
…
Панель инструментов
Как в excel надстрочный знак
Как сделать надстрочный знак в Excel?
Выделим слова «надстрочный шрифт» и делаем один клик правой кнопкой мыши, в контекстном меню кликаем по пункту Формат ячеек. Нам нужно видоизменить выделенный текст как надстрочный, соответственно отмечаем галочкой пункт Надстрочный и жмем Ок.
Как в Экселе сделать надстрочный?
Вы можете выбрать символы в ячейке или диапазоне ячеок, которые вы хотите отформакторить. На вкладке «Главная» в группе «Шрифт» нажмите кнопку запуска диалогового окна «Параметры шрифта». Нажмите сочетание клавиш CTRL+1. В области «Эффекты»выберите поле «Надстроковые» или «Подстроковые» и нажмите кнопку «ОК».
Как поставить надстрочный знак?
Чтобы ввести текст немного выше (надстрочный) или ниже (подстрочный) обычного текста, можно использовать сочетания клавиш.
Как в Excel поставить верхний и нижний индекс?
Если необходимо несколько ячеек перевести в верхний или нижний индекс:
Как в Excel поставить степень над буквой?
Стандартный и самый простой вариант – использовать значок «^», который получается при нажатии Shift+6 при английской раскладке клавиатуры.
Как сделать верхний индекс в Excel?
На вкладке Главная в группе Шрифт нажмите кнопку вызова диалогового окна «параметры шрифта». Нажмите сочетание клавиш CTRL+1. В разделе эффектыустановите флажок надстрочный или подстрочный текст и нажмите кнопку ОК.
Как сделать верхний индекс?
Ввести верхний индекс можно одним из следующих способов:
Как поставить верхний индекс в Power Point?
В PowerPoint, начните с ввода текста, а затем введите число или текст, который вы хотите превратить в верхний индекс. Сначала просто наберите обычный текст. Теперь, выделите часть текста, к которому мы хотим применить эффект. Выберите текст, который нужно сделать надстрочным.
Как поставить цифру вверху текста?
Выделяем слово или цифру мышью. Правой кнопкой мыши выбираем «Шрифт». В открывшемся окне ставим галочку в «Надстрочный».
Как сделать нижний индекс в презентации?
В разделе Другие языки и технологии на вопрос Как в PowerPoint сделать нижний индекс? заданный автором Артемида лучший ответ это Выделяешь нужный символ — правым щелчком по выделенной символе выбираем ШРИФТ- там выбиришь надстрочный или подстрочный! вот и все!
Как в ворде поставить верхний и нижний индекс одновременно?
Комбинации «горячих» клавиш, которые значительно упростят работу в Редакторе формул: верхний индекс —Ctrl+H(от английского слова «high»), нижний индекс —Ctrl+L(«low»), оба индекса одновременно —Ctrl+J(«joint»).
Как сделать подстрочный текст в формуле?
Подстрочный – для формул (например H2SO4).
…
Панель инструментов
Как написать х в квадрате в Excel?
Способ 1: с помощью формулы
Как в Excel сделать число в квадрате?
Возведение в квадрат числа в отдельной ячейке
Как написать число в степени на клавиатуре?
Первый способ — удерживая клавишу Alt, набрать на цифровой клавиатуре клавиши в нужном порядке.
Отображение надстрочных символов в EXCEL
history 25 апреля 2013 г.
В EXCEL легко отформатировать шрифт, чтобы отобразить надстрочные (x 2 ) и подстрочные (Al 2 O 3 ) символы. Это можно сделать выделив часть текста в ячейке и через диалоговое окно Формат ячеек / Шрифт ( CTRL+SHIFT+F ) применить необходимое форматирование шрифта. Но этот подход не работает, если в ячейке находится не значение, а формула. И все же, в ряде случаев, выход есть.
Представим себе достаточно экзотическую задачу: необходимо отображать числа в формате 225,00 2 Причем, значение (225,00) может меняться, в зависимости от значения других ячеек, т.е. в ячейке содержится формула.
Форматирование шрифта здесь не поможет, у нас не текстовая строка, а формула.
Функция ТЕКСТ()
Пользовательский формат
К сожалению, все это работает, при наличии соответствующих символов. Мне известно их четыре: 0 (код Alt+0176), 1 (код Alt+0185), 2 (код Alt+0178), 3 (код Alt+0179).
Как в Excel написать e в степени?
Как записать е в степени х в Экселе?
Экспонента — показательная функция f(x) = exp (x) = ex, где е — число Эйлера. Также в Excel есть возможность использовать другую известную математическую константу — число Пи.
Как сделать в Excel число в степени?
Стандартный и самый простой вариант – использовать значок «^», который получается при нажатии Shift+6 при английской раскладке клавиатуры. ВАЖНО! Чтобы число было возведено в нужную нам степень, необходимо в ячейке поставить знак «=» перед указанием цифры, которую вы хотите возвести.
Как записать e в степени?
Возведение числа е в степень означает возведение в степень числа Эйлера e x = exp (x).
Что такое е в степени х?
Функция е в степени х — это особый случай показательной функции, где в качестве основания выступает число е, иначе называемое числом Эйлера. Иначе такая функция называется экспоненциальной или экспонентой, записываться она может в нескольких формах: =exp(x).
Что такое е в математике?
называют числом Эйлера или числом Непера. Обозначается строчной латинской буквой «e».
Как убрать букву Е в Экселе?
Как сделать надстрочный знак в Excel?
В EXCEL легко отформатировать шрифт, чтобы отобразить надстрочные (x 2 ) и подстрочные (Al 2 O 3 ) символы. Это можно сделать выделив часть текста в ячейке и через диалоговое окно Формат ячеек / Шрифт ( CTRL+SHIFT+F ) применить необходимое форматирование шрифта.
Как поставить цифру сверху в Экселе?
Нажмите на клавиатуре горячие клавиши Ctrl+1. Появится окно «Формат ячеек». В нем вам необходимо в области «Видоизменение» установить отметку напротив пункта «Надстрочный». Нажмите ОК.
Как в Excel сделать верхний индекс?
На вкладке Главная в группе Шрифт нажмите кнопку вызова диалогового окна «параметры шрифта». Нажмите сочетание клавиш CTRL+1. В разделе эффектыустановите флажок надстрочный или подстрочный текст и нажмите кнопку ОК.
Как на калькуляторе посчитать e в степени?
Чему равна производная от е в степени х?
Как посчитать е в степени онлайн?
Экспонента является показательной функцией ƒ(x) = ex, где число e (число Эйлера, e ≈ 2,718) – основание степени, а х – степень в которую нужно его возвести.
Что означает буква Е в цифрах?
Что такое экспонента простыми словами?
Экспонентой называется функция y=ex, где e — хитрое математическое число, которое примерно равно 2,72. Она обладает замечательным свойством: ее производная равна ей самой. То есть, если расстояние, которое проходит снежный ком, зависит от времени как экспонента, то и его скорость выражается той же самой экспонентой.
Чему равно число exp?
Формулы подстановки Excel: ВПР, ИНДЕКС и ПОИСКПОЗ | Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Формулы подстановки Excel: ВПР, ИНДЕКС и ПОИСКПОЗ
Если произвести поиск по функциям подстановки, Google покажет, что ВПР намного популярнее функции ИНДЕКС. Оно и понятно, ведь чтобы придать функции ИНДЕКС тот же функционал, что и ВПР, необходимо воспользоваться еще одной формулой – ПОИСКПОЗ. Что касается меня, было всегда непросто попробовать и освоить две новые функции одновременно. Но они дают больше возможностей и гибкости в создании электронных таблиц. Но обо всем по порядку.
Функция ВПР()
Предположим, у вас есть таблица с данными о работниках. В первой колонке хранится табельный номер сотрудника, в остальных – другие данные (ФИО, отдел и т.д.). Если у вас есть табельный номер, то можно воспользоваться функцией ВПР, чтобы вернуть определенную информацию о сотруднике. Синтаксис формулы =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр]). Она говорит Excel: «Найди в таблице строку, первая ячейка которой совпадает с искомым_значением, и верни значение ячейки с порядковым номером номер_столбца».
Но случаются ситуации, когда у вас есть имя сотрудника и необходимо вернуть табельный номер. На рисунке в ячейке A10 – имя работника и требуется определить табельный номер в ячейке B10.
Функция ИНДЕКС()
Чтобы решить нашу проблему в один шаг, необходимо воспользоваться формулами ИНДЕКС и ПОИСКПОЗ. Сложность данного подхода заключается в том, что требуется применить две функции, которые, возможно, вы никогда не применяли до этого. Для упрощения понимания решим эту задачу в два этапа.
Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-нибудь говорит «индекс», у меня в голове не возникает ни единой ассоциации, чем же занимается эта функция. А требует она целых три аргумента: =ИНДЕКС(массив; номер_строки; [номер_столбца]).
Говоря по-простому, Excel идет в массив данных и возвращает значение, находящееся на пересечении указанной строки и столбца. Как будто бы просто. Таким образом, формула =ИНДЕКС($A$2:$C$6;4;2) вернет значение, находящееся в ячейке B5.
Применительно к нашей проблеме, чтобы вернуть табельный номер работника, формула должна выглядеть следующим образом =ИНДЕКС($A$2:$A$6;?;1). Выглядит как бессмыслица, но если мы заменим знак вопроса формулой ПОИСКПОЗ, у нас есть решение.
Функция ПОИСКПОЗ()
Синтаксис этой функции таков: =ПОИСКПОЗ(искомое_значение; просматриваемы_массив; [тип_сопоставления]).
Ну, в принципе, все. Функция ПОИСКПОЗ указывает функции ИНДЕКС, в какой строке искать значение. Замените знак вопроса в формуле ИНДЕКС формулой ПОИСКОПОЗ и вы получите эквивалент ВПР с возможностью поиска данных, находящихся левее ключевого столбца. Получиться так:
В начале было непривычно, был огромный соблазн вставить еще одну колону и работать, как всегда работал. Но со временем использование функции ИНДЕКС вошло в привычку. Оказалось, что это быстрее и требует меньших манипуляций. Так что в следующий раз, когда у вас возникнет желание задать номеру столбца отрицательное число в ВПР, воспользуйтесь сочетанием двух странных функций ИНДЕКС и ПОИСКПОЗ, чтобы решить свою проблему.
Функция ПОИСКПОЗ в Excel
В этой статье рассматривается синтаксис функции ПОИСКПОЗ Excel, а также как использовать функцию ПОИСКПОЗ в Excel с примерами формул.
Также в этой статье рассматривается комбинация формул ИНДЕКС ПОИСКПОЗ в Excel.
Функция ПОИСКПОЗ в Excel – синтаксис и использование
Функция ПОИСКПОЗ в Excel ищет заданное значение в диапазоне ячеек и возвращает относительное положение этого значения.
Синтаксис формулы ПОИСКПОЗ выглядит следующим образом:
Искомое_значение (обязательный аргумент) – значение, которое вы хотите найти. Это может быть числовое, текстовое или логическое значение, а также ссылка на ячейку.
Просматриваемый_массив (обязательный аргумент) – диапазон ячеек для поиска.
Чтобы лучше разобраться в данной функции, давайте напишем простую формулу ПОИСКПОЗ, основанную на следующих данных: фамилии студентов в столбце A и их баллы по экзаменам в столбце B, отсортированные от самых высоких до самых низких. Чтобы узнать, где среди других находится конкретный студент (например, студентка Виноградова), используйте эту простую формулу:
При желании вы можете поместить значение поиска в ячейку (E1 в данном примере) и сослаться на эту ячейку в формуле ПОИСКПОЗ Excel:
Функция ПОИСКПОЗ в Excel – Пример использования функции ПОИСКПОЗ в Excel
Как вы видите на изображении выше, фамилии студентов вводятся в произвольном порядке, поэтому мы устанавливаем аргумент тип_сопоставления равным 0 (точное совпадение), поскольку только этот тип соответствия не требует сортировки значений в массиве поиска. Технически формула ПОИСКПОЗ возвращает относительное положение студентки Виноградовой в исследуемом диапазоне. Но поскольку оценки сортируются от самых высоких до самых низких, это также говорит нам о том, что Виноградова пятая по счету среди всех учеников.
Особенности функции ПОИСКПОЗ Excel
Как вы только что видели, использовать ПОИСКПОЗ в Excel легко. Однако, как и у любой функции Excel, у нее есть несколько особенностей, о которых следует знать:
Использование ПОИСКПОЗ в Excel – примеры формул
Теперь, когда вы знаете основные возможности функции ПОИСКПОЗ в Excel, давайте рассмотрим еще несколько примеров формул, которые выходят за рамки основ.
Формула ПОИСКПОЗ Excel с подстановочными знаками
Как и многие другие функции Excel, ПОИСКПОЗ распознает следующие подстановочные знаки:
Формула ПОИСКПОЗ с подстановочными знаками полезна в ситуациях, когда требуется осуществить поиск не всей текстовой строки, а только некоторых символов или некоторой части строки. Чтобы проиллюстрировать это, рассмотрим следующий пример.
Предположим, что у вас есть список компаний и данные их продаж за последнюю неделю. Вы хотите найти относительное положение определенной компании в списке (отсортированному по объему продаж в порядке убывания), но вы не можете точно вспомнить его имя, хотя вы помните несколько первых букв.
Предполагая, что названия компаний находятся в диапазоне A2:A8, и вы ищете название, начинающееся с «восток», формула выглядит следующим образом:
=ПОИСКПОЗ(«восток*»; A2:A8; 0)
Чтобы сделать нашу формулу ПОИСКПОЗ более универсальной, вы можете ввести значение поиска в любой ячейке (E1 в этом примере) и объединить эту ячейку с подстановочным символом, например:
Как показано на изображении ниже, формула возвращает 2 позицию, которая соответствует компании « Восток-авто »:
Функция ПОИСКПОЗ в Excel – Пример функции ПОИСКПОЗ Excel с подстановочными знаками
Чтобы заменить только один символ в значении поиска, используйте подстановочный оператор «?», например:
Вышеуказанная формула будет соответствовать названию « Бейкер » и вернет его относительное положение, которое равно 5.
Формула ПОИСКПОЗ с учетом регистра
Чувствительность к регистру формулы для сопоставления данных в Excel заключается в следующем:
Логика этой формулы такова:
Пусть значение поиска находится в ячейке E1, а массив поиска — A2:A8, тогда формула выглядит следующим образом:
=ПОИСКПОЗ(ИСТИНА; СОВПАД(A2:A8; E1);0)
На следующем изображении демонстрируется формула ПОИСКПОЗ с учетом регистра в Excel:
Функция ПОИСКПОЗ в Excel – Пример функции ПОИСКПОЗ Excel с учетом регистра
Сравнить 2 столбца на совпадения и различия (ЕНД и ПОИСКПОЗ)
Проверка двух списков на совпадения и различия – одна из наиболее распространенных задач в Excel, и это можно сделать различными способами. Использование формулы ЕНД/ПОИСКПОЗ является одним из них:
=ЕСЛИ(ЕНД(ПОИСКПОЗ (1-е значение в Списке 1; Список 2; 0)); «Нет в Списке 1»; «»)
Для любого значения из Списка 2, отсутствующего в Списке 1, формула возвращает «Нет в Списке 1». И вот как это происходит:
Предположим, что нам необходимо сравнить значения в столбце B со значениями в столбце A, формула примет следующую форму (где B2 — самая верхняя ячейка):
=ЕСЛИ(ЕНД(ПОИСКПОЗ(B2;A:A;0));»Нет в Списке 1″; «»)
=ЕСЛИ(ЕНД(ПОИСКПОЗ(ИСТИНА; СОВПАД(A:A;B2); 0));»Нет в Списке 1″; «»)
На следующем изображении демонстрируются обе формулы в действии:
Функция ПОИСКПОЗ в Excel – Сравнение 2 столбцов на совпадения с использованием ЕНД и ПОИСКПОЗ
ИНДЕКС и ПОИСКПОЗ в Excel
С функцией ПОИСКПОЗ Excel мы уже знакомы. Давайте теперь рассмотрим, как можно использовать комбинацию ИНДЕКС ПОИСКПОЗ в Excel. Начнем с синтаксиса функции ИНДЕКС.
Синтаксис и использование функции ИНДЕКС
Функция ИНДЕКС Excel возвращает значение в массиве, основанное на указанных вами строках и столбцах. Синтаксис функции ИНДЕКС прост:
ИНДЕКС(массив; номер_строки; [номер_столбца])
Вот очень простое объяснение каждого параметра:
Если используются оба параметра номер_строки и номер_столбца, функция ИНДЕКС возвращает значение в ячейке на пересечении указанной строки и столбца.
Вот простейший пример формулы ИНДЕКС:
Формула ищет в ячейках от A1 до C10 и возвращает значение ячейки во второй строке и третьем столбце, то есть в ячейке C2.
Очень легко, не так ли? Однако при работе с реальными данными вы вряд ли знаете, какие строки и столбцы вам нужны, поэтому вам нужна помощь функции ПОИСКПОЗ.
ИНДЕКС ПОИСКПОЗ в Excel пример
Теперь, когда вы знаете синтаксис и основы этих двух функций, у вас наверняка уже сложилось понимание того, как работают ИНДЕКС ПОИСКПОЗ Excel.
Функция ПОИСКПОЗ определяет относительное положение значения поиска в указанном диапазоне ячеек. А функция ИНДЕКС принимает это число и возвращает значение в соответствующую ячейку.
Комбинацию ИНДЕКС ПОИСКПОЗ в Excel можно представить таким образом:
=ИНДЕКС(столбец для возвращения значения, ПОИСКПОЗ (значение поиска, столбец для поиска, 0))
Для лучшего понимания рассмотрим наглядный пример. Предположим, у нас есть список стран с населением, подобных этому:
Функция ПОИСКПОЗ в Excel – Исходные данные для формулы ИНДЕКС ПОИСКПОЗ в Excel
Найдем численность населения, например, России, используя следующую формулу ИНДЕКС ПОИСКПОЗ:
Теперь давайте разберем, что на самом деле выполняет каждый компонент этой формулы:
И вот результат, который мы получаем в Excel:
Функция ПОИСКПОЗ в Excel – ИНДЕКС ПОИСКПОЗ пример формулы
Обратите внимание! Количество строк и столбцов в массиве ИНДЕКС должно соответствовать значениям в параметрах номер_строки и/или номер_столбца функции ПОИСКПОЗ соответственно. В противном случае формула вернет неверный результат.
Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
Совет: Попробуйте использовать новые функции кслукуп и ксматч с улучшенными версиями функций, описанными в этой статье. Эти новые функции работают в любом направлении и возвращают точное совпадение по умолчанию, упрощая и удобнее в использовании, чем их предшественники.
Предположим, что у вас есть список номеров местоположений Office, и вам необходимо знать, какие сотрудники находятся в каждом офисе. Электронная таблица огромна, поэтому вы, возможно, считаете, что она является сложной задачей. Это очень просто сделать с помощью функции Просмотр.
Функции ВПР и ГПР вместе с функцией индекс и ПОИСКПОЗявляются наиболее полезными функциями в Excel.
Примечание: Функция «Мастер подстановок» больше не доступна в Excel.
Ниже приведен пример использования функции ВПР.
В этом примере ячейка B2 является первым аргументом— элементом данных, для работы которого требуется функция. Для функции ВПР первым аргументом является значение, которое нужно найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как «Иванов» или 21 000. Второй аргумент — это диапазон ячеек (C2-: E7, в котором нужно найти искомое значение. Третьим аргументом является столбец в диапазоне ячеек, который содержит искомое значение.
Четвертый аргумент необязателен. Введите значение истина или ложь. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести значение ложь, функция будет соответствовать значению, представленному первым аргументом. Другими словами, если оставить четвертый аргумент пустым — или ввести значение истина, вы получаете более гибкие возможности.
В этом примере показано, как работает функция. Когда вы вводите значение в ячейке B2 (первый аргумент), функция ВПР ищет ячейки в диапазоне C2: E7 (второй аргумент) и возвращает ближайшее приближенное соответствие из третьего столбца в диапазоне, столбец E (Третий аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приближенное соответствие. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.
Если вы хорошо знакомы с функцией ВПР, функция ГПР очень проста в использовании. При вводе одних и тех же аргументов выполняется поиск в строках, а не в столбцах.
Использование функций индекс и MATCH вместо функции ВПР
Существуют определенные ограничения с использованием функции ВПР — функция ВПР может искать значение слева направо. Это означает, что столбец с искомым значением всегда должен располагаться слева от столбца, содержащего возвращаемое значение. Если электронная таблица не создана таким образом, не используйте функцию ВПР. Вместо этого используйте сочетание функций индекс и ПОИСКПОЗ.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Дополнительные примеры использования функции индекс и MATCH вместо функции ВПР можно найти в статье https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ by Bill Джилена, Microsoft MVP.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями поиска перед их использованием с собственными данными, воспользуйтесь приведенными ниже примерами данных.
Пример функции ВПР на работе
Скопируйте указанные ниже данные в пустой лист.
5 вариантов использования функции ИНДЕКС (INDEX)
Бывает у вас такое: смотришь на человека и думаешь «что за @#$%)(*?» А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?
Вариант 1. Извлечение данных из столбца по номеру ячейки
Самый простой случай использования функции ИНДЕКС – это ситуация, когда нам нужно извлечь данные из одномерного диапазона-столбца, если мы знаем порядковый номер ячейки. Синтаксис в этом случае будет:
=ИНДЕКС( Диапазон_столбец ; Порядковый_номер_ячейки )
. но, в отличие от ВПР, могут извлекать значения левее поискового столбца и номер столбца-результата высчитывать не нужно.
Вариант 2. Извлечение данных из двумерного диапазона
Если диапазон двумерный, т.е. состоит из нескольких строк и столбцов, то наша функция будет использоваться немного в другом формате:
=ИНДЕКС( Диапазон ; Номер_строки ; Номер_столбца )
Т.е. функция извлекает значение из ячейки диапазона с пересечения строки и столбца с заданными номерами.
Легко сообразить, что с помощью такой вариации ИНДЕКС и двух функций ПОИСКПОЗ можно легко реализовать двумерный поиск:
Вариант 3. Несколько таблиц
Если таблица не одна, а их несколько, то функция ИНДЕКС может извлечь данные из нужной строки и столбца именно заданной таблицы. В этом случае используется следующий синтаксис:
=ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )
Обратите особое внимание, что в этом случае первый аргумент – список диапазонов — заключается в скобки, а сами диапазоны перечисляются через точку с запятой.
Вариант 4. Ссылка на столбец / строку
Если во втором варианте использования функции ИНДЕКС номер строки или столбца задать равным нулю (или просто не указать), то функция будет выдавать уже не значение, а ссылку на диапазон-столбец или диапазон-строку соответственно:
Вариант 5. Ссылка на ячейку
Общеизвестно, что стандартная ссылка на любой диапазон ячеек в Excel выглядит как Начало-Двоеточие-Конец, например A2:B5. Хитрость в том, что если взять функцию ИНДЕКС в первом или втором варианте и подставить ее после двоеточия, то наша функция будет выдавать уже не значение, а адрес, и на выходе мы получим полноценную ссылку на диапазон от начальной ячейки до той, которую нашла ИНДЕКС:
Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах
Совместное использование функций ИНДЕКС и ПОИСКПОЗ в Excel – хорошая альтернатива ВПР, ГПР и ПРОСМОТР. Эта связка универсальна и обладает всеми возможностями этих функций. А в некоторых случаях, например, при двумерном поиске данных на листе, окажется просто незаменимой. В данном уроке мы последовательно разберем функции ПОИСКПОЗ и ИНДЕКС, а затем рассмотрим пример их совместного использования в Excel.
Более подробно о функциях ВПР и ПРОСМОТР.
Функция ПОИСКПОЗ в Excel
Функция ПОИСКПОЗ возвращает относительное расположение ячейки в заданном диапазоне Excel, содержимое которой соответствует искомому значению. Т.е. данная функция возвращает не само содержимое, а его местоположение в массиве данных.
Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.
В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.
Из приведенных примеров видно, что первым аргументом функции ПОИСКПОЗ является искомое значение. Вторым аргументом выступает диапазон, который содержит искомое значение. Также функция имеет еще и третий аргумент, который задает тип сопоставления. Он может принимать один из трех вариантов:
В одиночку функция ПОИСКПОЗ, как правило, не представляет особой ценности, поэтому в Excel ее очень часто используют вместе с функцией ИНДЕКС.
Функция ИНДЕКС в Excel
Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.
Стоит отметить, что номера строк и столбцов задаются относительно верхней левой ячейки диапазона. Например, если ту же таблицу расположить в другом диапазоне, то формула вернет тот же результат:
Если массив содержит только одну строку или один столбец, т.е. является вектором, то второй аргумент функции ИНДЕКС указывает номер значения в этом векторе. При этом третий аргумент указывать необязательно.
Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):
Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):
Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel
Если Вы уже работали с функциями ВПР, ГПР и ПРОСМОТР в Excel, то должны знать, что они осуществляют поиск только в одномерном массиве. Но иногда приходится сталкиваться с двумерным поиском, когда соответствия требуется искать сразу по двум параметрам. Именно в таких случаях связка ПОИСКПОЗ и ИНДЕКС в Excel оказывается просто незаменимой.
На рисунке ниже представлена таблица, которая содержит месячные объемы продаж каждого из четырех видов товара. Наша задача, указав требуемый месяц и тип товара, получить объем продаж.
Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 – тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:
=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))
Как видите, мы получили верный результат. Если поменять месяц и тип товара, формула снова вернет правильный результат:
В данной формуле функция ИНДЕКС принимает все 3 аргумента:
Если подставить в исходную громоздкую формулу вместо функций ПОИСКПОЗ уже вычисленные данные из ячеек D15 и D16, то формула преобразится в более компактный и понятный вид:
=ИНДЕКС(B2:E13;D15;D16)
Как видите, все достаточно просто!
На этой прекрасной ноте мы закончим. В этом уроке Вы познакомились еще с двумя полезными функциями Microsoft Excel – ПОИСКПОЗ и ИНДЕКС, разобрали возможности на простых примерах, а также посмотрели их совместное использование. Надеюсь, что данный урок Вам пригодился. Оставайтесь с нами и успехов в изучении Excel.