Урок 50. Поиск по нескольким полям


Содержание материала:

Урок 25
Сортировка и поиск данных в электронных таблицах

§ 5.2. Сортировка и поиск данных в электронных таблицах

Содержание урока

5.2. Сортировка и поиск данных в электронных таблицах

5.2. Сортировка и поиск данных в электронных таблицах

Сортировка данных в столбцах электронной таблицы. Электронные таблицы позволяют сортировать данные в отдельных столбцах. Если в столбец электронной таблицы ввести данные одного типа (числа, текст, даты или время), можно произвести их сортировку по возрастанию или убыванию. Ниже приведена табл.5.2, в которой сортировка данных в столбцах проведена следующим образом:

в столбце А — сортировка чисел по убыванию;
в столбце В — сортировка текста по убыванию;
в столбце С — сортировка дат по возрастанию;
в столбце D — сортировка времени по возрастанию.

Таблица 5.2. Сортировка чисел, текста, дат и времени в столбцах

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

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

При сортировке по убыванию данные выстраиваются в порядке, обратном указанному выше.

Сортировка записей в электронных таблицах. Электронные таблицы могут содержать сотни и тысячи записей (строк). Часто бывает необходимо их упорядочить, т. е. расположить в определенной последовательности. Упорядочение записей называется сортировкой.

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

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

Сортировка данных в электронных таблицах — это упорядочение записей (строк) по значениям одного из полей.

Например, после сортировки по возрастанию по текстовому полю Фамилия база данных «Записная книжка» примет следующий вид (табл. 5.3).

Таблица 5.3. Результат сортировки базы данных «Записная книжка»

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

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

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

Условия поиска записей создаются с использованием операторов сравнения (=, >, (больше);
= (больше или равно);
(не равно).

Для текстовых данных возможны следующие операции сравнения:

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

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

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

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

Например, если в базе данных «Записная книжка» ввести простой фильтр для поля Фамилия, состоящий из условия равно Иванов, то будет найдена и оставлена на экране одна запись базы данных (табл. 5.4).

Таблица 5.4. Результат применения фильтра для базы данных «Записная книжка»

Контрольные вопросы

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

2. В чем состоит различие между простыми и составными фильтрами?

Как реализовать sql поиск по нескольким полям с учётом того, что запрос может содержать сразу два поля?

Как реализовать sql поиск по нескольким полям?Пример работает не корректно

Сейчас идёт поиск и ищем значение «Роман» по двум полям. Точно так же запрос «Ракзин» выводит фамилию.
но если ввести «Ракзин Роман», то так как поля нет- естественно ничего не выводится. Мне нужно сделать запрос, что данный вариант работал. Как такое реализовать?

2 ответа 2

Если поля нет, значит надо создать его «виртуально»:

Второе условие задано на случай, если имя введут первым («Роман Ракзин»). Но предварительная обработка текста пользовательского запроса все равно не помешает, ибо текущий поиск ничего не найдет если между словами окажется два пробела.

  1. Разбить входную строку на слова и формировать запрос уже по ним.
  2. Использовать механизм полнотекстового поиска с таблицей для индексации необходимых полей

Всё ещё ищете ответ? Посмотрите другие вопросы с метками sql sql-server или задайте свой вопрос.

Похожие

Подписаться на ленту

Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.

дизайн сайта / логотип © 2020 Stack Exchange Inc; пользовательское содержимое попадает под действие лицензии cc by-sa 4.0 с указанием ссылки на источник. rev 2020.11.7.35374

Урок 50. Поиск по нескольким полям

Данное активити позволяет находить элементы списков, указав несколько полей по которым нужно искать данный элемент.

Сообщение об ошибке:да/нет.
Позволяет выводить ошибки в журнал БП.

Где искать элемент: из выпадающего списка вибирается месторасположение искомого элемента,путем выбора списка,в котором элемент может находится.
Если поле является списком, то нужно искать ID элемента, а не его значение.
Для этого нужно:
1.Перейти в режим редактирования элемента списка
2.Нажать F12 и выбрать селектор элементов страницы.
3.Выбрать поле со списком.
4.Внизу появится список с ID.

Параметры поиска элемента(ов): в данном поле указываются параметры, согласно которых будет осуществлен поиск элементов.
Параметры указываются в следующем порядке: Тип поля##буквенный код поля##код условия##искомое значение.

Пример:
CODE##NAIMENOVANIE_MATERIALA##1##Трос
DETAIL_TEXT##1##СПБ
NAME##3##Название
PREVIEW_TEXT##4##Луна-спутник Земли
Очередность заполнения обязательна.

CODE##NAIMENOVANIE_MATERIALA##1##Трос
1.Поле тип код (Code).
2.Буквенный код поля: по сути буквенное название поля, которое можно найти в настройках полей. (действия->настроить поля->нужный тип поля->Код)
3.Код условия: Равно(1), Не равно(2), Больше или равно(3), Меньше или равно(4), Больше(5), Меньше(6), Содержит(7), НЕ Содержит (8)
4.Искомое значение:название искомого значения, может быть текстом, фирой.
Т.е если ищем значение «Трос» в поле тип список, то так и вводим «Трос», если стоимость материала вв виде числа, то вводим искомое число, напримр 100.

DETAIL_TEXT##1##СПБ
1.Тип поля DETAIL_TEXT (детальный текст)
2.Буквенный код поля: по сути буквенное название поля, которое можно найти в настройках полей. (действия->настроить поля->нужный тип поля->Код).
3.Содержимое поля «детальный текст» искомого списка.

NAME##3##Название
1.Тип поля NAME (название).
2.Буквенный код поля: по сути буквенное название поля, которое можно найти в настройках полей.
3.Текст из поля «Название» из искомого списка.

PREVIEW_TEXT##4##Луна-спутник Земли
1.Тип поля PREVIEW_TEXT (Анонс)
2.Буквенный код поля: по сути буквенное название поля, которое можно найти в настройках полей.
3.Текст из поля «Анонс» или «Текст анонса» из искомого списка.

Тип условия для всех параметров: в данном поле можно указать один из 2 типов условий для поиска элементов списка «и/или».

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

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

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

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

Период ожидания. Время ожидания ответа от приложения: Данная опция позволяет ограничить срок ожидания ответа от приложения. Технические особенности платформы Битрикс24 иногда создают ситуацию при которой создаётся очередь из огромного числа запросов. Это может помешать работе нашего активити и бизнес-процесс будет ожидать длительный период времени. Для того, чтобы не останавливать дальнейшие действия, вы можете установить своё время ожидания. Если время истекло и результат не получен, то бизнес-процесс будет двигаться далее по заложенной вами логике.


Возвращаемые параметры данного активити:

Сообщение об ошибке
-Соответствует условиям
-ID элемента списка (первый, если больше 1)
-Результат поиска (массив для итератора)

Урок 50. Поиск по нескольким полям

Прошу помочь в оптимизации поиска

Есть таблица с товарами. В ней такие поля как название, штрих-код, артикул, размер, производитель.
Есть товар:
название — Платье женское «Вика» размер-46

Раньше у меня был поиск по трем фразам. Например:
я ввел «плат жен вик» или «плат вик» и мой поиск находил что мне нужно, отбрасывая все остальное.(поиск был только по наименованию)
Я решил добавить возможность поиска в разных полях таблицы, т.е. по штрих-коду, размеру, производителю.
Мои код работает в данном случае только по одной фразе. Он ищет во всех полям, но только одну фразу.

Если я задаю две или три фразы как я задумывал, например «Вика 46» то он ничего не находит.
Я походу туплю на ровном месте, помогите!

У меня уже закралась мысль просто сначала все данные кидать в одно поле и искать по нему. Но чет как-то не красиво в плане кода.

Хотел еще посоветоваться со знатоками. Может стоит убрать FOR после LOCATE? Вырастет скорость поиска?

Исправлено: PavlikPavlikovich, 16.07.16 12:04

Re: Поиск по нескольким полям таблицы
sphinx

Сообщений: 25991
Откуда: Каменск-Уральски

Дата: 16.07.16 13:27:27

В таком виде с учетом операторских ошибок можно и не выбрать то, что нужно.
Я бы названия нормализовал, завел бы отдельно поля: «ТипОдежды» (лучше даже через справочник), «Род» (м/ж), «Название» (Вика), поле «Размер» (46).
Тогда и выборки будут короткие и содержать меньшее число ошибок.

А если есть в БД «платок»? Как по корню «плат» их различать?

В этом куске достаточно в блок CASE. ENDCASE поместить вычисление выражения WHERE, а потом подставлять его в:

P.S. Если таблица с наименованиями не очень секретная — приложите архив с частью, чтоб самому данные не придумывать.

P.P.S. Могу помочь с нормализацией твоих наименований, есть огромный опыт.

Re: Поиск по нескольким полям таблицы
sphinx

Сообщений: 25991
Откуда: Каменск-Уральски

Дата: 16.07.16 13:43:30

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

Re: Поиск по нескольким полям таблицы
sphinx

Сообщений: 25991
Откуда: Каменск-Уральски

Дата: 16.07.16 14:07:20

Есть очень интересная статья по нечеткому поиску в тексте: habrahabr.ru

Re: Поиск по нескольким полям таблицы
PavlikPavlikovich
Автор

Сообщений: 128

Дата: 16.07.16 21:29:44

Блин я ввел в заблуждение. Надо было запятую поставить.
название — Платье женское «Вика», размер-46
У меня размер в отдельном поле. По поводу М и Ж. Я тоже сначала хотел так делить. Но потом решил что лучше писать женское/мужское в названии, это и для покупателя лучше. Сейчас такое шьют ))) не поймешь кому носить

Про тип «ТипОдежды» я тоже задумывался. Было бы красиво.

«Название» (Вика) Название это вообще редкость, поэтому это и привел в пример.

Вот данная конструкция, которая искала только по наименованию и позволяла мне забить «плат вик» и получить строки исключающие «платок»

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

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

Сам люблю когда все красиво, но у меня товар отечественного производства. Тут никакой нормализацией не поможешь. Объясню в кратце. Некая портниха в фирме «А» сшила платье «АА», а в фирме «Б» другая портниха сшила Юбку «ББ». Так вот эти фирмы (производители) для увеличения ассортимента меняются товарами и в итоге я получаю платье «АА» с разными артикулами, названиями и производителями.

Короче что бы описать то что я хочу (поиск по стольки полям да еще и по 3 фразам, это выходит надо описать (дофига комбинаций между этими полями»
Сделал я как и хотел раньше:
Создал поле в котором слепил наименование+артикул+размер+производитель. И ищу по этому полю. Работает нормально.

Re: Поиск по нескольким полям таблицы
sphinx

Сообщений: 25991
Откуда: Каменск-Уральски

Дата: 16.07.16 22:11:57

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

Необязательные для заполнения поля (например, поле Наименование и/или Размер) — просто не будут участвовать в поиске, что ж тут странного/необычного?

Чем для ПОКУПАТЕЛЯ лучше то, КАК вы храните информацию в БД? А при выводе (это ведь совсем другой вопрос, не так ли?) — вы вольны показывать то, что надо, а совсем не так, как храните в БД. Или что-то вообще не показывать, если есть такая необходимость.
Что мешает завести такой справочник:
1 — муж.
2 — жен.
3 — муж./жен.
Вам надо поставить нужный признак — Вы его поставите. Не понимаю, что вызывает затруднения? Зато при выборке Вы потом не подстроку в строке ищите (очень медленная операция на больших объемах), а ОДНОЗНАЧНО указываете поиск (если нужно) по этому признаку.

PavlikPavlikovich
sphinx
В этом куске достаточно в блок CASE. ENDCASE поместить вычисление выражения WHERE, а потом подставлять его в:

SELECT *,RECNO() as tekrec FROM ostat INTO CURSOR sw1

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

Я про то, что в блок CASE..ENDCASE вполне достаточно поместить только вычисление выражения для части WHERE. А потом это условие подставить в запрос.

Так эти артикулы/названия/производители — лишь дополнительные признаки, которые тоже можно хранить в отдельных полях. А если их нет в НЕНОРМАЛИЗОВАННОМ названии — значит просто соответствующее поле не будет заполнено. Кстати, по артикулам была очень недавно отдельная тема, по сути уникальные артикулы оказались неуникальны и их приводили к уникальности. По этому вопросу — или в поиск, или к PaulWist.

Re: Поиск по нескольким полям таблицы
PavlikPavlikovich
Автор

Сообщений: 128

Дата: 16.07.16 22:27:19


Да, да. Именно это и хочу.

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

Re: Поиск по нескольким полям таблицы
sphinx

Сообщений: 25991
Откуда: Каменск-Уральски

Дата: 16.07.16 23:23:37

1) Про CASE..ENDCASE. Вполне достаточно все условие (в зависимости от индекса CASE) поместить в переменную:

А потом просто выполнить:

Да, код может и не очень сильно сокращается (из-за демонического CASE..ENDCASE), но логически куда более читаемо/последовательно.

Re: Поиск по нескольким полям таблицы
sphinx

Сообщений: 25991
Откуда: Каменск-Уральски

Дата: 16.07.16 23:32:06

Так оно. Два человека, стоящих на небольшом расстоянии в зоне прямой видимости можно связать проводом, равным по длине расстоянию между ними. А можно потянуть через село Кукушкино. И не дай бох, эти люди попытаются перейти в другое место — есть мысли, в каком случае будет менее затратно?

Я бы и рад, но мне бы хотелось пример ВАШИХ данных. Увы, без Ваших данных я довольно плохо понимаю, как и что мне обработать, чтоб получился какой-то код.

Re: Поиск по нескольким полям таблицы
sphinx

Сообщений: 25991
Откуда: Каменск-Уральски

Дата: 16.07.16 23:55:03

Про суррогатный ключ я выразился не совсем корректно — он нужен лишь в случае, когда запись в основной таблице НЕ имеет ID, тогда его формируют искусственно, выбирая ключевые/значимые поля.

В нашем случае я имел в виду примерно такое:

То есть из справочников элементарным запросом мы получим Вашу ненормализованную строку, если есть такая необходимость (например, чтобы экспортировать все это в прайс-лист).

Re: Поиск по нескольким полям таблицы
PavlikPavlikovich
Автор

Сообщений: 128

Дата: 17.07.16 12:50:46

Спасибо за такое подробное разъяснение. Подумаю над таким подходом.
Нашел я свою ошибку. Она была на поверхности.
Исправил это

Теперь на выходе получаю то что нужно.

Исправлено: PavlikPavlikovich, 17.07.16 12:51

Re: Поиск по нескольким полям таблицы
sphinx

Сообщений: 25991
Откуда: Каменск-Уральски

Дата: 17.07.16 13:16:23

Вы все же упрямо продолжаете эксплуатировать говно-код. А я предлагаю существенно упростить подход и программирование.

Данные так и получу, чтобы показать, что и как я имею в виду?

Re: Поиск по нескольким полям таблицы
Sawradym

Сообщений: 1912
Откуда: Винница

Дата: 17.07.16 17:22:21
Re: Поиск по нескольким полям таблицы
Igor Korolyov

Сообщений: 33144

Дата: 17.07.16 20:54:28

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

Re: Поиск по нескольким полям таблицы
sphinx

Сообщений: 25991
Откуда: Каменск-Уральски

Дата: 17.07.16 22:19:26

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

А как правильно — здесь подскажут, не надо придумывать решение, которое никому не нужно. Я пытался другими словами сказать, не принижая попыток (когда-то все начинали).. Но человек упрям.

Королев на этот код (как и я) смотреть без слез не может.

Я же просил данные — по крайней мере наше сообщество ( не только я) ГОТОВЫ помочь. Но человек уперто пытается решать свои задачи только с уровня своего понимания. Да, я в свое время тоже так и пытался делать, но есть этот замечательный форум, тут помощь всегда окажут.

Re: Поиск по нескольким полям таблицы
sphinx

Сообщений: 25991
Откуда: Каменск-Уральски

Дата: 17.07.16 22:21:53

Игорь, черти магический круг. Пришлет данные — разберемся. Может я не во всем прав, но с говнокодом что-то предлагаю..

Re: Поиск по нескольким полям таблицы
Аспид

Сообщений: 3353
Откуда: Москва

Дата: 18.07.16 11:53:10

+100500
Мне тоже так кажется. Не знаю ск нужно текстбоксов, но для поиска по нескольким полям, очевидно что несколько)
И это будет проще всем.
А то ведь юзер может и так написать
46 разм. вика. плат.
И Аут твоему поиску)))
Они такие выдумщики)))

Re: Поиск по нескольким полям таблицы
sphinx

Сообщений: 25991
Откуда: Каменск-Уральски

Дата: 18.07.16 16:19:50
Re: Поиск по нескольким полям таблицы
dfr

Сообщений: 213
Откуда: Барнаул

Дата: 19.07.16 09:51:44

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

Re: Поиск по нескольким полям таблицы
Влад Колосов

Сообщений: 22664
Откуда: Ростов-на-Дону

Дата: 19.07.16 18:06:16

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

Продвинутые примеры с функцией ВПР: поиск по нескольким критериям

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

  • Поиск в Excel по нескольким критериям
  • Извлекаем 2-е, 3-е и т.д. значения, используя ВПР
  • Извлекаем все повторения искомого значения
  • Двумерный поиск по известным строке и столбцу
  • Используем несколько ВПР в одной формуле
  • Динамическая подстановка данных из разных таблиц

Поиск в Excel по нескольким критериям

Функция ВПР в Excel – это действительно мощный инструмент для выполнения поиска определённого значения в базе данных. Однако, есть существенное ограничение – её синтаксис позволяет искать только одно значение. Как же быть, если требуется выполнить поиск по нескольким условиям? Решение Вы найдёте далее.

Пример 1: Поиск по 2-м разным критериям

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

Обычная функция ВПР не будет работать по такому сценарию, поскольку она возвратит первое найденное значение, соответствующее заданному искомому значению. Например, если Вы хотите узнать количество товара Sweets, заказанное покупателем Jeremy Hill, запишите вот такую формулу:

– эта формула вернет результат 15, соответствующий товару Apples, так как это первое совпадающее значение.

Есть простой обходной путь – создать дополнительный столбец, в котором объединить все нужные критерии. В нашем примере это столбцы Имя клиента (Customer) и Название продукта (Product). Не забывайте, что объединенный столбец должен быть всегда крайним левым в диапазоне поиска, поскольку именно левый столбец функция ВПР просматривает при поиске значения.

Итак, Вы добавляете вспомогательный столбец в таблицу и копируете по всем его ячейкам формулу вида: =B2&C2. Если хочется, чтобы строка была более читаемой, можно разделить объединенные значения пробелом: =B2&” “&C2. После этого можно использовать следующую формулу:

=VLOOKUP(«Jeremy Hill Sweets»,$A$7:$D$18,4,FALSE)
=ВПР(«Jeremy Hill Sweets»;$A$7:$D$18;4;ЛОЖЬ)

Где ячейка B1 содержит объединенное значение аргумента lookup_value (искомое_значение), а 4 – аргумент col_index_num (номер_столбца), т.е. номер столбца, содержащего данные, которые необходимо извлечь.

Пример 2: ВПР по двум критериям с просматриваемой таблицей на другом листе

Если Вам необходимо обновить основную таблицу (Main table), добавив данные из второй таблицы (Lookup table), которая находится на другом листе или в другой рабочей книге Excel, то Вы можете собрать искомое значение непосредственно в формуле, которую вставляете в основную таблицу.

Как и в предыдущем примере, Вам понадобится в таблице поиска (Lookup table) вспомогательный столбец с объединенными значениями. Этот столбец должен быть крайним левым в заданном для поиска диапазоне.

Итак, формула с ВПР может быть такой:

Здесь в столбцах B и C содержатся имена клиентов и названия продуктов соответственно, а ссылка Orders!$A&$2:$D$2 определяет таблицу для поиска на другом листе.

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

Чтобы формула работала, значения в крайнем левом столбце просматриваемой таблицы должны быть объединены точно так же, как и в критерии поиска. На рисунке выше мы объединили значения и поставили между ними пробел, точно так же необходимо сделать в первом аргументе функции (B2&” “&C2).

Запомните! Функция ВПР ограничена 255 символами, она не может искать значение, состоящее из более чем 255 символов. Имейте это ввиду и следите, чтобы длина искомого значения не превышала этот лимит.

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

Извлекаем 2-е, 3-е и т.д. значения, используя ВПР

Вы уже знаете, что ВПР может возвратить только одно совпадающее значение, точнее – первое найденное. Но как быть, если в просматриваемом массиве это значение повторяется несколько раз, и Вы хотите извлечь 2-е или 3-е из них? А что если все значения? Задачка кажется замысловатой, но решение существует!

Предположим, в одном столбце таблицы записаны имена клиентов (Customer Name), а в другом – товары (Product), которые они купили. Попробуем найти 2-й, 3-й и 4-й товары, купленные заданным клиентом.

Простейший способ – добавить вспомогательный столбец перед столбцом Customer Name и заполнить его именами клиентов с номером повторения каждого имени, например, John Doe1, John Doe2 и т.д. Фокус с нумерацией сделаем при помощи функции COUNTIF (СЧЁТЕСЛИ), учитывая, что имена клиентов находятся в столбце B:

После этого Вы можете использовать обычную функцию ВПР, чтобы найти нужный заказ. Например:

    Находим 2-й товар, заказанный покупателем Dan Brown:

=VLOOKUP(«Dan Brown2»,$A$2:$C$16,3,FALSE)
=ВПР(«Dan Brown2»;$A$2:$C$16;3;ЛОЖЬ)
Находим 3-й товар, заказанный покупателем Dan Brown:

=VLOOKUP(«Dan Brown3»,$A$2:$C$16,3,FALSE)
=ВПР(«Dan Brown3»;$A$2:$C$16;3;ЛОЖЬ)

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

Если Вы ищите только 2-е повторение, то можете сделать это без вспомогательного столбца, создав более сложную формулу:

  • $F$2 – ячейка, содержащая имя покупателя (она неизменна, обратите внимание – ссылка абсолютная);
  • $B$ – столбец Customer Name;
  • Table4 – Ваша таблица (на этом месте также может быть обычный диапазон);
  • $C16 – конечная ячейка Вашей таблицы или диапазона.

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

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

Извлекаем все повторения искомого значения

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

Например, формула, представленная ниже, находит все повторения значения из ячейки F2 в диапазоне B2:B16 и возвращает результат из тех же строк в столбце C.

Введите эту формулу массива в несколько смежных ячеек, например, в ячейки F4:F8, как показано на рисунке ниже. Количество ячеек должно быть равным или большим, чем максимально возможное число повторений искомого значения. Не забудьте нажать Ctrl+Shift+Enter, чтобы правильно ввести формулу массива.

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

Часть 1:

$F$2=B2:B16 – сравниваем значение в ячейке F2 с каждым из значений диапазона B2:B16. Если найдено совпадение, то выражение СТРОКА(C2:C16)-1 возвращает номер соответствующей строки (значение -1 позволяет не включать строку заголовков). Если совпадений нет, функция IF (ЕСЛИ) возвращает пустую строку.

Результатом функции IF (ЕСЛИ) окажется вот такой горизонтальный массив:

Часть 2:

Здесь функция ROW (СТРОКА) действует как дополнительный счётчик. Так как формула скопирована в ячейки F4:F9, мы вычитаем число 3 из результата функции, чтобы получить значение 1 в ячейке F4 (строка 4, вычитаем 3), чтобы получить 2 в ячейке F5 (строка 5, вычитаем 3) и так далее.

Часть 3:

Функция SMALL (НАИМЕНЬШИЙ) возвращает n-ое наименьшее значение в массиве данных. В нашем случае, какую по счёту позицию (от наименьшего) возвращать – определено функцией ROW (СТРОКА) (смотри Часть 2). Так, для ячейки F4 функция НАИМЕНЬШИЙ(<массив>;1) возвращает 1-й (наименьший) элемент массива, то есть 1. Для ячейки F5 возвращает 2-й наименьший элемент массива, то есть 3, и так далее.

Часть 4:

Функция INDEX (ИНДЕКС) просто возвращает значение определённой ячейки в массиве C2:C16. Для ячейки F4 функция ИНДЕКС($C$2:$C$16;1) возвратит Apples, для F5 функция ИНДЕКС($C$2:$C$16;3) возвратит Sweets и так далее.

Часть 5:


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

Двумерный поиск по известным строке и столбцу

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

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

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

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

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

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

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

  • Ищем символы “Mar” – аргумент lookup_value (искомое_значение);
  • Ищем в ячейках от A1 до I1 – аргумент lookup_array (просматриваемый_массив);
  • Возвращаем точное совпадение – аргумент match_type (тип_сопоставления).

Использовав в третьем аргументе, Вы говорите функции ПОИСКПОЗ искать первое значение, в точности совпадающее с искомым значением. Это равносильно значению FALSE (ЛОЖЬ) для четвёртого аргумента ВПР.

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

Функция СУММПРОИЗВ

Функция СУММПРОИЗВ (SUMPRODUCT) возвращает сумму произведений выбранных массивов:

Функции ИНДЕКС и ПОИСКПОЗ

В следующей статье я буду объяснять эти функции во всех деталях, так что сейчас можете просто скопировать эту формулу:

Именованные диапазоны и оператор пересечения

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

  1. Выделите таблицу, откройте вкладку Formulas (Формулы) и нажмите Create from Selection (Создать из выделенного).
  2. Отметьте галочками Top row (в строке выше) и Left column (в столбце слева). Microsoft Excel назначит имена диапазонам из значений в верхней строке и левом столбце Вашей таблицы. Теперь Вы можете осуществлять поиск, используя эти имена, напрямую, без создания формул.
  3. В любой пустой ячейке запишите =имя_строки имя_столбца, например, так:

=Lemons Mar
… или наоборот:
=Mar Lemons

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

При вводе имени, Microsoft Excel будет показывать подсказку со списком подходящих имен, так же, как при вводе формулы.

  1. Нажмите Enter и проверьте результат

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

Используем несколько ВПР в одной формуле

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

Давайте разберем следующий пример. У нас есть основная таблица (Main table) со столбцом SKU (new), куда необходимо добавить столбец с соответствующими ценами из другой таблицы. Кроме этого, у нас есть 2 таблицы поиска. Первая (Lookup table 1) содержит обновленные номера SKU (new) и названия товаров, а вторая (Lookup table 2) – названия товаров и старые номера SKU (old).

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

    Запишите функцию ВПР, которая находит имя товара в таблице Lookup table 1, используя SKU, как искомое значение:

Здесь New_SKU – именованный диапазон $A:$B в таблице Lookup table 1, а 2 – это столбец B, который содержит названия товаров (смотрите на рисунке выше)
Запишите формулу для вставки цен из таблицы Lookup table 2 на основе известных названий товаров. Для этого вставьте созданную ранее формулу в качестве искомого значения для новой функции ВПР:

Здесь Price – именованный диапазон $A:$C в таблице Lookup table 2, а 3 – это столбец C, содержащий цены.

На рисунке ниже виден результат, возвращаемый созданной нами формулой:

Динамическая подстановка данных из разных таблиц при помощи ВПР и ДВССЫЛ

В начале разъясним, что мы подразумеваем под выражением “Динамическая подстановка данных из разных таблиц”, чтобы убедиться правильно ли мы понимает друг друга.

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

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

Если у Вас всего два таких отчета, то можно использовать до безобразия простую формулу с функциями ВПР и ЕСЛИ (IF), чтобы выбрать нужный отчет для поиска:

  • $D$2 – это ячейка, содержащая название товара. Обратите внимание, здесь мы используем абсолютные ссылки, чтобы избежать изменения искомого значения при копировании формулы в другие ячейки.
  • $D3 – это ячейка с названием региона. Используем абсолютную ссылку для столбца и относительную ссылку для строки, поскольку планируем копировать формулу в другие ячейки того же столбца.
  • FL_Sales и CA_Sales – названия таблиц (или именованных диапазонов), в которых содержаться соответствующие отчеты о продажах. Вы, конечно же, можете использовать обычные названия листов и ссылки на диапазоны ячеек, например ‘FL Sheet’!$A$3:$B$10, но именованные диапазоны гораздо удобнее.

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

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

  • $D$2 – это ячейка с названием товара, она неизменна благодаря абсолютной ссылке.
  • $D3 – это ячейка, содержащая первую часть названия региона. В нашем примере это FL.
  • _Sales – общая часть названия всех именованных диапазонов или таблиц. Соединенная со значением в ячейке D3, она образует полное имя требуемого диапазона. Ниже приведены некоторые подробности для тех, кто не имеет опыта работы с функцией ДВССЫЛ.

Как работают ДВССЫЛ и ВПР

Во-первых, позвольте напомнить синтаксис функции ДВССЫЛ (INDIRECT):

Первый аргумент может быть ссылкой на ячейку (стиль A1 или R1C1), именем диапазона или текстовой строкой. Второй аргумент определяет, какого стиля ссылка содержится в первом аргументе:

  • A1, если аргумент равен TRUE (ИСТИНА) или не указан;
  • R1C1, если FALSE (ЛОЖЬ).

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

Итак, давайте вернемся к нашим отчетам по продажам. Если Вы помните, то каждый отчёт – это отдельная таблица, расположенная на отдельном листе. Чтобы формула работала верно, Вы должны дать названия своим таблицам (или диапазонам), причем все названия должны иметь общую часть. Например, так: CA_Sales, FL_Sales, TX_Sales и так далее. Как видите, во всех именах присутствует “_Sales”.

Функция ДВССЫЛ соединяет значение в столбце D и текстовую строку “_Sales”, тем самым сообщая ВПР в какой таблице искать. Если в ячейке D3 находится значение “FL”, формула выполнит поиск в таблице FL_Sales, если “CA” – в таблице CA_Sales и так далее.

Результат работы функций ВПР и ДВССЫЛ будет следующий:

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

Если функция ДВССЫЛ ссылается на другую книгу, то эта книга должна быть открытой. Если же она закрыта, функция сообщит об ошибке #REF! (#ССЫЛ!).

Поиск по нескольким полям

Я думаю, что я не убираю django-haystack должным образом:

У меня есть модель данных, содержащая несколько полей, и я бы попросил их выполнить два:

Настройки поискового индекса:

Но когда я выполняю поиск, выполняется поиск только поля «имя пользователя»; «twitter_account» игнорируется. Когда я выбираю Searchresults через dbshell, объекты содержат правильные значения для «user» и «twitter_account», но на странице результатов отображается «нет результатов»:

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

Каждый SearchIndex требует наличия одно (и только одно) поле с документ = True. Это указывает на оба Хейстек и поисковая система о какое поле является основным полем для поиск внутри.

Попробуйте указать индекс следующим образом

и создайте файл с именем поиск/индексов//userprofile_text.txt

который будет содержать следующее

теперь haystack будет искать в содержимом этого файла (где вы можете добавить все, что захотите), когда вы не укажете индексный фильтр.

Урок 50. Поиск по нескольким полям

Здесь могла бы быть ваша реклама

Покинул форум
Сообщений всего: 4574
Дата рег-ции: Июль 2006
Откуда: Israel

Секрет
Теперь, когда вы уже наверняка второпях отправили свой запрос,
я расскажу вам простой секрет, который сэкономит вам уйму ожиданий,
даже если первый ответ по теме последуем сразу же.

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

После этого приходится начинать уточнять этим неграмотным что мне надо.
Они что, сами читать не умеют? А уточнять приходится.
И иногда пока они переварят то что я им скажу проходит и не одна ночь..

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

Поэтому с тех пор я строю свои вопросы по проверенной давным давно схеме:
Что есть
Что нужно получить
Как я пытался
Почему или что у меня не получилось.

На последок как оно происходит на форумах

Новичок: Подскажите пожалуста самый крепкий сорт дерева! Весь инет перерыл, поиском пользовался!
Старожил: Объясни, зачем тебе понадобилось дерево? Сейчас оно в строительстве практически не используется.
Новичок: Я небоскрёб собираюсь строить. Хочу узнать, из какого дерева делать перекрытия между этажами!
Старожил: Какое дерево? Ты вообще соображаешь, что говоришь?
Новичок: Чем мне нравиться этот форум — из двух ответов ниодного конкретного. Одни вопросы неподелу!
Старожил: Не нравится — тебя здесь никто не держит. Но если ты не соображаешь, что из дерева небоскрёбы не строят, то лучше бы тебе сначала школу закончить.
Новичок: Не знаите — лучше молчите! У меня дедушка в деревянном доме живёт! У НЕГО НИЧЕГО НЕ ЛОМАЕТСЯ.
Но у него дом из сосны, а я понимаю, что для небоскрёба нужно дерево прочнее! Поэтому и спрашиваю. А от вас нормального ответа недождёшся.
Прохожий: Самое крепкое дерево — дуб. Вот тебе технология вымачивания дуба в солёной воде, она придаёт дубу особую прочность:
Новичок: Спасибо, братан! То что нужно.

Отредактировано модератором: Uchkuma, 26 Апреля, 2011 — 10:21:12

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

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

Для вывода имен клиентов, официальный адрес и адрес доставки которых совпадают:

1. Создайте новый запрос

2. Добавьте в запрос таблицы «Клиенты» и «Заказы»

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

4. Перетащите поля «Название» и «Адрес» таблицы «Клиенты», а затем поле «АдресПолучателя» таблицы «Заказы» на бланк запроса.

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

По умолчанию свойство запроса «Уникальные записи» имеет значение Да, а свойство «Уникальные значения» — Нет. Поэтому в инструкцию SQL, связанную с запросом, добавляется ключевое слово ANSI SQL distinctrow. Измените значение свойства «Уникальные значения» на «Да». При этом вместо ключевого слова SQL DISTINCT в инструкции SQL будет участвовать ключевое слово Access distinctrow:

SELECT DISTINCTROW Клиенты.Название, Клиенты.Адрес, Заказы.АдресПолучателя

FROM Клиенты INNER JOIN Заказы ON (Клиенты.Адрес = Заказы.АдресПолучателя) AND (Клиенты.КодКлиента = Заказы.КодКлиента)

ORDER BY Клиенты.Название;

Для большинства заказов адрес клиента и адрес доставки совпадают, поэтому было бы лучше отыскивать заказы, для которых адреса различаются. Однако в Access нельзя создать такой запрос с помощью соединения по нескольким полям, т. к. в операции Access SQL inner join использовать оператор <> не допускается.

Дата добавления: 2020-02-25 ; просмотров: 488 | Нарушение авторских прав

Bitrix -cложный поиск одновременно по нескольким полям

Сложный поиск одновременно по нескольким полям в bitrix. Ищем не прямое вхождение а поск строки в подстроке (символы процентов). Поиск понимает гет запрос $_GET[‘s’] (…php?s=словапоиска)
OR- позволяет искать одновременно по нескольким полям.
1- обычный поиск по данному инфоблока
2. Поиск по значению настраиваемого полям
3. Поиск по значению связанного данного- у нас в данном коде нужно значение name.
пояснение.У нас есть инфоблок книги, есть второй инфоблок , называемый авторы. У одной книги может быть несколько авторов. Так вот, выводя книги мы фильтруем по имени автора. PROPERTY_ATT_AUTHOR- это настраиваемое поле книги. ДОбавляем точку и дописываем name- обращаемся уже к таблице автор и полю NAME автора. Т. битриском образовали связь один ко многим.
Аналогичен запросу (jeft join … on) в Mysql.

Урок 50. Поиск по нескольким полям

Пожалуйста, выделяйте текст программы тегом [сode=pas] . [/сode] . Для этого используйте кнопку [code=pas] в форме ответа или комбобокс, если нужно вставить код на языке, отличном от Дельфи/Паскаля. Обязательно указание:
1) типа базы данных (Paradox/Oracle/Interbase и т.п.)
2) способа доступа к базе данных (ODBC/ADO/DAO/BDE и т.п.)
Например: Paradox/BDE, MS Access/ADO

Что Вы имеете ввиду .

Причем здесь квери. Мне надо курсор вернуть на ту же стороку, на которой он был до того, как датасет был переоткрыт. к тому же это TdxMemData. Вобщем мимо тазика ты немного. Тренируй телепатию!

Благодарю
Да вот как Марьяж написал так и делал, только VarArrayOf почему-то пропустил. Прогнал вобщем

OracleDataSet1.Locate(‘FC_FAM;FC_IM;FC_OTCH’, VarArrayOf([‘КУЗНЕЦОВА’,’ТАТЬЯНА’,’НИКОЛАЕВНА’]), [loPartialKey]);

Проверял, у меня работает.

Действительно работает. Спасибо. +

Ребята, тема закрыта, ибо благодаря Марьяжу я свою проблему решил. Но мне интересно, откуда вы вообще оракл взяли. Я вроде даже СУБД не упоминал. И причем здесь SQL? Или я чего-то не понимаю, или средствами SQL невозможно изменить активную запись в датасете, а locate как раз служит для этого.

Топ-пост этого месяца:  Смотрите спецификацию CSS Grid Layout или что написано в шапке спецификации
Добавить комментарий