Excel ищет не все ячейки

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

Причины

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

К основным объяснениям возможных сбоев стоит отнести:

1. Большая длина фразы, объем которой более 255 символов. Такая проблема возникает редко.
2. На листе установлена защита, которая не дает вызвать нужную функцию.
3. Для ячеек установлен параметры «Скрывать формулы», а областью поиска являются «формулы».
4. Пользователь задает «Найти далее» вместо «Найти все». В таком случае выделения просто не будет видно.
5. Ошибки в задании области поиска. К примеру, установлен параметр «Значения», а нужно просмотреть необходимые формулы.
6. Установлен параметр «Ячейка целиком», а на практике поисковый запрос не имеет совпадения со значением секции. Нужно снять отметку «Ячейка целиком».
7. Задан показатель по формату или не обнулен после прошлого поиска.
8. Проблемы с Эксель и необходимость переустановки программы.

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

Что делать

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

Способ №1:

• Жмите на «Главная».
• Выберите «Найти и выделить».

• Кликните «Найти …».
• Введите символы для поиска.
• Жмите «Найти далее / все».

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

Способ №2 (по интервалу):

1. Выделите нужную область ячеек в Excel.
2. Жмите Ctrl+F на клавиатуре.
3. Введите нужный запрос и действуйте по рассмотренному выше методу.

Способ №3 (расширенный):

1. Войдите в «Найти и заменить».
2. Жмите на «Параметры».
3. Выберите инструменты для поиска.
4. Жмите на кнопку подтверждения.

Если вы все сделали правильно, но все равно не работает поиск в Эксель, попробуйте следующие шаги:

• Убедитесь, что количество введенных символов меньше 255. В ином случае функция не работает.
• Снимите защиту с листа Excel. Для этого войдите в «Файл», а далее «Сведения» и «Снять защиту листа». В случае, если установлен пароль, его необходимо ввести в диалоговом окне и подтвердить.

• Снимите параметр «Скрывать формулы» для ячейки и попробуйте запустить процесс в Excel еще раз.

• Задайте разные варианты поиска в Excel. Если не работает «Найти все», проверьте «Найти далее».
• Снимите отметку с пункта «Ячейка целиком».

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

Вариант №1

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

Вариант №2

Попробуйте удалить аргумент «нач_позиция», если в нем нет необходимости, или присвойте ему правильный параметр.

Вариант №3

Для определения числа символов в текстовой строке применяйте опцию ДЛСТР. При этом задайте правильный поисковый параметр.

Устранение сбоя

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

1. Запустите Excel в безопасном режиме и убедитесь, что он нормально работает. Для этого жмите и удерживайте Ctrl при запуске софта. В этом случае ПО пропускает ряд функций и параметров, которые могут привести к сбоям в работе. Если проблему не удалось решить путем запуска в безопасном режиме, переходите к следующему пункту. В ином случае отключите лишние настройки.
2. Установите последние обновления, которые могут помочь с устранением проблемы.
3. Убедитесь, что офис Excel не пользуется другим процессом. Эта информация должна быть в нижней части окна. Для устранения проблемы попробуйте закрыть посторонние процессы, а после этого снова проверьте, работает ли опция поиска.
4. Полностью удалите, а после этого поставьте программу Excel снова. Зачастую этот метод помогает, если Excel не ищет или не работает по какой-то причине.

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

Что еще попробовать

Если опция так и не работает в Excel, попробуйте дополнительные рекомендации:

• Убедитесь, что у вас правильная раскладка и вы действительно нажимаете Ctrl+F.
• Проверьте размер документа. Функция иногда зависает и не работает, если ПК / ноутбуку не хватает оперативной памяти из-за большого объема работы.
• Проверьте устройство на вирусы. Возможно, проблема возникает из-за вредоносного ПО.

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

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

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

Отличного Вам дня!

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

Причины

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

К основным объяснениям возможных сбоев относятся:

1. Длинное предложение, более 255 символов. Эта проблема встречается редко.
2. На лист устанавливается заглушка, не позволяющая вызвать нужную функцию.
3. Для ячеек установлено значение Скрыть формулы, а область поиска — «Формулы».
4. Пользователь указывает «Найти далее» вместо «Найти все». В этом случае выделение просто не будет видно.
5. Ошибки в настройке области поиска. Например, параметр «Значения» установлен, но требуемые формулы должны отображаться.
6. Параметр «Целая ячейка» установлен, но на практике поисковый запрос не соответствует значению раздела. Вам необходимо снять флажок «Вся ячейка».
7. Маркер формата установлен или не очищается после последнего поиска.
8. Проблемы с Excel и необходимость переустановки программы.

Это основные причины, по которым поиск в Excel не работает. Но все проблемы легко решить, если знать, как действовать.

Что делать

Во-первых, давайте вспомним, как правильно работает поиск в Excel. Пользователи могут выбирать из нескольких вариантов.

Способ №1:

• Щелкните «Домой».
• Выберите «Найти» и выберите».

• Щелкните «Найти …».
• Введите символы для поиска.
• Нажмите «Найти далее / все».

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

Способ №2 (по диапазону):

1. Выберите нужный диапазон ячеек в Excel.
2. Нажмите Ctrl + F на клавиатуре.
3. Введите требуемый запрос и действуйте, как описано выше.

Способ №3 (расширенный):

1. Введите «Найти и заменить».
2. Щелкните «Параметры».
3. Выберите инструменты поиска.
4. Нажмите кнопку подтверждения.

Если вы все сделали правильно, но поиск в Excel по-прежнему не работает, попробуйте выполнить следующие действия:

• Убедитесь, что количество введенных символов меньше 255. В противном случае функция не будет работать.
• Снимите защиту с листа Excel. Для этого перейдите в «Файл», затем в «Информация» и «Снять защиту с листа». Если пароль был установлен, вы должны ввести его в диалоговом окне и подтвердить.

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

• Выберите различные параметры поиска в Excel. Если «Найти все» не работает, выберите «Найти далее».
• Снимите флажок «Вся ячейка».

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

Вариант №1

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

Вариант №2

Попробуйте удалить аргумент start_position, если он вам не нужен, или укажите правильный параметр.

Вариант №3

Используйте опцию DLSTR, чтобы определить количество символов в текстовой строке. При этом установите правильный параметр поиска.

Устранение сбоя

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

1. Запустите Excel в безопасном режиме и убедитесь, что он работает правильно. Для этого удерживайте Ctrl при запуске программы. В этом случае программа пропускает ряд функций и параметров, которые могут привести к неисправности. Если проблема не решается загрузкой в ​​безопасном режиме, переходите к следующему шагу. В противном случае отключите ненужные настройки.
2. Установите последние обновления, чтобы решить проблему.
3. Убедитесь, что в Office Excel не используется другой процесс. Эта информация должна быть внизу окна. Чтобы исправить это, попробуйте закрыть посторонние процессы, а затем еще раз проверьте, работает ли опция поиска.
4. Полностью удалите, а затем повторно установите Excel. Этот метод часто помогает, если Excel по какой-то причине не пытается или не работает.

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

Что еще попробовать

Если этот параметр по-прежнему не работает в Excel, попробуйте воспользоваться дополнительными рекомендациями:

• Убедитесь, что у вас правильный макет, и нажмите Ctrl + F.
• Проверьте размер документа. Эта функция иногда дает сбой и не работает, если на вашем ПК / ноутбуке недостаточно оперативной памяти из-за большого объема работы.
• Проверьте свое устройство на вирусы. Проблема может быть связана с вредоносным ПО.

• Попробуйте установить более новую версию Excel. В этом случае рекомендуется полностью удалить старую версию и вычистить остатки.
• Убедитесь, что вы ввели правильные варианты расширенного поиска.

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

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

Причины

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

К основным объяснениям возможных сбоев относятся:

1. Длинное предложение, более 255 символов. Эта проблема возникает редко.
2. На лист устанавливается заглушка, не позволяющая вызвать нужную функцию.
3. Для ячеек установлено значение Скрыть формулы, а область поиска — «Формулы».
4. Пользователь указывает «Найти далее» вместо «Найти все». В этом случае выделение просто не будет видно.
5. Ошибки в настройке области поиска. Например, параметр «Значения» установлен, но требуемые формулы должны отображаться.
6. Параметр «Ячейка в целом» установлен, но на практике поисковый запрос не соответствует значению раздела. Вам необходимо снять флажок «Вся ячейка».
7. Маркер формата установлен или не очищается после последнего поиска.
8. Проблемы с Excel и необходимость переустановки программы.

Это основные причины, по которым поиск в Excel не работает. Но все проблемы легко решить, если знать, как действовать.

Дополнительные параметры поиска слов и фраз

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

Здесь вы можете указать дополнительные параметры поиска.

Исследовать:

• на листе — только на текущем листе;
• в книге: поиск по всему документу Excel, если он состоит из нескольких листов.

Навигация:

• по строкам — поисковая фраза будет выполняться слева направо от строки к строке;
• по столбцам — поисковая фраза будет выполняться сверху вниз от столбца к столбцу.

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

Область поиска — определяет, где именно искать совпадения:

• в формулах;
• в значениях ячеек (значения, уже рассчитанные по формулам);
• в примечаниях, оставленных пользователями к ячейкам.

А также дополнительные параметры:

• Прописные / строчные буквы — означает, что прописные и строчные буквы будут считаться разными.

Например, если вы проигнорируете регистр, запрос «excel» найдет все варианты этого слова, например Excel, EXCEL, ExCeL и т.д.

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

• Целая ячейка — установите этот флажок, если хотите найти ячейки, в которых поисковая фраза находится полностью и в которых отсутствуют другие символы. Например, есть таблица с множеством ячеек, содержащих разные числа. Поисковый запрос: «200». Если вы не проверяете всю ячейку, будут найдены все числа, содержащие 200, например: 2000, 1200, 11200 и т.д. Чтобы найти ячейки только с «200», вам нужно проверить всю ячейку. Тогда будут показаны только те, у которых точное совпадение с «200».
• Формат… — если вы установите формат, будут найдены только ячейки, содержащие желаемый набор символов, и ячейки будут иметь указанный формат (границы ячеек, выравнивание ячеек и т.д.). Например, вы можете найти все желтые ячейки, содержащие искомые символы.

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

Чтобы сбросить настройки формата поиска, нажмите «Очистить формат поиска.

Это меню вызывается нажатием стрелки справа от кнопки «Формат.

Что делать

Во-первых, давайте посмотрим, как правильно работает поиск в Excel. Пользователи могут выбирать из нескольких вариантов.

Способ №1:

• Щелкните «Домой».
• Выберите «Найти» и выберите».

• Щелкните «Найти …».
• Введите символы для поиска.
• Нажмите «Найти далее / все».

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

Способ №2 (по диапазону):

1. Выберите нужный диапазон ячеек в Excel.
2. Нажмите Ctrl + F на клавиатуре.
3. Введите требуемый запрос и действуйте, как описано выше.

Способ №3 (расширенный):

1. Введите «Найти и заменить».
2. Щелкните «Параметры».
3. Выберите инструменты поиска.
4. Нажмите кнопку подтверждения.

Если вы все сделали правильно, но поиск в Excel по-прежнему не работает, попробуйте выполнить следующие действия:

• Убедитесь, что количество введенных символов меньше 255. В противном случае функция не будет работать.
• Снимите защиту с листа Excel. Для этого перейдите в «Файл», затем в «Информация» и «Снять защиту листа». Если пароль был установлен, вы должны ввести его в диалоговом окне и подтвердить.

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

• Установите различные параметры поиска в Excel. Если «Найти все» не работает, выберите «Найти далее».
• Снимите флажок «Вся ячейка».

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

Вариант №1

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

Вариант №2

Попробуйте удалить аргумент start_position, если он вам не нужен, или укажите правильный параметр.

Вариант №3

Используйте опцию DLSTR, чтобы определить количество символов в текстовой строке. При этом установите правильный параметр поиска.

Устранение сбоя

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

1. Запустите Excel в безопасном режиме и убедитесь, что он работает правильно. Для этого удерживайте Ctrl при запуске программы. В этом случае программа пропускает ряд функций и параметров, которые могут привести к неисправности. Если проблема не может быть решена загрузкой в ​​безопасном режиме, переходите к следующему шагу. В противном случае отключите ненужные настройки.
2. Установите последние обновления, чтобы решить проблему.
3. Убедитесь, что в Office Excel не используется другой процесс. Эта информация должна быть внизу окна. Чтобы исправить это, попробуйте закрыть посторонние процессы, а затем еще раз проверьте, работает ли опция поиска.
4. Полностью удалите и переустановите Excel. Этот метод часто помогает, если Excel по какой-то причине не пытается или не работает.

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

Способ 2

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

Для этого нужно щелкнуть любую ячейку, среди которой нужно выполнить поиск, перейти на вкладку «Главная» — «Сортировка и фильтры» — «Фильтр.

В первой строке рядом с заголовками ячеек появляются раскрывающиеся стрелки.

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

Результат будет следующий.

Таблица до применения фильтра и таблица после применения фильтра.

Фильтрация не изменяет таблицу и не удаляет строки, она показывает только те строки, которые были найдены, их не нужно скрывать. Чтобы удалить фильтр, нужно нажать на стрелку в заголовке — Удалить фильтр из слова…

Вы также можете щелкнуть стрелку и выбрать Текстовые фильтры — Содержит и указать шрифты, которые вы ищете.

Затем введите желаемую фразу, например «Монако».

Результат будет следующим: только строки, содержащие слово «Монако».

Этот фильтр сбрасывается так же, как и предыдущий.

Таким образом, у пользователя есть варианты поиска слова в Excel: сам поиск и сам фильтр.

Видеоурок по теме

Поиск в программе Microsoft Excel

​Смотрите также​: На двух других​ значения как близнецы,​arikov299​ LookIn:=xlValues) FindVal =​ FindVal1(FindVal)​вот такие дела.​ и только этого​ различные принтеры, например​ или окружающей среды.​ Компьютере. Выполнение Выборочный​ОК​ COM будут исключены.​ на кнопку​ позицию​ на который она​ выдаче будут представлены​

​В документах Microsoft Excel,​ машинах (система XP​

Поисковая функция в Excel

​ без пробелов и​: Вот файл. Вы​ el.Row End Functionпрекрасно​- тоже работает,​Labuda​ столбца: «N по​ драйвер принтера записи​ Ниже описывается устранение​ запуск (также известную​

Способ 1: простой поиск

​.​Если проблему удалось​«Найти всё»​«По столбцам»​ ссылается. Например, в​ все ячейки, которые​ которые состоят из​ и XP&SP3, Офис-2003)​ даже такого же​

1. ​ уверены что должен​​ работает на рабочем​​ но все это​​: ВПР отлично работает,​​ порядку», все начинает​ XPS-документов Microsoft или​ дополнительных проблему, которая​​ как «чистой загрузки»)​​Если проблема устранена, щелкните​ решить после запуска​​или​​, можно задать порядок​ ячейке E2 содержится​ содержат данный последовательный​ большого количества полей,​​ то же.​​ формата, но функция​

   

2. ​ быть один столбец​ листе, если я​ относится к Office​ если аргумент ‘интервальный​ работать.​ видеодрайвера VGA будет​​ может привести к​​ помогает обнаружить проблемы​​Файл​​ Excel в безопасном​«Найти далее»​​ формирования результатов выдачи,​​ формула, которая представляет​ набор символов даже​ часто требуется найти​Serge​ не воспринимает их​​ и одна строка?​​ ее помещаю в​​ XP. Попробывал в​​ просмотр’ установить ‘ЛОЖЬ’.​

   

3. ​Вопрос не в​​ определить, является ли​​ сбою или повесить​ с приложениями конфликтующих.​>​ режиме, см.: Устранение​, чтобы перейти к​

   ​ начиная с первого​ собой сумму ячеек​ внутри слова. Например,​ определенные данные, наименование​: Если б заменялись​ как одинаковые. Помогает​ разве он в​ отдельный модуль.​ 2000 — болт​ я пока не​ помощи, вопрос в​

   ​ проблема с определенным​ в Excel.​ Выполните Выборочный запуск,​Параметры​ неполадок, возникающих при​ поисковой выдаче.​ столбца.​ A4 и C3.​ релевантным запросу в​ строки, и т.д.​ как обычно -​ только копирование ячейки,​ диапазоне не ищет?​КЛАСС!!!​ :-(. Однако не​ замечал ошибок в​ чем «фишка». Кто​ принтером или видеодрайвера.​Факторов окружающей среды​ выберите один из​

   ​>​ загрузке Excel.​Как видим, программа Excel​​В графе​​ Эта сумма равна​

   

   ​ этом случае будет​ Очень неудобно, когда​ не стал бы​ но проблема что​JayBhagavan​

4. ​Пожалуй я бы​ думаю, что это​ работе этой функции,​ знает, подскажите для​​Если вы по-прежнему красная​​При устранении неполадок сбои​ приведенных ниже ссылок​Надстройки​Если ваша проблема не​ представляет собой довольно​«Область поиска»​ 10, и именно​ считаться слово «Направо».​ приходится просматривать огромное​ тему создавать…​ таких значений много​: Не верьте -​ никогда не додумался​ глюк МС.​ даже с очень​ самообразования.​ или поврежденных после​ факторов окружающей среды​ в зависимости от​.​ устранена после запуска​ простой, но вместе​определяется, среди каких​ это число отображается​

Способ 2: поиск по указанному интервалу ячеек

​ Если вы зададите​ количество строк, чтобы​На листе Августей​ и все замучаешься​ проверьте. Читайте справку.​ поместить её в​to kNell​ большими массивами.​Z​ возникли проблемы в​ менее важен, чем​ установленной версии Windows,​Выберите​ Excel в безопасном​

1. ​ с тем очень​ конкретно элементов производится​ в ячейке E2.​

   

2. ​ в поисковике цифру​ найти нужное слово​​ этих как собак​​ копировать, помогите пожалуйста​arikov299​ отдельный модуль!​​Если ВПР Вам​​IgorTr​: Вопрос: сколько уникальных​ Excel при работе​ содержимое файла и​ а затем следуйте​надстройки COM​ режиме, перейдите к​ функциональный набор инструментов​

Способ 3: Расширенный поиск

​ поиск. По умолчанию,​ Но, если мы​ «1», то в​ или выражение. Сэкономить​ нерезанных (значение скопировал​ разобраться в чем​: justirus, вот так​Мир полон чудес…​ не нравится, можно​

​: Даааа — ун…​ в каждом из​ с помощью разрешение​ надстройки. Помогут определить​ инструкциям, приведенным в​и нажмите кнопку​ следующему пункту списка.​ поиска. Для того,​ это формулы, то​ зададим в поиске​ ответ попадут ячейки,​ время и нервы​ из ячейки содержащей​ дело. (Пример прикреплен)​ исправил: =ИНДЕКС(‘2′!A:Z394;ПОИСКПОЗ(Лист1!B2;’2′!26:26;0);ПОИСКПОЗ(Лист1!B3;’2’!A4:AB220;0))​Кстати, извините за​ предложить такой вариант​Эту хрень я​ полей?​ описанных здесь, нужно​ причину проблему, выполнив​ статье:​Перейти​При необходимости можно задать​ чтобы произвести простейший​ есть те данные,​ цифру «4», то​ которые содержат, например,​ поможет встроенный поиск​ его, Ctrl+C,Ctrl+F,Ctrl+V, перехожу​

​Sekach​не работает​ серость, но что​=СТРОКА(F1:F100)+ПОИСКПОЗ(‘rr’;F1:F100;0)-1​ тоже не понимаю.​boydak​

1. ​ связаться службы поддержки​​ следующие действия:​​Windows 10, Windows 7,​.​​ Центр обновления Windows​​ писк, достаточно вызвать​

   

2. ​ которые при клике​ среди результатов выдачи​ число «516».​ Microsoft Excel. Давайте​ на «Заменить», выбираю​:​justirus​ есть ‘собственые функции​предлагаемая формула возвращает​

   

   ​Function FindVal() Set​​: Z, записи в​​ Майкрософт для интерактивной​​Следуйте советам по устранению​​ Windows 8: Выборочный​Снимите все флажки в​ для автоматической загрузки​ поисковое окно, ввести​ по ячейке отображаются​ будет все та​Для того, чтобы перейти​ разберемся, как он​ «Заменить все») -​Sekach​: Так попробуйте: =ИНДЕКС(‘2′!A:F;ПОИСКПОЗ(Лист1!B2;’2′!A:A;0);ПОИСКПОЗ(Лист1!B3;’2′!A1:F1;0))​ рабочего листа’?​ номер строки первой​ el = Range(‘f1:f100′).Find(What:=’rr’,​ поле ФИО уникальны​ устранения неполадок.​ неполадок.​ запуск при помощи​ списке и нажмите​​ и установки рекомендованные​​ в него запрос,​ в строке формул.​ же ячейка E2.​ к следующему результату,​ работает, и как​ выдаёт нет совпадений!!!​, Данные — Текст​justirus​В некоторых книжках​ ячейки столбца F,​

   

   ​ LookIn:=xlValues) FindVal =​boydak​boydak​Проверка файлов в cleanest​​ программы настройки системы​​кнопку ОК​ обновления. Установка никаких​​ и нажать на​​ Это может быть​ Как такое могло​ опять нажмите кнопку​

   

   ​ им пользоваться.​​Ума не приложу…​​ по столбцам -​: Но структура файла​ так называют пользовательские​ значение котрой равно​ el.Row End Function​: Ура!​: Доброго времени суток!​​ среде.​​Windows Vista: Запуск​.​ важных рекомендаций и​ кнопку. Но, в​

   

   ​ слово, число или​​ получиться? Просто в​​«Найти далее»​Скачать последнюю версию​ И говорю вам-раньше​ формат Общий, проделайте​ непонятна, зачем писать​ функции, если их​ ‘rr’​ Sub FindVal1() Set​Для тех кто​Работаю в excel2013,​В следующих разделах описаны​ Выборочный запуск помощи​Закройте приложение Excel и​ оптимизации обновлений часто​ то же время,​ ссылка на ячейку.​ ячейке E2 в​.​ Excel​ заменялось всё нормально…​ так с каждым​ формулу с поиском,​ вставлять на рабочий​IgorTr​ el = Range(‘f1:f100′).Find(What:=’rr’,​​ столкнется с подобным.​​ получил для правки​​ некоторые области, которые​​ программы настройки системы​ снова запустите его.​ можно Устранение проблем​ существует возможность настройки​ При этом, программа,​​ качестве формулы содержится​​Так можно продолжать до​

   

   ​Поисковая функция в программе​Грешу на глюк…​ столбцом по отдельности,​​ если аргументы формулы​​ лист. ИМХО.​

   

   ​: Господа!​ LookIn:=xlValues) Cells(1, 1)​У меня было​ файл *.xls, т.е.​ стоит узнать.​Windows XP: как​Если проблема не возникает​ с заменив устаревшие​ индивидуального поиска с​ выполняя поиск, видит​ адрес на ячейку​ тех, пор, пока​ Microsoft Excel предлагает​

   

   ​Dophin​ тогда все сверяется​ не будут меняться​Татьяна​Win 2000, Office​​ = el.Row End​​ следующее: в поле​

   

   ​ должен буду вернуть​Где хранится файл​ создать и настроить​ при запуске Excel,​ файлы и корректировать​ большим количеством различных​

   

   ​ только ссылку, а​ A4, который как​ отображение результатов не​​ возможность найти нужные​​: кусочек файла можно?​

   

   ​ Нет данных только​ при растягивании? Или​: Добрый день!​ 2000 (9.0.4402 SR1)​ SubДаже в таком​ № п/п в​ в этом же​Перемещение файла на локальном​ учетные записи пользователей​ начните Включение надстройками​ уязвимостей. Установка последних​ параметров и дополнительных​ не результат. Об​ раз включает в​ начнется по новому​ текстовые или числовые​Guest​ в 271 строке​

   

3. ​ это еще только​В Excel 2007​И Sub и​​ виде макрос работает,​​ низу стояло «Всего…».​​ формате.​​ компьютере помогут определить,​ в Windows XP​

​ один во время,​ обновлений для Office,​ настроек.​ этом эффекте велась​ себя искомую цифру​ кругу.​ значения через окно​: Скорее всего эксель​Спасибо огромное, работает!​ наброски?​ не работает поиск​ Function ваши прекрасно​ а функция нет.​ Путем удаления этой​Проблема​ есть ли проблема​Выборочный запуск используется для​ пока не позволяет.​

​ выполните действия, описанные​

lumpics.ru

Приложение Excel не отвечает, зависает или прекращает работать

​Автор: Максим Тютюшев​​ речь выше. Для​ 4.​В случае, если при​ «Найти и заменить».​ не находит Август​ А не подскажите​UPD: Понял вашу​ функций в мастере​ работают!​ ИМХО очередной глюк​ ячейки или добавлением​в том, что​ с файлом или​ выявления оскорбительного процесс,​ Это позволит выяснить,​ в этой статье:​Примечание:​ того, чтобы производить​Но, как отсечь такие,​ запуске поисковой процедуры​

​ Кроме того, в​ 2009, потомучто для​ изза чего такая​ задумку, каждое отделение​ фунцкий — сбрасывает​Может проблема в​ от любимой всеми​ пустой строки между​ поиск ФИО в​ в котором сохранен​ службы или приложения,​ какие надстройки является​ Обновление Office и​ Мы стараемся как можно​ поиск именно по​

​ и другие заведомо​ вы нажмете на​ приложении имеется возможность​ него это число!​ беда происходит? Так​ будет на отдельном​ написанное, даже если​ отсутствии SR1 -​

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

Запустите Excel в безопасном режиме

​ кнопку​ расширенного поиска данных.​ (К примеру 01.08.2009)​ сказать для общего​ листе, а валюта​ введено название функции,​ остается только гадать.​Ну наверное предлагать​​ таблицей ПОИСК в​​ результат (в таблице​ могут возникнуть при​Если ваша проблема​ что и перезапустите​Если установка последних обновлений​ актуальными справочными материалами​ данным, которые отображаются​ поиска? Именно для​«Найти все»​Простой поиск данных в​Serge​ развития и что​ будет новыми столбцами,​
​ которая указана в​brovey​ делать поиск тупым​ фильтре «ЗАРАБОТАЛ»​ более 15 000​ сохранении файла Excel​

​ не устранена после​ каждый раз при​ для Office не​ на вашем языке.​ в ячейке, а​

Установка последних обновлений

​ этих целей существует​, все результаты выдачи​ программе Excel позволяет​: Это в любом​ бы выставлять правильный​ листа 2?​ «выберите функцию».​: 2brovey​ перебором ячеей не​Как говорит «создатель»:​ строк). Ctrl+F проблему​ по сети или​ повторного создания профиля,​ включении надстройки Excel.​ решила проблему, перейдите​ Эта страница переведена​

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

Проверка того, что Excel не используется другим процессом

​ формат значений в​Тогда формула усложнится))​Serge​Стоп!​ прилично?​ «Когда знаешь, всё​ решает, но создает​ на веб-сервере. Рекомендуется​ перейдите к следующему​Если отключение надстроек не​ к следующему пункту​ автоматически, поэтому ее​ формул, нужно переставить​После открытия окна​

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

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

​CAHO​: Дим, я тут​По опроеделению самой​kNell​ просто! «​ лишние трудности.​ сохранить файл на​ пункту в списке.​

1. ​ решило проблему, перейдите​ списка.​

   • ​ текст может содержать​ переключатель из позиции​​«Найти и заменить»​​ нижней части поискового​​ введенный в поисковое​​ Excel. Всё равно.​​ не было​​: =ИНДЕКС(‘2′!A:F;ПОИСКПОЗ(Лист1!B$2;’2’!A:A;0);5)​​ как то сам​​ МС.​​: ВПР часто ошибается​​Спасибо Всем за​​Мучает​​ локальном компьютере. Это​Восстановление программ Office может​​ к следующему пункту​​Если Excel используется другим​

   • ​ неточности и грамматические​«Формулы»​любым вышеописанным способом,​​ окна. В этом​​ окно набор символов​ Сейчас комп перезагружу.​​Sekach​​arikov299​​ спрашивал и точно​​Процедура Sub X…()​ если различие в​​ участие!​​вопрос:​

   • ​ следует делать в​ устранить проблемы с​ в списке.​​ процессом, эта информация​​ ошибки. Для нас​в позицию​​ жмем на кнопку​​ списке находятся информация​ (буквы, цифры, слова,​​ Если надо ОСу​​, в файле разные​

2. ​: justirus,не работает. Можете​​ помню что в​​ отличается от функции​​ позициях состоит в​​kNell​​почему так?​​ следующих случаях:​

3. ​ Excel не отвечает,​​Файлы Excel могут находиться​​ будет отображаться в​​ важно, чтобы эта​​«Значения»​

4. ​«Параметры»​ о содержимом ячеек​​ и т.д.) без​​ переустанавлю. BIOS новый.​

5. ​ типы значений: число​ сами попробовать я​

​ 2003 искалось нормально.​ Function X…() тем,​ хвосте.​: Господа,​Файл приложить не​• Перенаправление папки «Документы»​ зависает или зависает​ на компьютере в​ строке состояния в​ статья была вам​. Кроме того, существует​

​.​ с данными, удовлетворяющими​ учета регистра.​ Монитор, системник, сервер…Что​

Проанализируйте сведения о файле Excel и содержимого

​ и текст, посмотрите​ там файлик атачил.​ Да и в​ что функция ВСЕГДА​Тупой перебор не​помогите — совсем​ могу, т.к. в​ на расположение сервера​ счет автоматического исправления​ течение длительного времени.​ нижней части окна​ полезна. Просим вас​ возможность поиска по​В окне появляется целый​ запросу поиска, указан​Находясь во вкладке​

• ​ ещё можно?..​ их функцией ТИП().​

• ​CAHO, ненене. вопрос​ 2007 некоторые формулы​ возвращает значение, а​

• ​ подходит , (уже​ голову сломал.​ архиве весит 300kb,​• Автономные файлы​

• ​ ошибок в файлах​ Они будут обновлены​ Microsoft Excel. При​

• ​ уделить пару секунд​ примечаниям. В этом​

​ ряд дополнительных инструментов​ их адрес расположения,​«Главная»​Теперь с аватаркой?​

Установите флажок ли ваш файл создается по сторонних разработчиков

​ Надо привести к​ именно в посик​ ищет, ВПР например​ процедура нет.​ делал =) )​вот так работает:​ могу кинуть в​• Открытие файлов​ Office. Инструкции по​ до версии из​ попытке выполнения других​ и сообщить, помогла​ случае, переключатель переставляем​ для управления поиском.​ а также лист​, кликаем по кнопке​ Прикольная :)​
​ одному типу, или​ поз по столбцам,​ :-)​Что Ваша функция​ слишком медленно, так​Sub FindVal() Dim​

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

​ личку.​ из SharePoint или​ таким образом, см.:​ версии и часто​ действий Excel во​ ли она вам,​ в позицию​ По умолчанию все​ и книга, к​«Найти и выделить»​Dophin​ Текст по столбцам,​ формула будет написана​Михаил С.​Function FindVal() Call​ как позиций около​ sRng As Range​P.S. Раньше работало​ Webfolder​ Восстановление приложений Office.​ будут пересылаться из​

​ время работы, Excel​ с помощью кнопок​«Примечания»​ эти инструменты находятся​
​ которым они относятся.​, которая расположена на​: не, у меня​
​ или умножить все​ лля многих листов,​: Честно говоря, он​ FindVal1(FindVal) End Functionвернет???​

​ 1500.​ Dim sText As​ и с большим​• Удаленный рабочий​
​Если восстановление программ Office​ одного пользователя другому​ может не отображаться.​ внизу страницы. Для​.​

Восстановление программ Office

​ в состоянии, как​ Для того, чтобы​ ленте в блоке​ значение «Август» и​ значения на 1​ так как там​ (поиск функций, да​У меня на​Ну если никто​

​ String Dim dblFCRow​ кол-вом строк, видимо​ стол или Citrix​ не решило проблему,​

Проверка актуальности версии антивирусной программы и наличия конфликтов с Excel

​ пользователю. Как правило​ Подождите, пока в​ удобства также приводим​

​Ещё более точно поиск​

​ при обычном поиске,​ перейти к любому​ инструментов​ находит и заменяет​ специальной вставкой​ столбци бегать будут​ и сам мастер)в​ машине Office XP​

​ не знает…​ As Double Set​ дело в полученном​• Сетевых устройств​

​ перейдите к следующему​ пользователь наследует файла​

​ процессе завершить задачу​ ссылку на оригинал​ можно задать, нажав​ но при необходимости​ из результатов выдачи,​«Редактирование»​ нормально. Хотя тоже​Serge​ это не сойдет​ 2007 не очень​ SP-2.​

​Остается только сетовать​​ sr = Application.InputBox(prompt:=’Search​ файле, но в​• Виртуализированной среде.​ пункту в списке.​ Excel, но не​ свою работу, перед​ (на английском языке).​ на кнопку​ можно выполнить корректировку.​ достаточно просто кликнуть​. В появившемся меню​

​ область поиска -​: Сегодня день наверное​justirus​ то и нужен​предложенные варианты функции​ по поводу МС.​ Range?’, Type:=8) sText​ чем???​

Дополнительные сведения

​ Дополнительные сведения о​

​Если антивирусная программа не​ знаете, что включено​ активацией другие действия.​В этой статье рассматриваются​«Формат»​По умолчанию, функции​ по нему левой​ выбираем пункт​ формулы.​ такой: У Dophin​

​: Странно, у меня​

​ — все функции​ не работают, в​brovey​ = Application.InputBox(prompt:=’Search Text?’,​Здравия мыслям Вашим​ программного обеспечения корпорации​ обновлена, Excel может​

• ​ в файле. Следующее​Если Excel не используется​

• ​ действия по устранению​.​

​«Учитывать регистр»​ кнопкой мыши. После​«Найти…»​

​Вероятнее всего Ваши​

​ окна залипают :),​ есть результат:​ на ленте; и​ отличии от Sub.​: Извините, может я​ Type:=2) dblFCRow =​ и устремлениям, люди​ Майкрософт, которая выполняется​ работать неправильно.​ может привести к​ другим процессом, перейдите​ неполадок, которые могут​При этом открывается окно​и​

​ этого курсор перейдет​. Вместо этих действий​
​ августы некошерные какие​
​ у меня вот​vikttur​ подсказки при вводе…​
​Я говорю именно​ чего-то не понимаю​
​ sRng.Find(what:=sText, LookIn:=xlValues).Row Debug.Print​
​ добрые!​ в виртуализированной среде​Проверка актуальности антивирусной программы​ производительности или поврежденных​ к следующему пункту​ помочь устранить наиболее​ формата ячеек. Тут​«Ячейки целиком»​ на ту ячейку​ можно просто набрать​

​ то))​

​ выбор из списка​: Я Вам об​arikov299​ о собственых функциях​ (у меня работает​ sr.Address (адрес мне​ikki​ следующей статье: Политика​Для защиты от новых​ вопросы:​ в списке.​ распространенные проблемы при​ можно установить формат​

​отключены, но, если​ Excel, по записи​

​ на клавиатуре сочетание​Serge​
​ пропал…​ этом писал…​
​: файл добавил. Вот​ рабочего листа, хотя​

​ и процедура и​ нужен) Set sr​: раскрывающийся список автофильтра​ поддержки для запуска​ вирусов поставщики антивирусных​Формулы ссылки на целые​Хотя надстроек можно расширить​ получении Excel не​

​ ячеек, которые будут​
​ мы поставим галочки​ которой пользователь сделал​

​ клавиш​

​: А где у​Суть: жму Ctrl+F,​arikov299​ формула: =ИНДЕКС(‘2′!A4:Z394;ПОИСКПОЗ(Лист1!B2;’2′!A:A;0);ПОИСКПОЗ(Лист1!B3;’2’!A4:AB220;0))​ все тоже самое​ функция, предложенные by​ = Nothing End​ ограничен.​ в программном обеспечении​ программ периодически выпускают​ столбцы.​ возможности, они могут​ отвечает ошибки, Excel,​ участвовать в поиске.​ около соответствующих пунктов,​ щелчок.​Ctrl+F​ меня «Август 2009»?!​

​ пишу надо-есть, в​: justirus,странные странности.​vikttur​ …​ IgorT), ну да​ Subа так не​в старших версиях​ виртуализации оборудования сторонних​ обновления, которые можно​

support.office.com

Не работает поиск в автофильтре

​Создание ссылок на нечетного​​ иногда мешал или​
​ зависает или перестает​ Можно устанавливать ограничения​ то в таком​Если у вас довольно​.​И в файле,​
​ области поиска выбираю…​​Ну да ладно, вышел​: Всем помогающим: вопрос​Однако.​ ладно.​ работает (если в​ (кажется. до 2003-й​ программного обеспечения Майкрософт.​ скачать из Интернета.​
​ числа элементов в​​ конфликтов с Excel.​​ работать после запуска​
​ по числовому формату,​ случае, при формировании​ масштабная таблица, то​После того, как вы​ и в найти/заменить​
​ А выбора и​ из ситуации просто​ темы — почему​Function FindVal1(rngSRange As​Если я правильно​ виде функции):​
​ включительно) — 1000​Памяти​ Скачайте последние обновления,​

​ аргументах формулы массива.​​ Попробуйте запустить Excel​ его, или откройте​
​ по выравниванию, шрифту,​ результата будет учитываться​ в таком случае​ перешли по соответствующим​ просто «Август»… Причём​
​ нет! Тока «формулы»…Кто​ вместо​ не работают две​

​ Range, sText As​​ понял проблемма в​Function FindVal (byval​ позиций, в более​Файлы Excel может стать​ посетив сайт поставщика​Сотни или возможно тысяч​ без надстроек, чтобы​ книгу Excel. Такие​ границе, заливке и​
​ введенный регистр, и​ не всегда удобно​ пунктам на ленте,​ скопированный из ячейки,​ спёр? Я с​

​первого «поискпоз» вставил номер​​ функции. И никаких​ String) Dim dblFCRow​ том, что код​ rngSRange As Range,​ поздних — 10000.​ достаточно большим при​ своей антивирусной программы.​ объектов скрытых или​ увидеть, если проблема​ проблемы могут возникнуть​ защите, по одному​ точное совпадение. Если​ производить поиск по​ или нажали комбинацию​ содержащий «Август»…​​ таким не встречался​​ строки и покатило,​ других!​ As Double dblFCRow​​ оформленный процедурой работает,​​ sText as string)​от формата файла​ добавлении большого количества​Список поставщиков антивирусных программ​ 0 высота и​ не исчезнет.​ по одной или​
​ из этих параметров,​ вы введете слово​ всему листу, ведь​ «горячих клавиш», откроется​Guest​

​ ещё…​​ видемо там ошибка,​Автору: файл на​ = rngSRange.Find(What:=sText, LookIn:=xlValues).Row​

​ а оформленный функцией​​ Dim dblFCRow As​ это не зависит​

​ форматирования и фигур.​​ см. в статье​
​ ширина.​Выполните одно из указанных​
​ нескольким из перечисленных​ или комбинируя их​ с маленькой буквы,​ в поисковой выдаче​ окно​: Перезагрузил, делаю всё​Если кто знает,​ вот только какая​ форуме по Excel​ FindVal1 = CStr(dblFCRow)​
​ нет?​ Double dblFCRow =​ никак.​
​ Убедитесь в том,​ Разработчики антивирусного программного​

planetaexcel.ru

Поиск в Экселе не работает

​Лишним стили, вызванные часто​​ ниже действий.​
​ ниже причин.​ вместе.​
​ то в поисковую​
​ может оказаться огромное​«Найти и заменить»​ так же в​ будем рады!​ выясню…. спасибо всем​ предпочтительнее картинок​ End Function Sub​А не пробывали​ Sheets(1).rngSRange.Find(what:=sText, LookIn:=xlValues).Row ПоЗна​Z​ что она имеет​ обеспечения для Windows.​ используемые при копировании​Если вы используете систему​Следуйте предоставленным решений в​Если вы хотите использовать​
​ выдачу, ячейки содержащие​ количество результатов, которые​во вкладке​ том же файле​ЗЫ. Как жить-то?..​vikttur​JayBhagavan​ FindV22() Dim rrrr​ ли Вы, в​
​ = CStr(dblFCRow) End​: Дополнение к сказанному​
​ достаточно оперативной памяти​
​Проверка наличия конфликтов с​

​ и вставке книги.​​ Windows 10, выберите​ этой статье в​ формат какой-то конкретной​
​ написание этого слова​ в конкретном случае​«Найти»​

​ — меняет!!! Видимо​​vikttur​: Странный человек. Ему​
​: arikov299, смотрите на​
​ As Range Set​

​ таком случае, сам​​ Functionв чем загвоздка​
​ IKKI: есть маленькие​ для запуска приложения.​ Excel антивирусной программы:​
​Определенные имена лишним и​Пуск​ порядке. Если вы​ ячейки, то в​
​ с большой буквы,​ не нужны. Существует​
​. Она нам и​ локальный глюк…У нас​: Из закладки «Заменить»​ уже 3 раза​ предмет лишних пробелов​
​ rrrr = Worksheets(1).Range(‘F1:F100’)​ поиск оформлять процедурой,​ ?​
​ хитрости — сортировать​

​ Требования к системе​​Если антивирусная программа поддерживает​ недопустимые.​>​ попытались ранее одним​ нижней части окна​ как это было​ способ ограничить поисковое​ нужна. В поле​

​ тут кстати ещё​​ вернитесь в «Найти»​
​ написали, что в​ в искомых ячейках.​
​ MsgBox FindVal1(rrrr, ‘rr’)​ а затем вызывать​нужно чтобы функция​ данные туда/сюда перед​ для набора приложений​ интеграцию с Excel,​Если эти действия не​Все программы​ из таких способов​ нажмите на кнопку​ бы по умолчанию,​ пространство только определенным​«Найти»​ один есть. Если​
​Serge​ ПОИСКПОЗ​Vik_tor​ End Subдает положительный​

​ ее из функции?​​ возвращала адрес.​ фильтром — возьмет​ Microsoft Office перейдите​ вы можете столкнуться​
​ решило проблему, перейдите​>​ не помог, перейдите​«Использовать формат этой ячейки…»​ уже не попадут.​ диапазоном ячеек.​
​вводим слово, символы,​ файл достаточно большой​
​: А как же​просматриваемый_массив​

​: Это место проверьте​​ результат. Тоесть внутри​т.е. для примера​ВПР не предлагать.​ первые 10 тыс​ в следующих статьях​ с проблемами производительности.​ к следующему пункту​
​Система Windows​ к следующему проверяйте​.​ Кроме того, если​Выделяем область ячеек, в​ или выражения, по​
​ (от 300 кило),​ я из неё​должен состоять из​ =ИНДЕКС(‘2′!A4:Z394;ПОИСКПОЗ(Лист1!B2;’2’!​ макроса эта Function​ IgorT сделать:​
​Спасибо.​ спереди или сзади​
​ Майкрософт:​ В таком случае​ в списке.​>​ в списке.​После этого, появляется инструмент​

​ включена функция​​ которой хотим произвести​
​ которым собираемся производить​
​ шрифт на листе​
​ заменять-то буду?!​
​ одной строки или​
​A:A​ функциклирует. Прикол типа​
​Sub FindVal1(fl) Set​IgorTr​

​ из ваших, скорее​​Требования к системе для​ можно отключить интеграцию​Иногда приложением стороннего создаются​выполнить >​
​Примечание:​

​ в виде пипетки.​​«Ячейки целиком»​
​ поиск.​ поиск. Жмем на​ 6-10 (редко меньше,​vikttur​ столбца​;0);ПОИСКПОЗ(Лист1!B3;’2′!A4:A​ ИМХО.​ el = Range(‘f1:f100′).Find(What:=’rr’,​
​: А что такое​ всего уникальных, 15-ти…​ Office 2016​ Excel с антивирусной​ файлы Microsoft Excel.​введите​ При возникновении проблем с​ С помощью него​
​, то в выдачу​
​Набираем на клавиатуре комбинацию​ кнопку​ иногда больше), и​
​: Раньше не обращал​
​Как можно указать​B​IgorTr​ LookIn:=xlValues) fl =​ sRng в первом​

​В «Приемах» есть​​Требования к системе для​
​ программой. Вы также​ В этом случае​
​Excel/safe​ открытием файлов Excel​ можно выделить ту​
​ будут добавляться только​ клавиш​«Найти далее»​

​ таблицу прокручиваешь колесом​​ внимания, задавал в​
​ относительную позицию одним​
​220;0))​: to IgorTr​
​ el.Row End Sub​ примере?​ вариант удобного фильтра,​ Office 2013​ можете отключить все​ файлы могут быть​
​в окне «​
​ после обновления Windows​ ячейку, формат которой​
​ элементы, содержащие точное​Ctrl+F​, или на кнопку​
​ мыша вверх-вниз, то​ «Найти». У меня​ значением, если, например,​
​арех​Как это не​ Function FindVal() Call​А где у​ или же самому​
​требования к системе​
​ надстройки антивирусной программы,​ созданы неправильно, а​выполнить​ 7 до Windows​ вы собираетесь использовать.​ наименование. Например, если​, после чего запуститься​«Найти всё»​ при возврате не​ тоже на «Заменить»​ искомое находится в​: arikov299, Прикрепленные файлы​ удивительно, функция​ FindVal1(FindVal) End Function​ тебя возврат функции​ использовать расширенный. Как​

​ для Office 2010​​ установленные в Excel.​
​ некоторые функции могут​», а затем​
​ 10 см. статью​После того, как формат​ вы зададите поисковый​ знакомое нам уже​.​ отображается ничего кроме​ только «формулы». Может,​
​ 5 строке 2-го​ Безымянный.png (5.82 КБ)​Function FindVal() Call​
​IgorTr​ во втором?​ вариант, однако.​требования к системе​Важно:​ работать неправильно при​ нажмите кнопку​ Ошибки при открытии​
​ поиска настроен, жмем​ запрос «Николаев», то​ окно​При нажатии на кнопку​

​ границ…Т.е. данные-формулы-форматы остаются,​
​ и вора-то нет?​ столбца?​vikttur​ FindVal1(FindVal) End Functionвернет​: 2brover​IgorTr​boydak​ для Excel 2007​
​  Изменение параметров антивирусной​
​ открытии файлов в​ОК​ файлов Office после​ на кнопку​
​ ячейки, содержащие текст​
​«Найти и заменить»​«Найти далее»​ но посмотреть их​ :)​
​Sekach​: Диапазон ПОИСКПОЗ должен​ результат выполнения процедуры​Это крутой код!!!​: А что тебе​

CyberForum.ru

В Excel 2007 не работает поиск функций в мастере фунцкий

​: ikki и Z,​​Office 2010 реализована собственные​
​ программы может привести​ Microsoft Excel. В​.​ перехода с Windows 7​«OK»​ «Николаев А. Д.»,​. Дальнейшие действия точно​мы перемещаемся к​

​ можно только в​​Serge​: Здравствуйте уважаемые! У​ состоять из одной​ FindVal1. И я​Function FindVal()​ серьезно нужен​ всё Вами сказанное​ 64-разрядные версии продуктов​ к уязвимости компьютера​

​ этом случае проверки​​Если вы используете систему​ на Windows 10.​.​ в выдачу уже​ такие же, что​ первой же ячейке,​ строке формул…Как нибуть​: А если задавать​

planetaexcel.ru

почему не работает индекс и поискпоз

​ меня приключилась такая​​ строки/столбца. Ошибка в​ не вижу причин,​

​Call FindVal1(FindVal)​​sR.Address​ для меня известно.​ Office преимуществами большего​ для вирусных, мошеннических​ возможностей в новые​
​ Windows 8, выберите​Безопасный режим позволяет запустить​Бывают случаи, когда нужно​

​ добавлены не будут.​​ и при предыдущем​ где содержатся введенные​ скрин выложу. К​

​ в «Найти», то​​ беда, мне надо​ диапазоне последней функции​​ почему бы ей​​End Function​​???​​ Косвенно я сообщил,​

​ емкости обработки. Чтобы​​ или вредоносных атак.​ файлы из-за пределов​

​ команду​​ Excel без возникла​ произвести поиск не​По умолчанию, поиск производится​ способе. Единственное отличие​

​ группы символов. Сама​​ чему это я…А!​
​ при переходе на​ сравнить 2 столбца,​
​justirus​ этого не сделать.​А ты сам​kNell​ что проблема решается,​ узнать больше о​ Корпорация Майкрософт не​ приложение стороннего производителя.​выполнить​ определенные запуска программы.​ по конкретному словосочетанию,​
​ только на активном​ будет состоять в​ ячейка становится активной.​ The_Prist спасибо, интересно​

​ «Заменить» всё-равно отображается​​ в 1-ом (столбец​: Здравствуйте.​У меня тоже​ запускал это чудо?​: извиняюсь….​ но эти методы​

​ 64-разрядной версии Office,​​ рекомендует изменять параметры​ Если функции работают​

​в меню приложения​​ Можно открыть Excel​ а найти ячейки,​
​ листе Excel. Но,​

​ том, что поиск​​Поиск и выдача результатов​

​ если б я​​ только «формулы»…​ А) то что​Похоже дело в​ Office XP SP-2​Что оно у​правил текст уже​ (танцы с бубном)​ перейдите в следующих​
​ антивирусной программы. Используйте​ правильно, необходимо убедиться,​ > введите​ в безопасном режиме,​ в которых находятся​ если параметр​
​ выполняется только в​

​ производится построчно. Сначала​​ проверить успел, сработало​

​Чёт не пойму,​​ надо найти, во​ диапазонах поиска:​ и функция​
​ тебя возвратила?​ в браузере…из-за невнимательности​ приводят к «незапланированным​ статьях Майкрософт:​ это решение на​ что третьей стороне​Excel/safe​ нажав и удерживая​

​ поисковые слова в​​«Искать»​ указанном интервале ячеек.​

​ обрабатываются все ячейки​​ бы?..Вопрос, естественно риторический​ всё раньше заменялось​

​ 2-м (столбец Б)​​1. ПОИСКПОЗ(Лист1!B2;’2′!A:A;0) -​
​Function FindVal() Set​kNell​ допустил ошибки.​ ​ неудобствам». Для интереса/подтверждения​64-разрядной версии Office 2013​ свой страх и​ о проблеме.​в окне «​

​ Ctrl при запуске​​ любом порядке, даже,​вы переведете в​Как уже говорилось выше,​ первой строки. Если​​ :)​​ вроде правильно…​ область поиска, в​ здесь вы ищите​
​ el = Range(‘f1:f100′).Find(What:=’rr’,​: у меня эта​в первом куске​ могу выслать файл,​Основные сведения о​ риск.​

planetaexcel.ru

Функция ПОИСКПОЗ и ПРОСМОТР не работают как должны

​Если после проверки​​выполнить​ программы, или используя​ если их разделяют​ позицию​ при обычном поиске​ данные отвечающие условию​ЗЫ. Для Микки.​Dophin​ 3-ем (столбец С)​ во всей колонке,​ LookIn:=xlValues) FindVal =​ штука как и​ кода sRange не​ в котором имеются​ 64-разрядной версии Office​Возможно, вам придется обратиться​ вне приложения сторонних​», а затем​параметр/safe​ другие слова и​«В книге»​ в результаты выдачи​ найдены не были,​ Не хочешь ещё​: у меня тоже​ вывести результат, казалось​ найденная позиция будет​ el.Row End Functionпрекрасно​ раньше возвратила #ЗНАЧ!​ нужен…sr мы задаем​ два листа. На​Принтеры и драйверы видео​ к поставщику антивирусной​ ваша проблема не​ нажмите кнопку​

​(/ Safe excel.exe)​​ символы. Тогда данные​​, то поиск будет​​ попадают абсолютно все​ программа начинает искать​ одну оффТОПКУ на​ только формулы. Может​ бы простейшая задача,​ равна номеру строки,​ работает на рабочем​ =)))​

​ его через Inputbox​ первом список более​При запуске Excel, она​ программы, чтобы узнать,​ устранена, перейдите к​ОК​ при запуске программы​ слова нужно выделить​ производиться по всем​ ячейки, содержащие последовательный​​ во второй строке,​​ пятницу замутить? Типа​ сейчас заменяет также​ но не тут​ а т.к. в​ листе, если я​сидим дальше …..​функция возвращает значение​ 17 000 строк​ проверяет принтера по​ как настроить ее​

CyberForum.ru

Пропал выбор в области поиска Ctrl+F

​Если вы используете систему​​ При запуске Excel​ знаком «*». Теперь​

​В параметре​​ в любом виде​ пока не отыщет​ узнаем :)​

​Guest​​ не правильно воспринимают​ начинается с 4​ отдельный модуль.​: to IgorTr​от функции мне​работает​ драйверы, которые будут​

​ исключить интеграцию с​​При запуске Windows некоторыми​ Windows 7, нажмите​ в безопасном режиме,​ в поисковой выдаче​«Просматривать»​
​ не зависимо от​ удовлетворительный результат.​Serge​

​: У меня эксель​​ значения и в​ строки, результат будет​Кстати, извините за​Повторюсь еще раз​

​ надо через поиск​​, на втором более​ отображаться в книгах​ Excel или сканирование​ приложениями и службами​ кнопку​ он пропускает функции​

​ будут отображены все​​можно изменить направление​ регистра.​Поисковые символы не обязательно​: Предыдущий пост естественно​

​ указанном месте тоже​​ в столбце искомого.​

​ строки;​​ есть ‘собственые функции​ и процедура и​ столбце вытащить значение​ ПОИСК​ принтер и будет​

​ Соответственно и Call​​ (сортировать не могу)​ удалении только одного​

planetaexcel.ru

​ не менее надстройки​

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

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

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

• Базовая информация об ИНДЕКС и ПОИСКПОЗ
• Используем функции ИНДЕКС и ПОИСКПОЗ в Excel
• Преимущества ИНДЕКС и ПОИСКПОЗ перед ВПР
• ИНДЕКС и ПОИСКПОЗ – примеры формул
  • Как находить значения, которые находятся слева
  • Вычисления при помощи ИНДЕКС и ПОИСКПОЗ
  • Поиск по известным строке и столбцу
  • Поиск по нескольким критериям
  • ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА

Базовая информация об ИНДЕКС и ПОИСКПОЗ

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

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

ИНДЕКС – синтаксис и применение функции

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

INDEX(array,row_num,[column_num])
ИНДЕКС(массив;номер_строки;[номер_столбца])

Каждый аргумент имеет очень простое объяснение:

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

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

Вот простейший пример функции INDEX (ИНДЕКС):

=INDEX(A1:C10,2,3)
=ИНДЕКС(A1:C10;2;3)

Формула выполняет поиск в диапазоне A1:C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, то есть из ячейки C2.

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

ПОИСКПОЗ – синтаксис и применение функции

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

Например, если в диапазоне B1:B3 содержатся значения New-York, Paris, London, тогда следующая формула возвратит цифру 3, поскольку «London» – это третий элемент в списке.

=MATCH("London",B1:B3,0)
=ПОИСКПОЗ("London";B1:B3;0)

Функция MATCH (ПОИСКПОЗ) имеет вот такой синтаксис:

MATCH(lookup_value,lookup_array,[match_type])
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

• lookup_value (искомое_значение) – это число или текст, который Вы ищите. Аргумент может быть значением, в том числе логическим, или ссылкой на ячейку.
• lookup_array (просматриваемый_массив) – диапазон ячеек, в котором происходит поиск.
• match_type (тип_сопоставления) – этот аргумент сообщает функции ПОИСКПОЗ, хотите ли Вы найти точное или приблизительное совпадение:
  • 1 или не указан – находит максимальное значение, меньшее или равное искомому. Просматриваемый массив должен быть упорядочен по возрастанию, то есть от меньшего к большему.
  • 0 – находит первое значение, равное искомому. Для комбинации ИНДЕКС/ПОИСКПОЗ всегда нужно точное совпадение, поэтому третий аргумент функции ПОИСКПОЗ должен быть равен 0.
  • -1 – находит наименьшее значение, большее или равное искомому значению. Просматриваемый массив должен быть упорядочен по убыванию, то есть от большего к меньшему.

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

Позвольте напомнить, что относительное положение искомого значения (т.е. номер строки и/или столбца) – это как раз то, что мы должны указать для аргументов row_num (номер_строки) и/или column_num (номер_столбца) функции INDEX (ИНДЕКС). Как Вы помните, функция ИНДЕКС может возвратить значение, находящееся на пересечении заданных строки и столбца, но она не может определить, какие именно строка и столбец нас интересуют.

Как использовать ИНДЕКС и ПОИСКПОЗ в Excel

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

Ещё не совсем понятно? Представьте функции ИНДЕКС и ПОИСКПОЗ в таком виде:

=INDEX(столбец из которого извлекаем,(MATCH (искомое значение,столбец в котором ищем,0))
=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое значение;столбец в котором ищем;0))

Думаю, ещё проще будет понять на примере. Предположим, у Вас есть вот такой список столиц государств:

Давайте найдём население одной из столиц, например, Японии, используя следующую формулу:

=INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))

Теперь давайте разберем, что делает каждый элемент этой формулы:

• Функция MATCH (ПОИСКПОЗ) ищет значение «Japan» в столбце B, а конкретно – в ячейках B2:B10, и возвращает число 3, поскольку «Japan» в списке на третьем месте.
• Функция INDEX (ИНДЕКС) использует 3 для аргумента row_num (номер_строки), который указывает из какой строки нужно возвратить значение. Т.е. получается простая формула:

  =INDEX($D$2:$D$10,3)
=ИНДЕКС($D$2:$D$10;3)

  Формула говорит примерно следующее: ищи в ячейках от D2 до D10 и извлеки значение из третьей строки, то есть из ячейки D4, так как счёт начинается со второй строки.

Вот такой результат получится в Excel:

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

Стоп, стоп… почему мы не можем просто использовать функцию VLOOKUP (ВПР)? Есть ли смысл тратить время, пытаясь разобраться в лабиринтах ПОИСКПОЗ и ИНДЕКС?

=VLOOKUP("Japan",$B$2:$D$2,3)
=ВПР("Japan";$B$2:$D$2;3)

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

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

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

Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а Вы решите – остаться с ВПР или переключиться на ИНДЕКС/ПОИСКПОЗ.

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

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

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

Например, если у Вас есть таблица A1:C10, и требуется извлечь данные из столбца B, то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, вот так:

=VLOOKUP("lookup value",A1:C10,2)
=ВПР("lookup value";A1:C10;2)

Если позднее Вы вставите новый столбец между столбцами A и B, то значение аргумента придется изменить с 2 на 3, иначе формула возвратит результат из только что вставленного столбца.

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

3. Нет ограничения на размер искомого значения. Используя ВПР, помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС/ПОИСКПОЗ.

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

=VLOOKUP(A2,B5:D10,3,FALSE)
=ВПР(A2;B5:D10;3;ЛОЖЬ)

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:

=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))

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

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

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

ИНДЕКС и ПОИСКПОЗ – примеры формул

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

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

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

Функции ПОИСКПОЗ и ИНДЕКС в Excel гораздо более гибкие, и им все-равно, где находится столбец со значением, которое нужно извлечь. Для примера, снова вернёмся к таблице со столицами государств и населением. На этот раз запишем формулу ПОИСКПОЗ/ИНДЕКС, которая покажет, какое место по населению занимает столица России (Москва).

Как видно на рисунке ниже, формула отлично справляется с этой задачей:

=INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

Теперь у Вас не должно возникать проблем с пониманием, как работает эта формула:

• Во-первых, задействуем функцию MATCH (ПОИСКПОЗ), которая находит положение «Russia» в списке:

  =MATCH("Russia",$B$2:$B$10,0))
=ПОИСКПОЗ("Russia";$B$2:$B$10;0))

• Далее, задаём диапазон для функции INDEX (ИНДЕКС), из которого нужно извлечь значение. В нашем случае это A2:A10.
• Затем соединяем обе части и получаем формулу:

  =INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10;0))
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

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

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

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

1. MAX (МАКС). Формула находит максимум в столбце D и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))

Результат: Beijing

2. MIN (МИН). Формула находит минимум в столбце D и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))

Результат: Lima

3. AVERAGE (СРЗНАЧ). Формула вычисляет среднее в диапазоне D2:D10, затем находит ближайшее к нему и возвращает значение из столбца C той же строки:

=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))

Результат: Moscow

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

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

• Если указываете 1, значения в столбце поиска должны быть упорядочены по возрастанию, а формула вернёт максимальное значение, меньшее или равное среднему.
• Если указываете -1, значения в столбце поиска должны быть упорядочены по убыванию, а возвращено будет минимальное значение, большее или равное среднему.

В нашем примере значения в столбце D упорядочены по возрастанию, поэтому мы используем тип сопоставления 1. Формула ИНДЕКС/ПОИСКПОЗ возвращает «Moscow», поскольку величина населения города Москва – ближайшее меньшее к среднему значению (12 269 006).

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

Эта формула эквивалентна двумерному поиску ВПР и позволяет найти значение на пересечении определённой строки и столбца.

В этом примере формула ИНДЕКС/ПОИСКПОЗ будет очень похожа на формулы, которые мы уже обсуждали в этом уроке, с одним лишь отличием. Угадайте каким?

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

INDEX(array,row_num,[column_num])
ИНДЕКС(массив;номер_строки;[номер_столбца])

И я поздравляю тех из Вас, кто догадался!

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

=INDEX(Ваша таблица,(MATCH(значение для вертикального поиска,столбец, в котором искать,0)),(MATCH(значение для горизонтального поиска,строка в которой искать,0))
=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального поиска,столбец, в котором искать,0)),(MATCH(значение для горизонтального поиска,строка в которой искать,0))

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

А теперь давайте испытаем этот шаблон на практике. Ниже Вы видите список самых населённых стран мира. Предположим, наша задача узнать население США в 2015 году.

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

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

• ПОИСКПОЗ для столбца – мы ищем в столбце B, а точнее в диапазоне B2:B11, значение, которое указано в ячейке H2 (USA). Функция будет выглядеть так:

  =MATCH($H$2,$B$1:$B$11,0)
=ПОИСКПОЗ($H$2;$B$1:$B$11;0)

  Результатом этой формулы будет 4, поскольку «USA» – это 4-ый элемент списка в столбце B (включая заголовок).

• ПОИСКПОЗ для строки – мы ищем значение ячейки H3 (2015) в строке 1, то есть в ячейках A1:E1:

  =MATCH($H$3,$A$1:$E$1,0)
=ПОИСКПОЗ($H$3;$A$1:$E$1;0)

  Результатом этой формулы будет 5, поскольку «2015» находится в 5-ом столбце.

Теперь вставляем эти формулы в функцию ИНДЕКС и вуаля:

=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))

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

=INDEX($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11;4;5))

Эта формула возвращает значение на пересечении 4-ой строки и 5-го столбца в диапазоне A1:E11, то есть значение ячейки E4. Просто? Да!

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

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

Предположим, у нас есть список заказов, и мы хотим найти сумму по двум критериям – имя покупателя (Customer) и продукт (Product). Дело усложняется тем, что один покупатель может купить сразу несколько разных продуктов, и имена покупателей в таблице на листе Lookup table расположены в произвольном порядке.

Вот такая формула ИНДЕКС/ПОИСКПОЗ решает задачу:

{=INDEX('Lookup table'!$A$2:$C$13,MATCH(1,(A2='Lookup table'!$A$2:$A$13)*
(B2='Lookup table'!$B$2:$B$13),0),3)}
{=ИНДЕКС('Lookup table'!$A$2:$C$13;ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13)*
(B2='Lookup table'!$B$2:$B$13);0);3)}

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

MATCH(1,(A2='Lookup table'!$A$2:$A$13),0)*(B2='Lookup table'!$B$2:$B$13)
ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13);0)*(B2='Lookup table'!$B$2:$B$13)

В формуле, показанной выше, искомое значение – это 1, а массив поиска – это результат умножения. Хорошо, что же мы должны перемножить и почему? Давайте разберем все по порядку:

• Берем первое значение в столбце A (Customer) на листе Main table и сравниваем его со всеми именами покупателей в таблице на листе Lookup table (A2:A13).
• Если совпадение найдено, уравнение возвращает 1 (ИСТИНА), а если нет – 0 (ЛОЖЬ).
• Далее, мы делаем то же самое для значений столбца B (Product).
• Затем перемножаем полученные результаты (1 и 0). Только если совпадения найдены в обоих столбцах (т.е. оба критерия истинны), Вы получите 1. Если оба критерия ложны, или выполняется только один из них – Вы получите 0.

Теперь понимаете, почему мы задали 1, как искомое значение? Правильно, чтобы функция ПОИСКПОЗ возвращала позицию только, когда оба критерия выполняются.

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

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

Если всё сделано верно, Вы получите результат как на рисунке ниже:

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

Как Вы, вероятно, уже заметили (и не раз), если вводить некорректное значение, например, которого нет в просматриваемом массиве, формула ИНДЕКС/ПОИСКПОЗ сообщает об ошибке #N/A (#Н/Д) или #VALUE! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА.

Синтаксис функции ЕСЛИОШИБКА очень прост:

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

Где аргумент value (значение) – это значение, проверяемое на предмет наличия ошибки (в нашем случае – результат формулы ИНДЕКС/ПОИСКПОЗ); а аргумент value_if_error (значение_если_ошибка) – это значение, которое нужно возвратить, если формула выдаст ошибку.

Например, Вы можете вставить формулу из предыдущего примера в функцию ЕСЛИОШИБКА вот таким образом:

=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
"Совпадений не найдено. Попробуйте еще раз!") =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));
"Совпадений не найдено. Попробуйте еще раз!")

И теперь, если кто-нибудь введет ошибочное значение, формула выдаст вот такой результат:

Если Вы предпочитаете в случае ошибки оставить ячейку пустой, то можете использовать кавычки («»), как значение второго аргумента функции ЕСЛИОШИБКА. Вот так:

IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")
ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);"")

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