Индекс формула в excel

10 ответов

Originally posted by Namik Доброго Вам времени суток Уважаемые господа просмотрите пожалуйста нижеследующий код

———————————————— Dim s As Integer s = ActiveCell.Characters.Count With ActiveCell.Characters(Start:=s, Length:=l).Font .Superscript = True End With End Sub

этот код позволяет мне перевести в верхний индекс последний символ в выделенной ячейке а мне необхадимо чтобы в верхний индекс переводился символ который я выделю

как мне этого добиться помогите пожалуйста

С Уважением и Благодарностью Намик

А что значит “выделю”? Ты не сможешь запустить макрос, если находишься в состоянии правки ячейки.

А что значит “выделю”? Ты не сможешь запустить макрос, если находишься в состоянии правки ячейки.

Да Уважаемый Вы правы я не правильно выразился

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

можете Вы мне помочь в этом ?

С Уважением и Благодарностью Намик

P.S. а каже тогда это реализуется в Excel? ведь там мы выделяем символ в ячейке (ячейка становится редактируемой) щелкаем правой кнопкой мыши выбираем формат ячейки и т.д.

Да Уважаемый Вы правы я не правильно выразился

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

можете Вы мне помочь в этом ?

С Уважением и Благодарностью Намик

P.S. а каже тогда это реализуется в Excel? ведь там мы выделяем символ в ячейке (ячейка становится редактируемой) щелкаем правой кнопкой мыши выбираем формат ячейки и т.д.

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

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

Форма массива

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

Если первый аргумент функции ИНДЕКС является константной массива, используйте форму массива.

ИНДЕКС(массив; номер_строки; )

Форма массива функции индекс имеет следующие аргументы:

IsArray — обязательный аргумент. Диапазон ячеек или константа массива.

Если массив содержит только одну строку или один столбец, соответствующий аргумент «номер_строки» или «номер_столбца» является необязательным.

Если массив содержит более одной строки и несколько столбцов и используется только аргумент «номер_строки» или «номер_столбца», функция индекс возвращает массив всей строки или столбца в массиве.

Номер_строки — обязательный аргумент. Выбирает строку в массиве, из которой требуется возвратить значение. Если аргумент «номер_строки» опущен, требуется номер_столбца.

Номер_столбца — необязательный аргумент. Выбирает столбец в массиве, из которого требуется возвратить значение. Если аргумент номер_столбца опущен, то требуется номер_строки.

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

аргумент «номер_строки» и «номер_столбца» должен указывать на ячейку в массиве; в противном случае функция индекс возвращает значение #REF! ошибка #ЧИСЛО!.

Если для аргумента «номер_строки» или «номер_столбца» установлено значение 0 (ноль), функция индекс возвращает массив значений для всего столбца или строки соответственно. Чтобы использовать значения, возвращаемые в виде массива, введите в качестве формулы массива функцию индекс.

Примечание: Если у вас установлена текущая версия Office 365, вы можете ввести формулу в левую верхнюю ячейку диапазона вывода, а затем нажмите клавишу Ввод , чтобы подтвердить формулу как формулу динамических массивов. В противном случае необходимо ввести формулу в качестве устаревшей формулы массива, сначала выделив диапазон вывода, введите формулу в верхнюю левую ячейку выходного диапазона, а затем нажмите клавиши CTRL + SHIFT + ВВОД , чтобы подтвердить его. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

В этих примерах функция ИНДЕКС используется для поиска значения ячейки, находящейся на пересечении заданных строки и столбца.

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — ВВОД.

Значение ячейки на пересечении второй строки и второго столбца в диапазоне A2:B3.

Значение ячейки на пересечении второй строки и первого столбца в диапазоне A2:B3.

В этом примере функция ИНДЕКС используется в формуле массива для поиска значений двух заданных ячеек в массиве с диапазоном 2 x 2.

Примечание: Если у вас установлена текущая версия Office 365, вы можете ввести формулу в левую верхнюю ячейку диапазона вывода, а затем нажмите клавишу Ввод , чтобы подтвердить формулу как формулу динамических массивов. В противном случае формула должна быть введена как устаревшая формула массива, если сначала выбрать две пустые ячейки, ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавиши CTRL + SHIFT + ВВОД , чтобы подтвердить его. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Значение ячейки на пересечении первой строки и второго столбца в массиве. Массив содержит значения 1 и 2 в первой строке и значения 3 и 4 во второй строке.

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

Excel MATCH функция

Microsoft Excel Функция ПОИСКПОЗ выполняет поиск определенного значения в диапазоне ячеек и возвращает относительное положение этого значения.

аргументы

Lookup_value (необходимые): Конкретное значение, которое вы хотите сопоставить в массиве look_up; Этот аргумент может быть числом, текстом, логическим значением или ссылкой ячейки на значение (число, текст или логическое значение).

Lookup_array (необходимые): Диапазон ячеек, содержащий искомое значение.

Match_type (необязательный): Тип соответствия, которое будет выполнять функция. Он состоит из 3-х видов:

• — Находит первое значение, точно соответствующее lookup_value
• 1 — или опущено. Если точное значение соответствия не может быть найдено, Match найдет наибольшее значение, которое меньше или равно значению look_up.Значения в аргументе массива look_up должны быть в порядке возрастания..
• -1 — Находит наименьшее значение, которое больше или равно значению look_up.Значения в аргументе массива look_up должны быть в порядке убывания..

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

1. Функция ПОИСКПОЗ не чувствительна к регистру. 2. Функция Match вернет значение ошибки # N / A, если совпадение не будет найдено. 3. Функция ПОИСКПОЗ позволяет использовать символы подстановки в аргументе lookup_value для приблизительного совпадения.

Примеры

Пример 1: функция ПОИСКПОЗ для точного совпадения

Пожалуйста, сделайте следующее, чтобы вернуть позицию Чарльза Монагана в диапазоне B3: B7.

Выберите пустую ячейку, введите в нее формулу ниже и нажмите клавишу Enter, чтобы получить результат.=MATCH(D4,B3:B7,0)

Внимание: В формуле D4 содержит поисковое значение; B3: B7 это диапазон ячеек, содержащий искомое значение; количество означает, что вы ищете точное значение соответствия

Пример 2: функция ПОИСКПОЗ для приблизительного совпадения

В этом разделе рассказывается об использовании функции ПОИСКПОЗ для поиска приблизительного совпадения в Excel.

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

Выберите пустую ячейку, введите в нее приведенную ниже формулу и нажмите клавишу Enter, чтобы получить результат.=MATCH(D4,B3:B7,1)

Затем он вернет позицию числа 2000, которое является наибольшим значением, меньшим 2500 в списке.

Примечание. В этом случае все значения в списке B3: B7 должны быть отсортированы в порядке возрастания, в противном случае будет возвращена ошибка # N / A. Чтобы вернуть позицию наименьшего значения (говорит 3000), которое больше 2500 в списке, примените эту формулу:=MATCH(D4,B3:B7,-1)

Внимание: Все значения в списке B3: B7 должны быть отсортированы по убыванию в случае возврата ошибки # N / A

Пример 3: функция ПОИСКПОЗ для соответствия подстановочным знакам в функции ПОИСКПОЗ

Кроме того, функция ПОИСКПОЗ может выполнять сопоставление с использованием подстановочных знаков, когда тип сопоставления установлен на ноль.

Как показано на скриншоте ниже, для получения позиции значения, которое начинается с «привет», сделайте следующее.

Выберите пустую ячейку, введите в нее формулу ниже (вы можете заменить D4 непосредственно на «привет *») и нажмите клавишу Enter. Он вернет позицию первого значения совпадения.=MATCH(D4,B3:B7,0)

Чаевые: Функция ПОИСКПОЗ не чувствительна к регистру.

Пример формулы с ВПР и ПОИСКПОЗ

Пример таблицы табель премии изображен ниже на рисунке:

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

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

Для этого:

1. В ячейку B14 введите размер выручки: 370 000.
2. В ячейке B15 укажите номер магазина: 3.
3. В ячейке B16 введите следующую формулу:

В результате определена нижняя граница премии для магазина №3 при выручке больше >370 000, но меньше


Поиск ближайшего значения Excel формулой ВПР и ПОИСКПОЗ:

В первом аргументе функции ВПР указываем ссылку на ячейку с критерием поискового запроса (исходная сумма выручки), который содержится в ячейке B14. Область поиска в просматриваемом диапазоне A5:K11 указывается во втором аргументе функции ВПР. А в третьем аргументе должен быть указан номер столбца, но он пока неизвестен. Из второго критерия поискового запроса известно только что исходный номер столбца таблицы относится к 3-тьему магазину (ячейка B15).

Чтобы определить номер столбца, который содержит заголовок «Магазин 3» следует использовать функцию ПОИСКПОЗ. Как само название функции говорит о том, что ее задачей является поиск позиции где находится значение внутри определенного диапазона ячеек. В нашем случаи мы ищем значение: «Магазин 3», которое следует еще определить используя конструкцию сложения амперсандом текстовой строки «Магазин » и критерий из ячейки B15. Поэтому в первому аргументе функции указываем «Магазин »&B15. Во втором аргументе функции ПОИСКПОЗ указывается ссылка на просматриваемый диапазон A3:J3 где нужно искать исходное значение (указанное в первом аргументе). Третий аргумент содержит значение 0 – это значит, что функция возвратит результат, как только найдет первое совпадение значений. В нашем примере значение «Магазин 3» находится на позиции номер 6 в диапазоне A3:J3, а значит функция ПОИСКПОЗ возвращает число 6 которое будет использовано в качестве значения для третьего критерия функции ВПР. Есть еще и четвертый аргумент в функции ВПР который определяет точность совпадения найденного значения с критерием (0-точное совпадение; 1 или пусто – приближенное совпадение), но в формуле он опущен по следующей причине. Получив все аргументы функция ВПР не находит значения 370 000 и так как не указан последний аргумент выполняет поиск ближайшего значения в Excel – 350 000.

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

Легко заметить, что эта формула отличается от предыдущей только номером столбца указанном в третьем аргументе функции ВПР. А, следовательно, нам достаточно лишь к значению, полученному через функцию ПОИСКПОЗ добавить +1, так как сумма максимально возможной премии находиться в следующем столбце после минимальной суммы соответствующий критериям поискового запроса.

Полезные советы для формул с функциями ВПР, ИНДЕКС и ПОИСКПОЗ:

Чтобы пошагово проанализировать формулу Excel любой сложности, рационально воспользоваться встроенными инструментами в разделе: «ФОРМУЛЫ»-«Зависимости формул». Например, особенно полезный инструмент для пошагового анализа вычислительного цикла – это «Вычислить формулу».

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

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

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

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

Для начала создадим выпадающий список для поля АРТИКУЛ ТОВАРА, чтобы не вводить цифры с клавиатуры, а выбирать их. Для этого кликаем в соответствующую ячейку (у нас это F13), затем выбираем вкладку ДАННЫЕ – ПРОВЕРКА ДАННЫХ. В открывшемся окне в пункте ТИП ДАННЫХ выбираем СПИСОК. А в качестве источника выделяем столбец с артикулами, включая шапку. Так у нас получился выпадающий список артикулов, которые мы можем выбирать.

Теперь нужно сделать так, чтобы при выборе артикула автоматически выдавались значения в остальных четырех строках. Воспользуемся функцией ИНДЕКС. Записываем ее и параллельно изучаем синтаксис.

Массив. В данном случае это вся таблица заказов. Выделяем ее вместе с шапкой и фиксируем клавишей F4.

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

Записываем команду ПОИСКПОЗ и проставляем ее аргументы.

Искомое значение. В нашем случае это ячейка, в которой указывается артикул, т.е. F13. Фиксируем ее клавишей F4.

Просматриваемый массив. Т.к. мы ищем по артикулу, значит, выделяем столбец артикулов вместе с шапкой. Фиксируем F4.

Тип сопоставления. Excel предлагает три типа сопоставления: больше, меньше и точное совпадение. У нас конкретный артикул, поэтому выбираем точное совпадение. В программе оно значится как 0 (ноль). На этом аргументы ПОИСКПОЗ закончились.

Номер столбца. Опять же воспользуемся ПОИСКПОЗ. Искомым значением будет ячейка E14, где указано наименование параметра, который мы ищем (ТОВАР). Просматриваемый массив: шапка с наименованиями, потому что искать система будет по слову ТОВАР. Тип сопоставления: 0.

Синтаксис функции ИНДЕКС закончен. Как в итоге выглядит формула, видно на скриншоте выше. Видим, что артикул 3516 действительно у арахиса. Протянем формулу на остальные строки и проверим. Теперь, меняя артикул товара, мы будем видеть, кто его купил, сколько и почем.

5 вариантов использования функции ИНДЕКС (INDEX)

Бывает у вас такое: смотришь на человека и думаешь «что за @#$%)(*?» А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?

Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза «внешность обманчива» работает на 100%. Одна из наиболее многогранных и полезных — функция ИНДЕКС (INDEX) . Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.

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

Самый простой случай использования функции ИНДЕКС – это ситуация, когда нам нужно извлечь данные из одномерного диапазона-столбца, если мы знаем порядковый номер ячейки. Синтаксис в этом случае будет:

=ИНДЕКС( Диапазон_столбец ; Порядковый_номер_ячейки )

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

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

Вариант 2. Извлечение данных из двумерного диапазона

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

=ИНДЕКС( Диапазон ; Номер_строки ; Номер_столбца )

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

Легко сообразить, что с помощью такой вариации ИНДЕКС и двух функций ПОИСКПОЗ можно легко реализовать двумерный поиск:

Вариант 3. Несколько таблиц

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

=ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )

Обратите особое внимание, что в этом случае первый аргумент – список диапазонов — заключается в скобки, а сами диапазоны перечисляются через точку с запятой

Вариант 4. Ссылка на столбец / строку

Если во втором варианте использования функции ИНДЕКС номер строки или столбца задать равным нулю (или просто не указать), то функция будет выдавать уже не значение, а ссылку на диапазон-столбец или диапазон-строку соответственно:

Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM) , СРЗНАЧ (AVERAGE) и т.п

Вариант 5. Ссылка на ячейку

Общеизвестно, что стандартная ссылка на любой диапазон ячеек в Excel выглядит как Начало-Двоеточие-Конец, например A2:B5. Хитрость в том, что если взять функцию ИНДЕКС в первом или втором варианте и подставить ее после двоеточия, то наша функция будет выдавать уже не значение, а адрес, и на выходе мы получим полноценную ссылку на диапазон от начальной ячейки до той, которую нашла ИНДЕКС:

Нечто похожее можно реализовать функцией СМЕЩ (OFFSET) , но она, в отличие от ИНДЕКС, является волатильной, т.е. пересчитывается каждый раз при изменении любой ячейки листа. ИНДЕКС же работает более тонко и запускает пересчет только при изменении своих аргументов, что ощутимо ускоряет расчет в тяжелых книгах по сравнению со СМЕЩ.

Один из весьма распространенных на практике сценариев применения ИНДЕКС в таком варианте — это сочетание с функцией СЧЁТЗ (COUNTA) , чтобы получить автоматически растягивающиеся диапазоны для выпадающих списков, сводных таблиц и т.д.

Оценка индекса доходности инвестиции в Excel

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

Первый вариант расчета индекса доходности следующий:

1. Денежный поток CF (Cash Flow) =C8-D8
2. Дисконтированный денежный поток =E8/(1+$C$4)^A8
3. Чистый дисконтированный денежный поток (NPV) =СУММ(F8:F16)-B7
4. Индекс доходности (PI) =F17/B7

На рисунке ниже показан итоговый результат расчета PI в Excel.

Расчет в Excel индекса доходности (PI) инвестиции

Второй вариант расчета индекса доходности инвестиционного проекта заключается в использовании встроенной финансовой формулы в Excel – ЧПС (чистая приведенная стоимость) для расчета чистого дисконтированного дохода (NPV). В результате формулы расчета будут иметь следующий вид:

1. Дисконтированный денежный поток (NPV) =ЧПС(C4;E7:E16)-B7
2. Индекс прибыльности (PI) =E17/B7

Второй вариант расчета индекса доходности (PI) в Excel

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

★ Программа InvestRatio – расчет всех инвестиционных коэффициентов в Excel за 5 минут(расчет коэффициентов Шарпа, Сортино, Трейнора, Калмара, Модильянки бета, VaR) + прогнозирование движения курса

Функция 24: INDEX (ИНДЕКС)

Функция INDEX (ИНДЕКС) возвращает значение или ссылку на значение. Используйте её в сочетании с другими функциями, такими как MATCH (ПОИСКПОЗ), чтобы создавать могучие формулы.

Как можно использовать функцию INDEX (ИНДЕКС)?

Функция INDEX (ИНДЕКС) может возвратить значение или ссылку на значение. Вы можете использовать ее, чтобы:

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

Синтаксис INDEX (ИНДЕКС)

Функция INDEX (ИНДЕКС) имеет две синтаксические формы – массива и ссылочная. Форма массива возвращает значение, а ссылочная – ссылку.

Форма массива имеет вот такой синтаксис:

• array (массив) – массив констант или диапазон ячеек.
• Если аргумент array (массив) имеет только 1 строку или 1 столбец, то соответствующий аргумент с номером строки/столбца не обязателен.
• Если аргумент array (массив) содержит более 1-й строки и 1-го столбца:
  • и задано значение только аргумента row_num (номер_строки), то будет возвращен массив всех значений этой строки.
  • если же задано значение только аргумента column_num (номер_столбца), то массив всех значений этого столбца.
• Если аргумент row_num (номер_строки) не указан, то требуется указать column_num (номер_столбца).
• Если аргумент column_num (номер_столбца) не указан, то требуется указать row_num (номер_строки).
• Если указаны оба аргумента, то функция возвращает значение ячейки на пересечении указанных строки и столбца.
• Если в качестве аргумента row_num (номер_строки) или column_num (номер_столбца) указать ноль, то функция возвратит массив значений всего столбца или всей строки соответственно.

Ссылочная форма имеет вот такой синтаксис:

• reference (ссылка) может ссылаться на один или несколько диапазонов ячеек, включая несмежные диапазоны, которые должны быть заключены в круглые скобки.
• Если каждая область в reference (ссылка) имеет только 1 строку или 1 столбец, то соответствующий аргумент с номером строки/столбца не обязателен.
• area_num (номер_области) указывает номер области в аргументе reference (ссылка), из которой нужно вернуть результат.
• Если аргумент area_num (номер_области) не указан, то будет выбрана 1 область.
• Если аргумент row_num (номер_строки) или column_num (номер_столбца) равен нулю, то функция возвратит ссылку на весь столбец или всю строку соответственно.
• Результат – это ссылка, которая может быть использована другими функциями.

Ловушки INDEX (ИНДЕКС)

Если row_num (номер_строки) и column_num (номер_столбца) указывают на ячейку, не принадлежащую заданному массиву или ссылке, функция INDEX (ИНДЕКС) сообщит об ошибке #REF! (#ССЫЛКА!).

Создание формулы подстановки с помощью мастера подстановок (толькоExcel 2007 )

Примечание: Надстройка «Мастер подстановок» прекращена в Excel 2010. Эти функциональные возможности заменены мастером функций и доступными функциями поиска и работы со ссылками (ссылками).

В Excel 2007 мастер подстановок создает формулу подстановки на основе данных листа, имеющих заголовки строк и столбцов. Мастер подстановок помогает находить другие значения в строке, когда вы знаете значение в одном столбце, и наоборот. Мастер подстановок использует индекс и СОВПАДЕНИе в создаваемых формулах.

Щелкните ячейку в диапазоне.

На вкладке формулы в группе решения нажмите кнопку Подстановка .

Если команда подстановка недоступна, необходимо загрузить мастер подстановок надстройка программу.

Загрузка программы-надстройки «Мастер подстановок»

Нажмите кнопку Microsoft Office , щелкните Параметры Excelи выберите категорию надстройки.

В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.

В диалоговом окне надстройки установите флажок Мастер подстановоки нажмите кнопку ОК.

Постановка задачи

Если вы продвинутый пользователь Microsoft Excel, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно – без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?

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

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

Способ 1. Дополнительный столбец с ключом поиска

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

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

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы : Простой способ, знакомая функция, работает с любыми данными.

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

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS) , появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

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

Минусы : Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

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

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

Нажмите в конце не Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести формулу не как обычную, а как формулу массива.

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы : Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

Как в экселе делаются нижние индексы

Можно ли как-нибудь добавить верхние/нижние индексы в Label

Нижние и верхние индексы в VBДрузья напишите код для нижных и верхних индексов в VB. Например я хочу написать код воду (H2O) как.

Как делаются форумыЗдрасте всем! Хочу научиться делать форумы. В данный момент интересует техника добавления.

Как делаются такие в ВК?Подскажите пожалуйста как сделать так как на картинке?

Выделить нужную ячейку Нажать F2 (или двойным кликом) Выделить нужный символ Нажать Ctrl+U Нажать Enter.

Если надо подчеркнуть текст всей ячейки, то просто Ctrl+U — Enter

Спасибо авторам за понятные и хорошие ответы. А как сделать так, чтобы нижний индекс стал не у редактируемого символа, а у переменной? Когда в итоге работы формулы, должно быть нижнее индексное значение у одной из переменных? см. рисунок:

На рисунке в ячейке G6 есть ссылка на ячейку E6 которая должна быть в виде нижнего индекса т.е. В классе 1010002% девочек и 1001 мальчиков. должно выглядеть так: В классе 101000₂% девочек и 1001 мальчиков. Обычное форматирование отключается при выборе переменных в формуле, а редактирование ячейки E6 не приводит к изменению ячейки G6. Заранее спасибо.

Спасибо. Вот файл в нем в столбце G должны быть задачи с ответами для вставки в тест. Причем в различных системах счисления. т.е. в моем примере в ячейке G6 должна быть двоичная система счисления и выглядеть это должно так: «В классе 101000₂% девочек и 1001₂ мальчиков. Сколько учеников в классе? (в ответ запиши одно десятичное число) =15″

в ячейке G7 должна быть восьмеричная: «В классе 50₈% девочек и 17₈ мальчиков. Сколько учеников в классе? (в ответ запиши одно десятичное число) =25″

а в G8 шестнадцатеричная: «В классе 28₁₆% девочек и 12₁₆ мальчиков. Сколько учеников в классе? (в ответ запиши одно десятичное число) =30″

Сочетание с функцией ПОИСКПОЗ

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

Оператор ПОИСКПОЗ возвращает положение указанного значения в выделенном диапазоне ячеек.

Формула функции выглядит следующим образом:

.

Давайте “пробежимся” по аргементам функции:

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

Использование ПОИСКПОЗ позволяет автоматически заполнить аргументы “Номер строки” и/или “Номер столбца” функции ИНДЕКС.

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

Наша задача – используя ИНДЕКС и ПОИСКПОЗ добавить в ячейку со стоимостью (G2) такую функцию, которая будет выводить конкретное значение в зависимости от того, что будет указано в ячейке с наименованием рядом.

Для начала, заполняем ячейку с наименованием. Можно просто скопировать и вставить значение из основной таблицы.
Теперь встаем в ячейку, в которой планируем отображать результат, и жмем кнопку “Вставить функцию” (fx).
Выбираем функцию ИНДЕКС из списка операторов.
Выбираем список аргументов для массива и жмем OK.
Приступаем к заполнению аргументов:
в значении “Массива” указываем ячейки столбца, содержащего цены позиций. Сделать это можно вручную или с помощью выделения ячеек в самой таблице.
в поле аргумента “Номер_строки” требуется добавить оператор ПОИСКПОЗ. Для этого делаем следующее:
перейдя в поле для заполнения данного аргумента щелкаем по небольшой стрелке вниз в поле с именем ячейки (в котором будет отображаться название текущей функции), расположенным слева от кнопки “Вставить функцию”.
в раскрывшемся перечне выбираем пункт “Другие функции”.
в очередном окне Мастера функций выбираем категорию “Ссылки и массивы”, находим оператор “ПОИСКПОЗ”, отмечаем его и жмем OK.
в аргументе “Искомое_значение” указываем адрес ячейки, по содержимому которого будет выполняться поиск в основном массиве (в нашем случае – это F2). В “Просматриваемом_массиве” указываем вручную или с помощью выделения в самой таблице диапазон ячеек, в котором будет производиться поиск искомого значения

В аргумент “Тип_сопоставления” указываем цифру .

Обращаем внимание на строку формул. Здесь мы левой кнопкой мыши щелкаем по слову “ИНДЕКС”.

появится снова список аргументов функции ИНДЕКС

Мы можем заметить, что в результате проделанных выше действий, поле “Номер_строки” заполнилось автоматически. Так как выделенный массив одномерный и содержит только один столбец, последний аргумент оставляем незаполненным и жмем кнопку OK.Примечание: заполнить аргумент “Номер_строки” можно и вручную, ориентируясь на синтаксис функции ПОИСКПОЗ.

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