Исправление ошибки #Н/Д в функциях ИНДЕКС и ПОИСКПОЗ. Excel индекс

Технические характеристики и ограничения Microsoft Excel

Количество открытых книг	Ограничено объемом доступной оперативной памяти и ресурсами системы
Общее количество строк и столбцов на листе	1 048 576 строк и 16 384 столбца
Ширина столбца	255 знаков
Высота строки	409 пунктов
Разрывы страниц	1 026 горизонтальных и вертикальных
Общее количество знаков в ячейке	32 767 знаков
Число знаков в верхнем и нижнем колонтитуле	255
Максимальное число переводов строки в ячейке	253
Количество листов в книге	Ограничено объемом доступной оперативной памяти (по умолчанию 1 лист)
Количество цветов в книге	16 миллионов цветов (32-битовый спектр с полным доступом к 24-битовому спектру)
Именованные представления в книге	Ограничено объемом доступной оперативной памяти
Количество уникальных форматов/стилей ячеек	64 000
Количество стилей заливки	256
Количество стилей и толщин линий	256
Количество уникальных шрифтов	1 024 доступных глобальных шрифта; 512 шрифтов в каждой книге
Количество численных форматов в книге	От 200 до 250, в зависимости от установленной языковой версии Excel
Количество имен в книге	Ограничено объемом доступной оперативной памяти
Количество окон в книге	Ограничено объемом доступной оперативной памяти
Гиперссылки на листе	66 530 гиперссылок
Количество областей в окне	16
Количество связанных листов	Ограничено объемом доступной оперативной памяти
Сценарии	Ограничено объемом доступной оперативной памяти; в итоговый отчет включается 251 сценарий, начиная с первого
Количество изменяемых ячеек в сценарии	32
Количество вычисляемых ячеек в надстройке «Поиск решения»	200
Количество пользовательских функций	Ограничено объемом доступной оперативной памяти
Пределы масштабирования	от 10 до 400 процентов
Количество отчетов	Ограничено объемом доступной оперативной памяти
Количество ссылок для сортировки	64 для однократной сортировки; не ограничено для последовательных сортировок
Количество уровней отмены	3000
Количество полей в форме данных	32
Количество параметров в книге	255 параметров в одной книге
Количество элементов, которые отображаются в раскрывающихся списках фильтров	10 000
Количество независимых ячеек, которые можно выделить	2 147 483 648 ячеек
Максимальные пределы объема памяти и размера файла для книг с моделями данных	В 32-разрядной среде выделяется 2 гигабайта (ГБ) виртуального адресного пространства, которое совместно используется приложением Excel, книгой и надстройками, работающими в рамках одного процесса. Доля адресного пространства, выделяемого для модели данных, может достигать 500–700 мегабайт (МБ), но может быть и меньше, если загружаются другие модели данных и надстройки. 64-разрядная среда не накладывает жестких ограничений на размер файлов. Размер книги ограничен только объемом доступной памяти и ресурсами системы. В Excel 2016 предлагается функция обработки больших адресов (Large Address Aware), которая позволяет 32-разрядной версии Excel 2016 использовать в два раза больше памяти при работе в 64-разрядной операционной системе Windows. Дополнительные сведения см. в статье Изменение функции обработки больших адресов для Excel. Примечание: Добавление таблиц в модели данных увеличивает размер файла. Если вы не планируете создавать для модели данных сложные связи с использованием большого количества источников и типов данных в книге, во время импорта или создания таблиц, сводных таблиц или подключений к данным снимите флажок Добавить эти данные в модель данных. Подробнее см. в статье Спецификации и ограничения модели данных.
Ядер процессоров	64

support.office.com

Отображение чисел в виде почтовых индексов

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

В Excel используются два особых формата для почтовых индексов, «Почтовый индекс» и «Индекс + 4». Если эти форматы не подходят, можно создать собственный формат почтового индекса.

Применение стандартного формата почтового индекса к числам

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

Для отмены выделения ячеек щелкните любую ячейку в таблице.
На вкладке Главная нажмите кнопку Вызова диалогового окна рядом с полем число.
В поле Числовые форматы выберите пункт Дополнительный.
В списке Тип выберите пункт Почтовый индекс или Индекс + 4.

Примечания:

Эти коды будут доступны в списке Тип только в том случае, если в списке Язык (местоположение) выбран пункт русский. Для разных языков в список Тип включаются различные дополнительные коды (в частности, могут не включаться вообще).
Если вы хотите импортировать адреса внешний файл, может Обратите внимание, что начальных нулей в начале почтовых индексов больше не будут отображаться. Это происходит потому Excel интерпретирует столбец почтовый индекс значения в виде чисел, при действительно ли нужно их хранить и отформатировано как текст. Чтобы решить эту проблему, на шаге 3 мастера импорта текста, щелкните столбец, содержащий почтовые индексы и выберите текстовый в группе формат данных столбца. Нажмите кнопку Готово, чтобы завершить импорт данных.

Создание пользовательского формата почтового индекса

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

Для отмены выделения ячеек щелкните любую ячейку в таблице.
На вкладке Главная нажмите кнопку Вызова диалогового окна рядом с полем число.
В списке Числовые форматы выберите пункт (все форматы).
В списке Тип выберите числовой формат, который нужно настроить.

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

Чтобы настроить предопределенные индекс + 4 формат, выберите 00000 0000 в списке тип. При выборе встроенном числовом формате в списке Тип файла Excel создает копию этого числовой формат, вы можете настроить. Исходный числовой формат в списке тип нельзя изменены или удалены, поэтому не нужно беспокоиться о перезапись исходного числовой формат.
В поле Тип внесите необходимые изменения в выбранный числовой формат. Например если диапазон ячеек содержит пятизначный и 9 значный почтовые индексы (индексами), можно применить пользовательский формат, чтобы правильно отображает оба типа ПОЧТОВЫЕ индексы. В поле Тип введите [< = 99999] 00000; 00000 0000

Дополнительные сведения о том, как создать пользовательские числовые форматы Просмотр, Создание или удаление пользовательского числового формата.

Включение начальных знаков в почтовые индексы

Можно отформатировать ячейку или диапазон ячеек для отображения начальных знаков, чтобы почтовый индекс предшествует недостаточно символов для заполнения ширины ячейки. Например, можно использовать нули или тире для отображения почтовый индекс следующим образом: 0000000 98052 или ---98052.

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

Для отмены выделения ячеек щелкните любую ячейку в таблице.
На вкладке Главная нажмите кнопку Вызова диалогового окна рядом с полем число.
В списке Числовые форматы выберите пункт (все форматы).
В списке Тип введите *0 и далее требуемый формат почтового индекса.

Например, для 5-значного индекса введите *0#####

См. также

Создание и удаление пользовательского числового формата

support.office.com

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Начнем с количества. В любой ячейке под этим столбцом пишем =ИНДЕКС.

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

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

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

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

exceltable.com

Исправление ошибки #Н/Д в функциях ИНДЕКС и ПОИСКПОЗ

В разделе описаны наиболее распространенные причины «Ошибка # н/д» должна отображаться, являющееся результатом функции индексили ПОИСКПОЗ .

Примечание: Если необходимо ИНДЕКСУ или ПОИСКПОЗ для возврата осмысленные значения вместо # н/д, используйте функцию IFERROR и затем вложить функций индекс и ПОИСКПОЗ в эту функцию. Только заменив собственное значение # н/д идентифицирует ошибку, но не решило. Итак, очень важно, прежде чем использовать ЕСЛИОШИБКА, убедитесь, что формула работает неправильно данное.

Проблема: Нет соответствий

Если функция ПОИСКПОЗ не находит искомое значение в массиве подстановки, возвращается ошибка # н/д.

Если вы считаете, что данные не содержится данных в электронной таблице, но не удалось найти его СООТВЕТСТВИЕ , возможно, так как:

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

Решение: чтобы удалить непредвиденные символы или скрытых пробелов, используйте функцию ПЕЧСИМВ или СЖПРОБЕЛЫ соответственно. Кроме того проверьте, если ячейки, отформатированные как типы данных.

Вы использовали формулу массива, но не нажали клавиши CTRL+SHIFT+ВВОД

При использовании массива в индекс, ПОИСКПОЗили сочетание этих двух функций необходим на клавиатуре нажмите клавиши Ctrl-клавиши Shift + Ввод. Excel автоматически будет размещать формулы в фигурные скобки {}. Если вы пытаетесь ввести их, Excel отобразит формулу как текст.

Проблема: Несоответствие типа сопоставления и порядка сортировки данных

При использовании СООТВЕТСТВИЕмежду значениями в качестве аргумента тип_сопоставления и порядок сортировки значений в массиве подстановки должно быть согласованность. Если синтаксис отличается от перечисленных ниже правил, появляется ошибка # н/д.

Если тип_сопоставления равен 1 или не указан, значения в просматриваемый_массив должен быть в порядке возрастания. Например, -2, -1, 0, 1, 2..., A, B и C..., ЛОЖЬ, ИСТИНА лишь некоторые из них.
Еслитип_сопоставления -1, значения в просматриваемый_массив должен быть в порядке убывания.

В приведенном ниже примере — функция ПОИСКПОЗ

=MATCH(40,B2:B10,-1)

функция ПОИСКПОЗ в Excel

Аргумент тип_сопоставления в синтаксисе присваивается значение -1, это означает, что порядок значений в B2: B10 должен быть в порядке убывания формулы для работы. Но значения в порядке возрастания, и, которая приводит к ошибке # н/д.

Решение: Измените аргумента тип_сопоставления 1 или сортировка по убыванию формат таблицы. Затем повторите попытку.

У вас есть вопрос об определенной функции?

Задать вопрос на форуме сообщества, посвященном Excel

Помогите нам улучшить Excel

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

См. также

Как исправить ошибку # н/д

Способы использования функций индекс и ПОИСКПОЗ с несколькими условиями в Excel

Функция ИНДЕКС

Функция ПОИСКПОЗ

Общие сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Обнаружение ошибок в формулах с помощью функции проверки ошибок

Все функции Excel (по алфавиту)

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

support.office.com

Добавление списка или поля со списком на лист в Excel

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

Образец списка

Добавление списка на лист

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

Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.
В разделе Элементы управления формы выберите элемент управления Список (элемент управления формы).
Щелкните ячейку, в которой нужно создать список.
Нажмите кнопку Свойства и на вкладке Элемент управления задайте необходимые свойства:
- В поле Формировать список по диапазону введите диапазон ячеек, содержащий список значений.
  
  Примечание: Если нужно отобразить в списке больше элементов, можно изменить размер шрифта для текста.
- В поле Связь с ячейкой введите ссылку на ячейку.
  
  Совет: Выбираемая ячейка содержит число, связанное с элементом, выбранным в списке. Его можно использовать в формуле для получения фактического элемента из входного диапазона.
- В группе Возможен выбор установите переключатель одинарного значения и нажмите кнопку ОК.
  
  Примечание: Если вы хотите выбрать параметр набора значений или списка значений, подумайте о том, чтобы использовать элемент ActiveX "Список".

Добавление поля со списком на лист

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

Можно добавить поле со списком одного из двух типов: элемент управления формы или элемент ActiveX. Если необходимо создать поле со списком, в котором пользователь сможет изменять текст в текстовом поле, рассмотрите возможность использования элемента ActiveX "Поле со списком". Элемент ActiveX "Поле со списком" более универсален: вы можете изменить свойства шрифта, чтобы текст было легче читать на листе с измененным масштабом. Кроме того, такое поле со списком можно программно разместить в ячейках, содержащих список проверки данных.

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

Примечание: Можно также создать список на другом листе той же книги.
На вкладке Разработчик нажмите кнопку Вставить.

Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.
Выберите тип поля со списком, которое нужно добавить:
- в разделе Элементы управления формы выберите элемент управления Поле со списком (элемент управления формы);
  
  ИЛИ:
- в разделе Элементы ActiveX выберите элемент управления Поле со списком (элемент ActiveX).
Щелкните ячейку, в которую нужно добавить поле со списком, и нарисуйте его с помощью перетаскивания.

Советы:

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

Форматирование элемента управления формы "Поле со списком"

Щелкните правой кнопкой мыши поле со списком и выберите команду Формат объекта.
Откройте вкладку Элемент управления и настройте следующие параметры.
- Формировать список по диапазону: введите диапазон ячеек, содержащий список элементов.
- Связь с ячейкой: поле со списком можно связать с ячейкой, где отображается номер элемента при его выборе из списка. Введите номер ячейки, где должен отображаться номер элемента.
  
  Например, в ячейке C1 отображается значение 3, если выбрать пункт Фруктовое мороженое, так как это третий элемент в списке.
  
  Совет: Чтобы вместо номера отображать сам элемент, можно воспользоваться функцией ИНДЕКС. В нашем примере поле со списком связано с ячейкой B1, а диапазон ячеек для списка — A1:A2. Если в ячейку C1 ввести формулу =ИНДЕКС(A1:A5;B1), то при выборе третьего пункта в ячейке C1 появится текст "Фруктовое мороженое".
- Количество строк списка: количество строк, которые должны отображаться, если щелкнуть стрелку вниз. Например, если список содержит 10 элементов и вы не хотите использовать прокрутку, вместо значения по умолчанию введите 10. Если ввести число, которое меньше количества элементов в списке, появится полоса прокрутки.
Нажмите кнопку ОК.

Форматирование элемента ActiveX "Поле со списком"

На вкладке Разработчик нажмите кнопку Режим конструктора.

Щелкните правой кнопкой мыши поле со списком и выберите пункт Свойства. Откройте вкладку Alphabetic (По алфавиту) и измените нужные свойства.

Вот как можно настроить свойства поля со списком на этом рисунке:

Пример поля со списком.

Параметры свойств для элемента ActiveX "поле со списком".

Настраиваемое свойство	Действие
Цвет заливки	Щелкните свойство BackColor (Цвет фона), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.
Тип, начертание или размер шрифта	Щелкните свойство Font (Шрифт), нажмите кнопку ... и выберите тип, размер или начертание шрифта.
Цвет шрифта	Щелкните свойство ForeColor (Цвет текста), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.
Связь с ячейкой для отображения значения, выбранного в списке	Щелкните свойство LinkedCell (Связанная ячейка).
Связывание поля со списком и списка элементов	Щелкните поле рядом со свойством ListFillRange (Диапазон элементов списка) и укажите диапазон ячеек для списка.
Изменение количества отображаемых элементов списка	Щелкните поле ListRows и введите число элементов.

Закройте область Properties (Свойства) и нажмите кнопку Режим конструктора.
Завершив форматирование, можно щелкнуть правой кнопкой мыши столбец, который содержит список, и выбрать команду Скрыть.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Общие сведения о формах, элементы управления формы и элементы управления ActiveX на листе

" (элементы управления формы)

support.office.com