Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Подставка значений по введенным данным из нескольких условий - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подставка значений по введенным данным из нескольких условий (Формулы/Formulas)
Подставка значений по введенным данным из нескольких условий
Narahon Дата: Вторник, 05.06.2018, 17:04 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Здравствуйте! 2 недели пытаюсь решить задачу, которую сам перед собой поставил, но так до сих пор и не получилось.

Есть документ, который состоит из двух листов, на одном листе поля ввода данных (титульный лист) на втором - некая база данных о клиентах.

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

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

Логику я понимаю, а вот с формулой какая-то беда...

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

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

Надеюсь объяснил нормально)

Файл примера вложил, темно-синим помечены ячейки, по которым ведётся поиск

Удалил с психа формулы, которые прописывал, сейчас восстанавливаю и вставлю)

А вот формула, которая была в оригинальном файле:

Цитата

=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИ(ЕСЛИОШИБКА(И(ЕОШ(ИНДЕКС('База клиентов'!A3:A99998;ПОИСКПОЗ(СЦЕПИТЬ(ПСТР(B4;1;1);" ";ПСТР(B4;2;3);" ";ПСТР(B4;5;3);" ";ПСТР(B4;8;2);" ";ПСТР(B4;10;2));'База клиентов'!J3:J99998;0)));ЕОШ(ИНДЕКС('База клиентов'!A3:A99998;ПОИСКПОЗ(СЦЕПИТЬ(B6;" ";B7);'База клиентов'!E3:E99998;0))))=ЛОЖЬ;ИНДЕКС('База клиентов'!A3:A99998;ПОИСКПОЗ(E7;'База клиентов'!M3:M99998;0)));ЕСЛИОШИБКА(ИНДЕКС('База клиентов'!A3:A99998;ПОИСКПОЗ(СЦЕПИТЬ(ПСТР(B4;1;1);" ";ПСТР(B4;2;3);" ";ПСТР(B4;5;3);" ";ПСТР(B4;8;2);" ";ПСТР(B4;10;2));'База клиентов'!J3:J99998;0));ИНДЕКС('База клиентов'!A3:A99998;ПОИСКПОЗ(СЦЕПИТЬ(B6;" ";B7);'База клиентов'!E3:E99998;0))));ИНДЕКС('База клиентов'!A3:A99998;ПОИСКПОЗ(E7;'База клиентов'!M3:M99998;0)));"")


P.S. создал отдельную тему по рекомендации модератора
К сообщению приложен файл: 2562458.xlsx (13.2 Kb)


Сообщение отредактировал Narahon - Вторник, 05.06.2018, 17:07
 
Ответить
СообщениеЗдравствуйте! 2 недели пытаюсь решить задачу, которую сам перед собой поставил, но так до сих пор и не получилось.

Есть документ, который состоит из двух листов, на одном листе поля ввода данных (титульный лист) на втором - некая база данных о клиентах.

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

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

Логику я понимаю, а вот с формулой какая-то беда...

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

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

Надеюсь объяснил нормально)

Файл примера вложил, темно-синим помечены ячейки, по которым ведётся поиск

Удалил с психа формулы, которые прописывал, сейчас восстанавливаю и вставлю)

А вот формула, которая была в оригинальном файле:

Цитата

=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ЕСЛИ(ЕСЛИОШИБКА(И(ЕОШ(ИНДЕКС('База клиентов'!A3:A99998;ПОИСКПОЗ(СЦЕПИТЬ(ПСТР(B4;1;1);" ";ПСТР(B4;2;3);" ";ПСТР(B4;5;3);" ";ПСТР(B4;8;2);" ";ПСТР(B4;10;2));'База клиентов'!J3:J99998;0)));ЕОШ(ИНДЕКС('База клиентов'!A3:A99998;ПОИСКПОЗ(СЦЕПИТЬ(B6;" ";B7);'База клиентов'!E3:E99998;0))))=ЛОЖЬ;ИНДЕКС('База клиентов'!A3:A99998;ПОИСКПОЗ(E7;'База клиентов'!M3:M99998;0)));ЕСЛИОШИБКА(ИНДЕКС('База клиентов'!A3:A99998;ПОИСКПОЗ(СЦЕПИТЬ(ПСТР(B4;1;1);" ";ПСТР(B4;2;3);" ";ПСТР(B4;5;3);" ";ПСТР(B4;8;2);" ";ПСТР(B4;10;2));'База клиентов'!J3:J99998;0));ИНДЕКС('База клиентов'!A3:A99998;ПОИСКПОЗ(СЦЕПИТЬ(B6;" ";B7);'База клиентов'!E3:E99998;0))));ИНДЕКС('База клиентов'!A3:A99998;ПОИСКПОЗ(E7;'База клиентов'!M3:M99998;0)));"")


P.S. создал отдельную тему по рекомендации модератора

Автор - Narahon
Дата добавления - 05.06.2018 в 17:04
_Boroda_ Дата: Вторник, 05.06.2018, 17:44 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А вот формула, которая была в оригинальном файле:
Мать моя женщина! Сильно. Но ...

Ваш вопрос нельзя решить формулой. В ячейке может быть или вводимое вручную значение, или формула. Одновременно быть не может. Поэтому только макрос.
В модуль листа "Титул" (правой мышой на ярлык листа - Исходный текст)
К сообщению приложен файл: primer-16-1.xlsm (22.4 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
А вот формула, которая была в оригинальном файле:
Мать моя женщина! Сильно. Но ...

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

Автор - _Boroda_
Дата добавления - 05.06.2018 в 17:44
Narahon Дата: Вторник, 05.06.2018, 17:54 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

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

Это понятно, поэтому я тоже делал через макрос, но с ними я дружу крайне плохо)) Спасибо Вам за подсказку, буду разбирать этот макрос и подстраивать под себя, надеюсь пойму что куда)

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

Это понятно, поэтому я тоже делал через макрос, но с ними я дружу крайне плохо)) Спасибо Вам за подсказку, буду разбирать этот макрос и подстраивать под себя, надеюсь пойму что куда)

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

Автор - Narahon
Дата добавления - 05.06.2018 в 17:54
_Boroda_ Дата: Вторник, 05.06.2018, 18:12 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Конечно возможно
К сообщению приложен файл: primer-16-2.xlsm (22.4 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеКонечно возможно

Автор - _Boroda_
Дата добавления - 05.06.2018 в 18:12
Narahon Дата: Вторник, 05.06.2018, 18:43 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Излишнее цитирование удалено администрацией - это нарушение п.5j Правил форума

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

Да и вообще, нужно мне браться за изучение макросов)
 
Ответить
СообщениеИзлишнее цитирование удалено администрацией - это нарушение п.5j Правил форума

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

Да и вообще, нужно мне браться за изучение макросов)

Автор - Narahon
Дата добавления - 05.06.2018 в 18:43
Narahon Дата: Среда, 06.06.2018, 12:21 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Конечно возможно


Логику макроса я частично понял, но как переделать его полностью под свой формат не понял совершенно((

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

Высылаю файл оригинала (урезанный немного), если поможете, буду бесконечно благодарен!
Внутри макрос немного переделанный мной, было бы интересно узнать ошибки, которые я совершил)
К сообщению приложен файл: arenda-primer.xlsm (43.7 Kb)
 
Ответить
Сообщение
Конечно возможно


Логику макроса я частично понял, но как переделать его полностью под свой формат не понял совершенно((

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

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

Автор - Narahon
Дата добавления - 06.06.2018 в 12:21
_Boroda_ Дата: Среда, 06.06.2018, 15:36 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Это нужно почти полностью переписывать всё. Логика-то поменялась. И, кстати, Вы заметили, что я поменял местами столбцы в базе?


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЭто нужно почти полностью переписывать всё. Логика-то поменялась. И, кстати, Вы заметили, что я поменял местами столбцы в базе?

Автор - _Boroda_
Дата добавления - 06.06.2018 в 15:36
Narahon Дата: Среда, 06.06.2018, 18:24 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Вы заметили, что я поменял местами столбцы в базе?


Если честно, то нет, я подумал, что это я так их расставил)
 
Ответить
Сообщение
Вы заметили, что я поменял местами столбцы в базе?


Если честно, то нет, я подумал, что это я так их расставил)

Автор - Narahon
Дата добавления - 06.06.2018 в 18:24
Narahon Дата: Четверг, 07.06.2018, 16:38 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Уважаемый _Boroda_, решил я всё таки переделать свою базу под формат этого макроса, всё работает отлично, но появился маленький вопрос, можно ли сделать поиск по паспорту, если они разделены на 2 строчки (серия и номер) или это так же меняет всю логику формулу и будет легче их объединить?

Заранее спасибо!)
 
Ответить
СообщениеУважаемый _Boroda_, решил я всё таки переделать свою базу под формат этого макроса, всё работает отлично, но появился маленький вопрос, можно ли сделать поиск по паспорту, если они разделены на 2 строчки (серия и номер) или это так же меняет всю логику формулу и будет легче их объединить?

Заранее спасибо!)

Автор - Narahon
Дата добавления - 07.06.2018 в 16:38
_Boroda_ Дата: Четверг, 07.06.2018, 16:47 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Забыл я. Кладите новый файл. И вопрос - на какую ячейку вешать запуск макроса - на серию или на номер? Например, если Вы заводите серию, то ничего не происходит, если Вы заводите номер, то проверяем серию и, если там не пусто, то запускаем поиск.


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЗабыл я. Кладите новый файл. И вопрос - на какую ячейку вешать запуск макроса - на серию или на номер? Например, если Вы заводите серию, то ничего не происходит, если Вы заводите номер, то проверяем серию и, если там не пусто, то запускаем поиск.

Автор - _Boroda_
Дата добавления - 07.06.2018 в 16:47
Narahon Дата: Понедельник, 11.06.2018, 10:30 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Забыл я. Кладите новый файл.


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

А по поводу на какую ячейку, то, наверное, будет правильно делать по номеру, меньше вероятности попасть на одинаковые числа.
К сообщению приложен файл: baza-primer.xlsm (47.0 Kb)
 
Ответить
Сообщение
Забыл я. Кладите новый файл.


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

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

Автор - Narahon
Дата добавления - 11.06.2018 в 10:30
_Boroda_ Дата: Среда, 13.06.2018, 13:42 | Сообщение № 12
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
К сообщению приложен файл: baza-primer_1.xlsm (42.8 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?

Автор - _Boroda_
Дата добавления - 13.06.2018 в 13:42
Narahon Дата: Среда, 13.06.2018, 14:48 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 42
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Так нужно?


Да! Огромное Вам спасибо, это просто идеальный вариант, лучше и не мог представить! Сейчас вставлю код в свой файлик, протестирую по максимуму)
 
Ответить
Сообщение
Так нужно?


Да! Огромное Вам спасибо, это просто идеальный вариант, лучше и не мог представить! Сейчас вставлю код в свой файлик, протестирую по максимуму)

Автор - Narahon
Дата добавления - 13.06.2018 в 14:48
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подставка значений по введенным данным из нескольких условий (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!