Выбор значения из выпадающего списка с поиском и переходом к ячейке с таким значением в excel

Выпадающий список с автоматической подстановкой данных

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

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

Далее нужно подтвердить этот диапазон путем нажатия клавиши «ОК».

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

Все, таблица есть, и она может использоваться в качестве основы для выпадающего списка, для чего надо:

Выбрать ячейку, где перечень располагается.
Открыть диалог «Проверка данных».
19
Тип данных выставляем «Список», а как значения даем имя таблицы через знак =.
20

21

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

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

Создание раскрывающегося списка при помощи функции СМЕЩ

Кроме классического метода возможно применение функции СМЕЩ

, чтобы генерировать выпадающие меню.

Откроем лист.

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

Выбрать интересующую ячейку, где надо разместить будущий список.
Открыть последовательно вкладку «Данные» и окно «Проверка данных».
13
Задаем «Список». Делается это аналогично предыдущим примерам. Наконец, используется такая формула: =СМЕЩ(A$2$;0;0;5). Мы ее вводим там, где задаются ячейки, которые будут использоваться в качестве аргумента.

Потом программой создастся меню с перечнем фруктов.

Синтаксис этой такой:

=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;;)

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

Поиск или замена текста и чисел на листе

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

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

Для удобства также приводим
ссылку на оригинал (на английском языке)
.

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

На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить.
Выполните одно из указанных ниже действий.
- Чтобы найти текст или числа, выберите пункт Найти.
- Чтобы найти и заменить текст или числа, выберите пункт Заменить.
В поле Найти введите текст или числа, которые нужно искать, или щелкните стрелку в поле Найти и нажмите кнопку последнего поиска в списке.

В условиях поиска можно использовать подстановочные знаки, например вопросительный знак (?) и звездочку (*).
- Звездочка используется для поиска любой строки знаков. Например, если ввести г*д, то будут найдены слова «год» и «город».
- Вопросительный знак заменяет один любой знак. Например, если ввести г?д, то будут найдены слова «гад», «гид» и «год».
  
  Совет: Звездочки, знак вопроса и символы тильда (~) можно найти в данных листа перед их с тильда в поле Найти. Например, чтобы найти данные, которые содержат «?», вы введите ~? как критерии поиска.
Нажмите кнопку Параметры, чтобы определить дополнительные условия поиска при необходимости:
- Для поиска данных на листе или во всей книге выберите в поле Искать вариант на листе или в книге.
- Для поиска данных в строках или столбцах выберите в поле Просматривать вариант по строкам или по столбцам.
- Для поиска данных с конкретными свойствами выберите в поле Область поиска вариант формулы, значения или примечания.
  
  Примечание: Формулы, значения и примечания доступны только на вкладке Поиск; на вкладке » Заменить » доступны только формулы.
- Для поиска данных с учетом регистра установите флажок Учитывать регистр.
- Для поиска ячеек, содержащих только символы, введенные в поле Найти, установите флажок Ячейка целиком.
Если вы хотите найти текст или числа с определенным форматированием, нажмите кнопку Формат и выберите нужные параметры в диалоговом окне Найти формат.

Совет: Чтобы найти ячейки, точно соответствующие определенному формату, можно удалить все условия в поле Найти, а затем выбрать ячейку с нужным форматированием в качестве примера. Щелкните стрелку рядом с кнопкой Формат, выберите пункт Выбрать формат из ячейки, а затем щелкните ячейку с форматированием, которое требуется найти.
Выполните одно из указанных ниже действий.
- Чтобы найти текст или числа, нажмите кнопку Найти все или Найти далее.
  
  Совет: При нажатии кнопки Найти все, каждого экземпляра условия, которые вы ищете указываются и щелкнув нужное вхождение в списке сделает ячейки active. Можно сортировать результаты Найти все, щелкнув заголовок столбца.
- Чтобы заменить текст или числа, введите знаки для замены в поле Заменить на (или оставьте это поле пустым, чтобы ничем не заменять знаки), а затем нажмите кнопку Найти далее или Найти все.
  
  Примечание: Если поле Заменить на недоступно, откройте вкладку Заменить.
  
  При необходимости поиск можно отменить, нажав клавишу ESC.
Чтобы заменить одно или все совпадения с введенным текстом, нажмите кнопку Заменить или Заменить все.

Советы

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

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

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

Предназначение и синтаксис метода Range.Find

Метод Find объекта Range предназначен для поиска ячейки и сведений о ней в заданном диапазоне по ее значению, формуле и примечанию. Чаще всего этот метод используется для поиска в таблице ячейки по слову, части слова или фразе, входящей в ее значение.

Синтаксис метода Range.Find

1	Expression.Find(What,After,LookIn,LookAt,SearchOrder,SearchDirection,MatchCase,MatchByte,SearchFormat)

Expression – это переменная или выражение, возвращающее объект Range, в котором будет осуществляться поиск.

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

Метод Range.Find возвращает объект Range, представляющий из себя первую ячейку, в которой найдена поисковая фраза (параметр What). Если совпадение не найдено, возвращается значение Nothing.

Если необходимо найти следующие ячейки, содержащие поисковую фразу, используется метод Range.FindNext.

Параметры метода Range.Find

Наименование	Описание
Обязательный параметр
What	Данные для поиска, которые могут быть представлены строкой или другим типом данных Excel. Тип данных параметра — Variant.
Необязательные параметры
After	Ячейка, после которой следует начать поиск.
LookIn	Уточняет область поиска. Список констант xlFindLookIn: xlValues (-4163) – значения; xlComments (-4144) – примечания; xlNotes (-4144) – примечания; xlFormulas (-4123) – формулы.
LookAt	Поиск частичного или полного совпадения. Список констант xlLookAt: xlWhole (1) – полное совпадение; xlPart (2) – частичное совпадение.
SearchOrder	Определяет способ поиска. Список констант xlSearchOrder: xlByRows (1) – поиск по строкам; xlByColumns (2) – поиск по столбцам.
SearchDirection	Определяет направление поиска. Список констант xlSearchDirection: xlNext (1) – поиск вперед; xlPrevious (2) – поиск назад.
MatchCase	Определяет учет регистра: False (0) – поиск без учета регистра (по умолчанию); True (1) – поиск с учетом регистра.
MatchByte	Условия поиска при использовании двухбайтовых кодировок: False (0) – двухбайтовый символ может соответствовать однобайтовому символу; True (1) – двухбайтовый символ должен соответствовать только двухбайтовому символу.
SearchFormat	Формат поиска – используется вместе со свойством Application.FindFormat.

* Примечания имеют две константы с одним значением. Проверяется очень просто: и .

В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.

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

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

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

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

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

INDEX(array,row_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,)ПОИСКПОЗ(искомое_значение;просматриваемый_массив;)

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

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

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

Зачем нужен выпадающий список?

Часто случается так, что в какой-то из колонок вашей таблицы нужно вводить одинаковые повторяющиеся значения. К примеру, фамилии сотрудников, названия товаров или делать выбор в ячейке Excel вида «да – нет». Что может случиться? Конечно, в первую очередь будут ошибки при вводе. Человеческий фактор ведь никто не отменял. Чем нам сие грозит? К примеру, когда мы решим подсчитать, сколько заказов выполнил каждый из менеджеров, то окажется, что фамилий больше, чем сотрудников. Далее придётся искать ошибки, исправлять их и вновь повторять расчет.

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

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

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

Макрос выпадающего списка с внесением нескольких значений в одну ячейку:

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range(«B2:B5»)) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application.Undo oldval = Target If Len(oldval) 0 And oldval newVal Then Target = Target & «//» & newVal Else Target = newVal End If If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If End Sub

В строке If Not Intersect(Target, Range(«B2:B5»)) Is Nothing And Target.Cells.Count = 1 Then указывается диапазон действия макроса. В строке Target = Target & «//» & newVal указывается разделитель «//». Его можно заменить на любой знак препинания, текст или поставить пробел.

Выпадающий список как валидация (вариант 1):

Будем использовать поле с валидацией. Шаги такие:

Выделяем одну ячейку на листе, куда следует поместить выпадающий список с элементами.
Переходим на вкладку «DATA» и нажимаем кнопку «Data Validation» -> «Data Validation…».
Будем работать с первой вкладкой «Settings», предназначение остальных вкладок можно узнать в статье.
Из списка «Allow» выбираем значение «List», в появившемся поле «Source» указываем диапазон всех возможных вариантов (список значений должен размещаться в 1-ом столбце или 1-ой строке), проще всего это сделать нажав пиктограму и обычным способом на листе выделив смежные ячейки.
Убедитесь, что переключатель «In-cell dropdown» активен, нажимаем «OK».

Как это выглядит:

Особенности такого варианта:

Выпадающий список вписывается по размерах в одну ячейку.
Если ячейки не выделена то визуального различия с другими обычными ячейками нет.
Поведение списка при попытке ввода несуществующего значения можно задать самостоятельно на вкладке «Error Alert» меню «Data Validation…». Можно не показывать ошибок, можно предупреждать про неверные значения или совсем запретить их ввод.
Формат файла не меняется и документ можно сохранять с расширением .xlsx.
Не используется связная ячейка для результата, в текущую ячейку записывается именно то, что выбрали из списка.

Как сделать случайную выборку в таблице Excel без повторов

Хотите сделать случайную выборку и являетесь продвинутым пользователем Excel? Можно создать простую функцию на VBA, которая будет выдавать заданное количество случайных чисел из нужного интервала. Откроем редактор Visual Basic (ALT+F11 или в старых версиях Excel через меню Сервис – Макрос – Редактор Visual Basic), вставим новый модуль через меню Insert – Module и скопируем туда текст вот такой функции:

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

Bottom – нижняя граница интервала случайных чисел выборки Excel.
Top – верхняя граница интервала случайных чисел выборки.
Amount – количество случайных чисел, которое мы хотим отобрать из интервала для нашей выборки.

Т.е., например, чтобы отобрать для выборки 5 случайных чисел от 10 до 100, нужно будет ввести =Lotto(10;100;5)

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

LottoCtrl+Shift+Enter

Ну, а дальше останется при помощи уже знакомой функции ВПР <font>(VLOOKUP)</font> вытащить имена из списка, соответствующие случайным номерам.

Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции

Выпадающий список с поиском

На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
Жмем «Свойства» – открывается перечень настроек.
Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

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

Макрос поиска ячейки с выпадающим списком

Допустим у нас имеется таблица Excel сформированная в результате экспорта журнала фактур из истории взаиморасчетов с клиентами фирмы, как показано ниже на рисунке:

Нам необходимо найти все выпадающие списки или определить каким ячейкам присвоена проверка вводимых данных, создана инструментом: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

В программе Excel по умолчанию есть встроенный инструмент для поиска ячеек с проверкой правил вводимых значений. Чтобы его использовать следует выбрать: ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек». В появившемся диалоговом окне следует отметить опцию «проверка данных» и нажать на кнопку ОК.

Откройте редактор макросов Visual Basic (ALT+F11) и создайте новый модуль выбрав в редакторе инструмент: «Insert»-«Module». В созданный модуль введите VBA код макроса:

Если нужно выделить все ячейки в таблице, которые содержат проверку вводимых значений включенной инструментом «Проверка данных», тогда выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«ProvDan»-«Выполнить».

В результате выделились 14 ячеек в столбце G, для которых включена проверка данных в стиле выпадающего списка:

В данном коде мы сначала выделяем все ячейки на текущем листе с помощью инструкции:

После, определяем диапазон ячеек на листе, который использует исходная таблица и с которыми будет работать наш макрос. Чтобы определить диапазон таблицы на рабочем листе Excel, мы могли бы использовать свойство UsedRange при создании экземпляра объекта Range в переменной diapaz1. Данное свойство охватывает только непустые ячейки, а это может быть даже несмежный диапазон. Но таблица может содержать пустые ячейки для, которых присвоена проверка ввода значений. Чтобы наш макрос не игнорировал пустые ячейки внутри таблицы мы определяем смежный (неразрывный) диапазон, который начинается с ячейки A1 и заканчивается последней используемой ячейкой на рабочем листе Excel.

Set diapaz1 = Application.Range(ActiveSheet.Range(«A1»), ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))

Последняя ячейка находиться наиболее отдаленно от ячейки A1 (в данном примере – это G15) и была использована на листе (это обязательное условие). При чем использована в прямом смысле, она может даже не содержать значения, но иметь измененный числовой формат, другой цвет фона, другие границы, объединение и т.п. Чтобы найти последнюю используемую ячейку на листе стандартными средствами Excel, выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек».

В появившемся окне следует выбрать опцию «последнюю ячейку». А после просто нажать ОК. Курсор клавиатуры сразу переместиться на последнюю используемую ячейку на рабочем листе Excel.

Можно даже при создании экземпляра объекта Range в переменной diapaz1 использовать диапазон целого листа. Для этого просто замените выше описанную инструкцию на:

Set diapaz1 = Selection

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

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

On Error Resume Next

Но после конца цикла обработка ошибок снова включается.

On Error GoTo 0

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

If IsError(diapaz1(i).Validation.Type) Then

Тогда она присоединяется к диапазону ячеек, находящихся в переменной diapaz2.

Set diapaz2 = Application.Union(diapaz2, diapaz1(i))

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

MsgBox «Найдено: » & diapaz2.Count & » ячеек!»

Как нам это может пригодиться?

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

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

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

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

Как найти значение в массиве

Для понимания темы алгоритм выполнения поставленной задачи необходимо рассмотреть на конкретном примере. Составим в Excel таблицу заказов за один день, в которой будут столбцы: «№ заказа», «Клиент», «Товар», «Количество», «Цена за ед.», «Сумма». Необходимо найти значение в массиве, т.е. составить индивидуальную карточку заказа клиента, чтобы можно было в сжатом виде получить информацию из ячеек исходной таблицы.

Внешний вид составленной таблички

Для этого надо выполнить ряд действий по алгоритму:

Составить карточку заказа клиента.

Карточка заказа клиента

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

Необходимые действия в окошке «Проверка вводимых значений». Здесь выбираем вариант «Список» и указываемы диапазон всех клиентов

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

Перечень клиентов, который появится в первой строке карточки после выполнения предыдущих манипуляций

В строке «№ заказа» прописать функцию «=ИНДЕКС(», после чего кликнуть по значку «fx» рядом со строкой формул Эксель.
В открывшемся меню Мастера функций из списка выбрать форму массива для функции «ИНДЕКС» и нажать на «ОК».

Выбор формы массива для функции «ИНДЕКС»

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

Внешний вид окошка «Аргументы функции»

Сначала надо кликнуть по пиктограмме напротив поля «Массив» и выделить целиком исходную табличку вместе с шапкой.

Заполнение строчки «Массив». Здесь надо нажать по пиктограмме в конце поля и выделить изначальную табличку

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

Заполнение поля Номер строки в меню аргументов функции. Здесь применяется оператор «ПОИСКПОЗ»

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

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

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

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

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

Растягивание формулы на все строки таблицы. Полностью заполненный массив. Данные будут сменяться при выборе другого клиента

Зависимые элементы

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

В этом случае дайте имя каждому столбцу. Выделите без первой ячейки (названия) и нажмите правую кнопку мыши. Выберите «Присвоить имя».

Это будет название города.

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

Поэтому переименуем эти города, поставив нижнее подчеркивание.

Первый элемент в ячейке A9 создаем обычным образом.

А во втором пропишем формулу:

=ДВССЫЛ(A9)

Сначала Вы увидите сообщение об ошибке. Соглашайтесь.

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

Выделение ячейки при поиске (Ctrl+F) другим цветом

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

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

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

Выделение строк ListView другим цветомЗдравствуйте, уважаемые! Подскажите как выделить четную (нечетную) строку ListView другим цветом.

Вложения

Выделение ячейки при поиске.xls (35.5 Кб, 670 просмотров)

Выложенный файл содержит в себе макросы. MacrosON активизируется по Ctrl+q, MacrossOFF по Ctrl+w

У меня книга рабочая НЕ закрывается и всё работает исправно. Нажми Ctrl+q и попробуй повыделять всякие ячейки — они будут заливаться красным. Ну и при поиске (Ctrl+F) соответственно тоже красный будет.

Вложения

Выделение ячейки при поиске-УСЛОВНОЕ.xls (32.5 Кб, 373 просмотров)

Сообщение от Все имена заняты
Сообщение от _Exhaust_

Проблема решена тем что нужно было запустить макрос: Alt+F8, выбрать MacrosOn, нажать Выполнить После этого выделенная ячейка всегда будет красной

Спасибо пользователю Все имена заняты за оригинальное решение, буду с ним работать

Также возникла идея, может кто знает как ее реализовать Сделать для Excel макрос который будет делать поиск похожим на поиск по странице в браузере (как в cGoogle Chrome к примеру):

Выделяем диапазон поиска, вызываем хоткеем поле поиска (свое, для макроса), по мере ввода все подходящие поля или части введенного текста окрашиваются, рядом с полем счетчик количества найденных результатов и 2 кнопки — «дальше» (работает при нажатии Enter в поле поиска) и «назад» (работает при нажатии Shift+Enter в поле поиска).

Это будет крутая тема! У кого какие варианты?

Выделение областей другим цветом в матрицеПомогите напишите код Дана матрица: 00000 00000 00000 00000 00000 Нужно чтобы выделил.

Выделение строки в LisBox другим цветомВсем привет! По умолчанию в ListBox строка выделяется синим цветом. Как изменить этот цвет на какой.

Подсветка ячейки другим цветомДоброго времени суток У меня такой вопрос: как сделать так, что бы в ячейке А1 (есть цифра, скажем.

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

Поиск ТЕКСТовых значений в Excel. Бесплатные примеры и статьи.

Выделение ячеек c ТЕКСТом с применением Условного форматирования в MS EXCEL

Выделяем ячейки, содержащие искомый текст. Рассмотрим разные варианты: выделение ячеек, содержащих значения в точности совпадающих с искомым текстом; выделение ячеек, которые содержат искомый текст в начале, в конце или середине строки. Также научимся выделять ячейку, в случае если ее значение совпадает с одним из нескольких заданных значений.

Есть ли слово в списке MS EXCEL

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

Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск

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

Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца

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

Поиск позиции ТЕКСТа с учетом РЕгиСТра в MS EXCEL и выводом значения из соседнего столбца

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

Поиск в MS EXCEL текстового значения первого или последнего по алфавиту

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

Разнесение в MS EXCEL ТЕКСТовых значений и ЧИСЕЛ по разным спискам

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

Поиск в MS EXCEL третьего (n-го) повтора в списках с ТЕКСТом

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

О классификации задач Поиска в EXCEL

В этой статье изложены попытки классификации задач по поиску значений в MS EXCEL. Статья может быть интересна для продвинутых пользователей EXCEL.

Поиск нескольких значений с максимальной длиной строки в MS EXCEL

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