Значение ячейки по адресу excel

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) , чтобы получить автоматически растягивающиеся диапазоны для выпадающих списков, сводных таблиц и т.д.

Пример 1: Денежный квартал по номеру месяца

Функция CHOOSE (ВЫБОР) непревзойденно работает с ординарными перечнями чисел в качестве значений. К примеру, если ячейка B2 содержит номер месяца, функция CHOOSE (ВЫБОР) может вычислить, к какому денежному кварталу он относится. В последующем примере денежный год начинается в июле.

В формуле перечислено 12 значений, соответственных месяцам от 1 до 12. Денежный год начинается в июле, так что месяцы 7, 8 и 9 попадают в 1-ый квартал. В таблице, представленной ниже, Вы сможете узреть номер денежного квартала под каждым номером месяца.

В функцию CHOOSE (ВЫБОР) номер квартала нужно вводить в том порядке, в каком они размещены в таблице. К примеру, в перечне значений функции CHOOSE (ВЫБОР) в позициях 7, 8 и 9 (июль, август и сентябрь) обязано стоять число 1.

=CHOOSE(C2,3,3,3,4,4,4, 1,1,1 ,2,2,2) =ВЫБОР(C2;2;3;3;3;4;4;4; 1;1;1 ;2;2;2)

Введите номер месяца в ячейку C2, и функция CHOOSE (ВЫБОР) вычислит номер денежного квартала в ячейке C3.

Внедрение функции

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

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

Итак, метод действий последующий:

1. Перебегаем во вкладку “Данные”, в которой жмем на клавишу “Анализ “что если” в группе инструментов “Прогноз”. В раскрывшемся перечне избираем “Подбор параметра” (в ранешних версиях клавиша может находиться в группе “Работа с данными”).
2. На дисплее покажется окно для подбора параметра, которе необходимо заполнить:

Для чего нужна функция

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

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

Связка фукнций ИНДЕКС и ПОИСКПОЗ

Давайте найдем телефон Ивана. Создаем формулу с функцией ИНДЕКС.

Так как нам нужен телефон, то искать мы его будем в соответствующем столбце таблицы, поэтому выделяем его. Далее мы должны указать номер строки в этом диапазоне и тут на помощь приходит функция ПОИСКПОЗ. Так как нас интересует телефон Ивана, то мы бы хотели получить номер строки, в которой находится его имя. Указываем, что мы будем искать — имя «Иван», затем указываем где мы его будем искать — соответствующий диапазон столбца «Имя». Ищем текст, поэтому точное совпадение — 0.

Получаем верный результат.

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

Пример 2: Находим значение ячейки, используя номер строки и столбца

Функция ADDRESS (АДРЕС) возвращает адрес ячейки в виде текста, а не как действующую ссылку. Если Вам нужно получить значение ячейки, можно использовать результат, возвращаемый функцией ADDRESS (АДРЕС), как аргумент для INDIRECT (ДВССЫЛ). Мы изучим функцию INDIRECT (ДВССЫЛ) позже в рамках марафона 30 функций Excel за 30 дней.

Функция INDIRECT (ДВССЫЛ) может работать и без функции ADDRESS (АДРЕС). Вот как можно, используя оператор конкатенации “&“, слепить нужный адрес в стиле R1C1 и в результате получить значение ячейки:

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

1:5000 – это первые 5000 строк листа Excel.

Как найти значение в Excel

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

Не волнуйся. Функция поиска в Microsoft Excel поможет вам. Чтобы найти определенное значение ячейки:

1. Сначала выберите диапазон ячеек, в котором вы хотите выполнить поиск. Если вы хотите выполнить поиск по всей таблице, просто щелкните случайную ячейку.
2. Перейдите на главную> Найти и выбрать> Найти . Как вариант, вы также можете использовать комбинацию клавиш Ctrl + F.
3. Рядом с меткой « Найти» введите значение, которое вы хотите найти.
4. Excel выделит первое появление значения в электронной таблице. Нажмите кнопку « Найти далее» , чтобы перейти ко второй ячейке.

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

Используйте подстановочные знаки для поиска ячеек

Excel также позволяет использовать подстановочные знаки при поиске значений в электронной таблице.

1. Звездочка (*) : соответствует строке символов. Ab * будет соответствовать Abbott, Abandon, Absolutely и т. Д.
2. Вопросительный знак (?) : Соответствует одному символу. Ab? будет соответствовать Abc, Abd, Abz и т. д.

Например, для поиска значений, начинающихся с J в электронной таблице, введите «J *» в поле « Найти» .

Найти ячейки с определенным форматированием

Excel также позволяет искать форматирование ячейки, а не ее значение. Для этого нажмите Ctrl + F, чтобы открыть диалоговое окно « Найти и заменить ». Затем нажмите на опцию « Формат», расположенную справа.

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

Щелкните ОК . Затем выберите вариант « Найти далее» или « Найти все» . Excel перечислит все ячейки с тем же стилем форматирования, который вы указали.

Найти ячейки с формулами

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

Excel отобразит список параметров с флажками. Проверьте формулы и выберите подпункты, которые соответствуют вашим потребностям. Параметры « Числа» выделяют формулы, возвращающие числа, « Текст» – формулы, возвращающие текстовое значение, и т. Д.

После этого нажмите OK , и Excel выделит все ячейки с формулами, соответствующими критериям.

Вложенные ЕСЛИ (несколько условий)

Для построения более сложных проверок в одной формуле можно использовать несколько функций ЕСЛИ(). Для этого в качестве значений аргументов значение_если_истина и значение_если_ложь введите еще одно условие с функцией ЕСЛИ(). Например, для проверки в какой диапазон попадает значение из ячейки А8 запишите формулу

• Если значение из ячейки А8 менее 5, то будет выведено 1
• Если значение из ячейки А8 более 10, то будет выведено 3
• Если значение из ячейки А8 между 5 и 10 (включая 5 и 8), то будет выведено 2

Примечание : В версии EXCEL 2007 можно использовать до 64 вложенных друг в друга функций ЕСЛИ(). Но, пожалуйста, не делайте так. Такой подход чреват ошибками.

Примечание : Формулу =ЕСЛИ(A8 10;3;2)) можно переписать как =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) или ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1)

Формула =ЕСЛИ(A8 10;3;2)) работает следующим образом: сначала вычисляется логическое выражение A8 =5, то EXCEL переходит к вычислению выражения ЕСЛИ(A8>10;3;2). Опять сначала вычисляется логическое выражение A8>10. Если оно истинно, то выводится 3, вычисления завершены. Если оно ложно, то выводится 2, вычисления завершены.

Чтобы грамотно написать формулу с вложенными ЕСЛИ() следуйте следующим простым правилам:

1.1) они не должны пересекаться. В примере выше — 3 диапазона и значение из А8 гарантированно попадет только в один из них. Было бы ошибкой написать формулу типа =ЕСЛИ(A8 10;3;ЕСЛИ(A8 10;3;2)) на интересует вся числовая ось от минус бесконечности до +бесконечности. И все 3 диапазона полностью покрывают эту область.

1.3.) Не забудьте ставить знак равно, чтобы при значениях А8 совпадающих с границами интервалов не было неправильных вычислений. Например, если в формуле =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) забыть указать знак = в логическом выражении A8>=5, то при А8=5 формула =ЕСЛИ(A8>5;ЕСЛИ(A8>10;3;2);1) вернет 1, тогда как исходная формула вернет 2.

2) Правильно записывайте логические выражения. Например, условие 5 10;3;2)) в строке формул можно записать как

Для этого поставьте курсор перед вторым ЕСЛИ(), нажмите и удерживайте клавишу ALT, нажмите ENTER.

4) для отладки вложенных ЕСЛИ вместо реальных вычислений (V8*10%-37500) используйте простые значения — последовательности 1; 2; 3 . Это покажет какой блок вложенных ЕСЛИ() срабатывает.

5) В ряде случаев от вложенных ЕСЛИ можно отказаться. Рассмотрим пример определения размера проекта от его бюджета

=ЕСЛИ(A1>=100;»Очень крупный проект»; ЕСЛИ(A1>=90;»Крупный проект»; ЕСЛИ(A1>=50;»Средний проект»;»Малый проект «)))

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

Для функции ВПР() необходимо создать в диапазоне A3:B6 таблицу значений:

Формула =ПРОСМОТР(A1;;) выглядит необычно, но по сути второй аргумент , как и третий аргумент это запись содержимого 4-х ячеек. Вместо можно записать ссылку на диапазон ячеек, содержащих 4 значения 0;50;90;100. Круглые скобки нужны, чтобы сообщить, что в функцию передается не одно значение, а целый массив.

Еще пример. Пусть требуется вывести разный текст (например » 0″) в случае наличия в ячейке А1 отрицательного значения, положительного значения или 0. Это можно сделать без вложенных ЕСЛИ():

Примечание : -1E+307 — это самое маленькое отрицательное число в EXCEL, 1E-307 — это самое маленькое положительное число. Таким образом задается 3 диапазона: от минус бесконечности до 0 (0 не включая); 0 (диапазон состоит только из одного значения 0); от 0 до +бесконечности (0 не включая).

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

Диапазон A27:A29 должен, понятно, содержать формулы (см. файл примера).

Ссылочная форма

Функция ИНДЕКС() позволяет использовать так называемую ссылочную форму. Поясним на примере.

Пусть имеется диапазон с числами ( А2:А10 ) Необходимо найти сумму первых 2-х, 3-х, …9 значений. Конечно, можно написать несколько формул =СУММ(А2:А3) , =СУММ(А2:А4) и т.д. Но, записав формулу ввиде:

=СУММ(A2:ИНДЕКС(A2:A10;4))

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

Использование функции ИНДЕКС() в этом примере принципиально отличается от примеров рассмотренных выше, т.к. функция возвращает не само значение, а ссылку (адрес ячейки) на значение. Вышеуказанная формула =СУММ(A2:ИНДЕКС(A2:A10;4)) эквивалентна формуле =СУММ(A2:A5)

Аналогичный результат можно получить используя функцию СМЕЩ()

=СУММ(СМЕЩ(A2;;;4))

Теперь более сложный пример, с областями.

Пусть имеется таблица продаж нескольких товаров по полугодиям.

Задав Товар , год и полугодие , можно вывести соответствующий объем продаж с помощью формулы =ИНДЕКС((B9:C12;D9:E12;F9:G12);B15;A19;B17)

Вся таблица как бы разбита на 3 подтаблицы (области), соответствующие отдельным годам: B9:C12 ; D9:E12 ; F9:G12 . Задавая номер строки, столбца (в подтаблице) и номер области, можно вывести соответствующий объем продаж. В файле примера , выбранные строка и столбец выделены цветом с помощью Условного форматирования .

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

​ двумя полезными функциями​ месяц и тип​ИНДЕКС​ которой соответствует искомому​ чисел ближайшее к​-1​в качестве значения​ может быть использован​ таком случае напишем​

​Просматриваемый массив. Т.к. мы​ будет видеть, что​Результат​ таковой другими функциями.​ ИНДЕКС используется область 1. ​ скобки . Если​Данные​ опущен, аргумент «номер_столбца»​ Microsoft Excel –​ товара, получить объем​возвращает содержимое ячейки,​ значению. Т.е. данная​ правильному.​, результатом будет наименьшее​ аргумента​ другими функциями, такими​

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

​ два нуля.​ ищем по артикулу,​ это за товар,​=ИНДЕКС(A2:C6;2;3)​ В зависимости от​ Все указанные здесь​ вы попытаетесь ввести​

​Яблоки​ является обязательным.​ПОИСКПОЗ​ продаж.​ которая находится на​ функция возвращает не​Функция​ значение, которое больше​match_type​ как​Скачать примеры использования функций​ значит, выделяем столбец​

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

​Бананы​ — необязательный аргумент.​ИНДЕКС​ указанный нами месяц,​ и столбца. Например,​ его местоположение в​возвращает модуль разницы​ ему. В нашем​

​ поиск точного совпадения.​(ИНДЕКС) или​Получили простейшую формулу, помогающую​ шапкой. Фиксируем F4.​ куплено и по​ диапазоне A2:C6, т. е.​

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

​ вывести максимальное значение​Тип сопоставления. Excel предлагает​

​ какой общей стоимости.​ содержимое ячейки C3.​ или как значение.​ находящиеся на разных​ вводе формулы не​Формула​

​ массиве, из которого​ простых примерах, а​Май​ формула возвращает значение​Например, на рисунке ниже​

​ и правильным числами.​ равно 54. Поскольку​ точное совпадение текстовой​(ВПР). Например:​ из массива. Протянем​ три типа сопоставления:​ Сделать это поможет​38​ Например, формула ЯЧЕЙКА(«ширина»;ИНДЕКС(A1:B2;1;2))​ листах, произойдет ошибка​ нажать клавиши CTRL+SHIFT+ВВОД,​

​Описание​ требуется возвратить значение.​ также посмотрели их​. А ячейка C16​ из диапазона A1:C4,​ формула вернет число​Функция​ такого значения нет​ строки, то в​Найти положение элемента в​ ее вправо, получив​ больше, меньше и​

​ функция ИНДЕКС совместно​=ИНДЕКС((A1:C6;A8:C11);2;2;2)​ эквивалентна формуле ЯЧЕЙКА(«ширина»;B1).​ #ЗНАЧ! Если необходимо​ она возвратит ошибку​Результат​ Если аргумент «номер_столбца»​ совместное использование. Надеюсь,​ — тип товара,​ которое находится на​5​MIN​ в списке баллов,​

​ искомом значении допускается​

Поиск индекса максимального числа массива в Excel

​ несортированном списке.​ аналогичную информацию по​ точное совпадение. У​ с ПОИСКПОЗ.​Пересечение второй строки и​ Функция ЯЧЕЙКА использует​ использовать диапазоны, находящиеся​ #ЗНАЧ!​

​=ИНДЕКС(A2:B3;2;2)​ опущен, аргумент «номер_строки»​ что данный урок​ например,​

​ пересечении 3 строки​, поскольку имя «Дарья»​(МИН) находит наименьшую​ то возвращается элемент,​ использовать символы подстановки.​Использовать вместе с​ цене и сумме.​

​ нас конкретный артикул,​Для начала создадим выпадающий​ второго столбца во​ значение, возвращаемое функцией​ на разных листах,​К началу страницы​Значение ячейки на пересечении​

​ является обязательным.​ Вам пригодился. Оставайтесь​

​Овощи​ и 2 столбца.​ находится в пятой​ из разниц.​ соответствующий значению 60.​В следующем примере, чтобы​

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

​ искомое слово. Думаю,​ результат.​ нужно из нескольких​ двумя столбцами как​ как вспомогательную в​ указанном диапазоне.​Формула ищет в C2:C10​(ПОИСКПОЗ) использована для​

​=ПОИСКПОЗ(D5;{«Jan»;»Feb»;»Mar»};0)​(ИНДЕКС), чтобы найти​

​ численность населения Воронежа​

​=ВПР​

• ​41​находит первое значение,​возвращает значение 2, поскольку​ ошибку. С помощью​ не соответствуют искомому​ что анимация, расположенная​Введите в строке формул​ сделать один!​

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

  ​ того, чтобы найти​​Вы можете преобразовать оценки​ ближайшее значение.​ в четвертом столбце​(2345678;A1:E7;5)​Формула​ равное аргументу​

• ​ элемент 25 является вторым​ функции ЕОШИБКА мы​ выражению, и номер​ выше, полностью показывает​ в нее следующую​

​Добавим рядом с нашей​ использовали в ее​ функциями такими как:​ разделе, посвященном функции​ значению​ из нескольких угаданных​ учащихся в буквенную​​Функция​​ (столбец D). Использованная​. Формула возвращает цену​Описание​искомое_значение​ в диапазоне.​ проверяем выдала ли​​ столбца, в котором​ ​ задачу.​​ формулу:​ таблицей еще один​ аргументах оператор &.​

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

​(ПОИСКПОЗ) имеет следующий​ ячейке A14.​ потому что функция​=ПОИСКПОЗ(39;B2:B5,1;0)​Просматриваемый_массив​ Функцией​ Если да, то​ найдено.​Пример 1. Первая идя​ Enter, а сочетание​ название товара и​ первый аргументом для​ какую пользу может​Для выполнения этой задачи​ значение в ячейке​Функция​(ПОИСКПОЗ) так же,​ синтаксис:​

​Краткий справочник: обзор функции​ ВПР нашла ближайшее​Так как точного соответствия​может быть не​ПОИСКПОЗ​ мы получаем значение​СУММ(($A$2:$D$9=F2)*СТОЛБЕЦ($A$2:$D$9))​ для решения задач​Ctrl+Shift+Enter​ месяц в единое​ функции теперь является​ приносить данная функция​ используется функция ГПР.​

​ C7 (​ABS​ как Вы делали​MATCH(lookup_value,lookup_array,)​

Использование функции ГПР

​ ВПР​ число, меньшее или​ нет, возвращается позиция​ упорядочен.​следует пользоваться вместо​ ИСТИНА. Быстро меняем​Наконец, все значения из​ типа – это​

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

​, чтобы ввести формулу​ целое с помощью​ значение FordМаркетинговый. По​ работая самостоятельно

Из​Важно:​100​возвращает модуль разницы​ это с​ПОИСКПОЗ(искомое_значение;просматриваемый_массив;)​Функции ссылки и поиска​ равное указанному (2345678).​ ближайшего меньшего элемента​-1​ одной из функций​ её на ЛОЖЬ​ нашей таблицы суммируются​

​ при помощи какого-то​ не как обычную,​ оператора сцепки (&),​ этой причине первый​ самого названия функции​  Значения в первой​).​ между каждым угаданным​VLOOKUP​lookup_value​ (справка)​ Эта ошибка может​ (38) в диапазоне​Функция​ПРОСМОТР​ и умножаем на​ (в нашем примере​ вида цикла поочерёдно​ а как формулу​ чтобы получить уникальный​ Ford из отдела​ ПОИСКПОЗ понятно, что​

Еще о функциях поиска

• ​ строке должны быть​Дополнительные сведения см. в​

• ​ и правильным числами.​(ВПР). В этом​

• ​(искомое_значение) – может​Использование аргумента массива таблицы​

​ привести к неправильному​

support.office.com>

Как узнать, что ячейка Эксель содержит латиницу

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

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

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

Использование специальной функции

Основная проблема поиска латинских букв, что их в два с половиной раза больше, чем цифр. Следовательно, нужно задавать программе цикл, состоящий из 26 итераций, что может быть довольно напряжно. Но если использовать формулу массива, состоящую из приведенных выше функций СЧЕТ и ПОИСК, то эта затея уже и не кажется настолько сложной: =СЧЁТ(ПОИСК({«a»:»b»:»c»:»d»:»e»:»f»:»g»:»h»:»i»:»j»:»k»:»l»:»m»:»n»:»o»:»p»:»q»:»r»:»s»:»t»:»u»:»v»:»w»:»x»:»y»:»z»};A1))>0. Эта формула вполне подходит для большинства ситуаций. Например, если нет возможности установить соответствующие макросы, которые бы смогли делать это проще и быстрее.

В описанной выше формуле A1 – это та ячейка, в которой осуществляется проверка. Соответственно, вам нужно поставить ту, которая подходит в вашей ситуации. В результате проверки с помощью этой функции возвращается логическое значение. Если совпадения обнаруживаются, то оператор возвращает ИСТИНА, если их нет – ЛОЖЬ.

Функция ПОИСК не дает возможности учитывать регистр при поиске символов. Чтобы это сделать, надо воспользоваться оператором НАЙТИ, который выполняет те же операции, имеет такие же самые аргументы, только уже учитывает регистр. Еще один способ – сделать описанную выше формулу формулой массива. В таком случае она будет иметь следующий вид:{=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1))>0}.

Поскольку это формула массива, ее нужно указывать без скобок. При этом после ввода надо нажать комбинацию клавиш Ctrl + Shift + Enter (вместо простого нажатия клавиши ввода, как в случае с обычной функцией), после чего фигурные скобки появятся сами.

Если же нужно найти кириллицу, последовательность действий аналогичная, только в качестве диапазона поиска нужно задавать всю последовательность кириллических символов. =СЧЁТ(ПОИСК({«а»:»б»:»в»:»г»:»д»:»е»:»ё»:»ж»:»з»:»и»:»й»:»к»:»л»:»м»:»н»:»о»:»п»:»р»:»с»:»т»:»у»:»ф»:»х»:»ц»:»ч»:»ш»:»щ»:»ъ»:»ы»:»ь»:»э»:»ю»:»я»};A1))>0. Также можно юзать функцию СИМВОЛ, чтобы сделать это. {=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(192:223));A1))>0}

Эта формула должна записываться так, как формула массива. Следовательно, нужно нажать комбинацию клавиш Ctrl + Shift + Enter вместо простого нажатия клавиши ввода. Но есть некоторые исключения, при которых эта функция не будет работать. Нужно предварительно убедиться в том, что язык по умолчанию для программ не в кодировке Unicode стоит русский. В этом случае проблем возникнуть не должно. Эти формулы имеют некоторые отличия между собой. Вместо 33 букв последняя формула применяет только 32. То есть, она не учитывает букву ё, как кириллическую.

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