Здравствуйте! 2 недели пытаюсь решить задачу, которую сам перед собой поставил, но так до сих пор и не получилось.
Есть документ, который состоит из двух листов, на одном листе поля ввода данных (титульный лист) на втором - некая база данных о клиентах.
Задача стоит следующая, сделать так, чтобы при вводе в определенные ячейки на выбор (их три: телефон, гос. номер, паспорт) excel искал это значение в "базе" и если бы находил, то во все остальные ячейки подставлял данные от нужного клиента
В целом, у меня получилось сделать это по одному значению, если добавить второе или третье, то появляется ошибка зациклинности.
Логику я понимаю, а вот с формулой какая-то беда...
Нужно найти (я делал через индекс и поискпоз) номер телефона и если нашел, то вставить в нужные ячейки нужные данные, если телефон не нашел, то искать по паспорту, если всё норм, то вставить, если нет, то искать по гос. номеру, если норм, то вставить, если нет, то оставить пустым.
Но так как все ячейки должны быть свободны для ввода, то формулы я вставлял через макрос (не уверен, что это правильно)
Надеюсь объяснил нормально)
Файл примера вложил, темно-синим помечены ячейки, по которым ведётся поиск
Удалил с психа формулы, которые прописывал, сейчас восстанавливаю и вставлю)
P.S. создал отдельную тему по рекомендации модератора
Здравствуйте! 2 недели пытаюсь решить задачу, которую сам перед собой поставил, но так до сих пор и не получилось.
Есть документ, который состоит из двух листов, на одном листе поля ввода данных (титульный лист) на втором - некая база данных о клиентах.
Задача стоит следующая, сделать так, чтобы при вводе в определенные ячейки на выбор (их три: телефон, гос. номер, паспорт) excel искал это значение в "базе" и если бы находил, то во все остальные ячейки подставлял данные от нужного клиента
В целом, у меня получилось сделать это по одному значению, если добавить второе или третье, то появляется ошибка зациклинности.
Логику я понимаю, а вот с формулой какая-то беда...
Нужно найти (я делал через индекс и поискпоз) номер телефона и если нашел, то вставить в нужные ячейки нужные данные, если телефон не нашел, то искать по паспорту, если всё норм, то вставить, если нет, то искать по гос. номеру, если норм, то вставить, если нет, то оставить пустым.
Но так как все ячейки должны быть свободны для ввода, то формулы я вставлял через макрос (не уверен, что это правильно)
Надеюсь объяснил нормально)
Файл примера вложил, темно-синим помечены ячейки, по которым ведётся поиск
Удалил с психа формулы, которые прописывал, сейчас восстанавливаю и вставлю)
Ваш вопрос нельзя решить формулой. В ячейке может быть или вводимое вручную значение, или формула. Одновременно быть не может. Поэтому только макрос. В модуль листа "Титул" (правой мышой на ярлык листа - Исходный текст)
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub ad_ = Target.Address(0, 0) With Sheets("База") r0_ = 2 c0_ = 1 r1_ = .Cells(.Rows.Count, c0_).End(3).Row c1_ = 14 nr_ = r1_ - r0_ + 1 nc_ = c1_ - c0_ + 1 ar = .Cells(r0_, c0_).Resize(nr_, nc_) End With Select Case ad_ Case "B4" c_ = 4 Case "B6" c_ = 6 Case "D7" c_ = 14 Case Else Exit Sub End Select ReDim ar1(1 To 7, 1 To 1) ReDim ar2(1 To 7, 1 To 1) Set slov = CreateObject("Scripting.Dictionary") With slov For i = 1 To nr_ slov.Item(ar(i, c_)) = i Next i If .Exists(Target.Value) Then str_ = .Item(Target.Value) For j = 1 To 7 ar1(j, 1) = ar(str_, j) ar2(j, 1) = ar(str_, j + 7) Next j Else Application.EnableEvents = 0 Range("B1").Resize(7).ClearContents Range("D1").Resize(7).ClearContents Application.EnableEvents = 1 Exit Sub End If End With Application.EnableEvents = 0 Range("B1").Resize(7) = ar1 Range("D1").Resize(7) = ar2 Application.EnableEvents = 1 End Sub
Ваш вопрос нельзя решить формулой. В ячейке может быть или вводимое вручную значение, или формула. Одновременно быть не может. Поэтому только макрос. В модуль листа "Титул" (правой мышой на ярлык листа - Исходный текст)
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub ad_ = Target.Address(0, 0) With Sheets("База") r0_ = 2 c0_ = 1 r1_ = .Cells(.Rows.Count, c0_).End(3).Row c1_ = 14 nr_ = r1_ - r0_ + 1 nc_ = c1_ - c0_ + 1 ar = .Cells(r0_, c0_).Resize(nr_, nc_) End With Select Case ad_ Case "B4" c_ = 4 Case "B6" c_ = 6 Case "D7" c_ = 14 Case Else Exit Sub End Select ReDim ar1(1 To 7, 1 To 1) ReDim ar2(1 To 7, 1 To 1) Set slov = CreateObject("Scripting.Dictionary") With slov For i = 1 To nr_ slov.Item(ar(i, c_)) = i Next i If .Exists(Target.Value) Then str_ = .Item(Target.Value) For j = 1 To 7 ar1(j, 1) = ar(str_, j) ar2(j, 1) = ar(str_, j + 7) Next j Else Application.EnableEvents = 0 Range("B1").Resize(7).ClearContents Range("D1").Resize(7).ClearContents Application.EnableEvents = 1 Exit Sub End If End With Application.EnableEvents = 0 Range("B1").Resize(7) = ar1 Range("D1").Resize(7) = ar2 Application.EnableEvents = 1 End Sub
В ячейке может быть или вводимое вручную значение, или формула.
Это понятно, поэтому я тоже делал через макрос, но с ними я дружу крайне плохо)) Спасибо Вам за подсказку, буду разбирать этот макрос и подстраивать под себя, надеюсь пойму что куда)
А возможно ли в тех ячейках, по которым происходит поиск (телефон, гос. номер, паспорт), сделать возможность при всём этом и написать то, чего нет в базе. Сейчас если написать имя, фамилию, отчество, а затем вписать номер, которого нет в базе, то всё стирается ((
В ячейке может быть или вводимое вручную значение, или формула.
Это понятно, поэтому я тоже делал через макрос, но с ними я дружу крайне плохо)) Спасибо Вам за подсказку, буду разбирать этот макрос и подстраивать под себя, надеюсь пойму что куда)
А возможно ли в тех ячейках, по которым происходит поиск (телефон, гос. номер, паспорт), сделать возможность при всём этом и написать то, чего нет в базе. Сейчас если написать имя, фамилию, отчество, а затем вписать номер, которого нет в базе, то всё стирается ((Narahon
Излишнее цитирование удалено администрацией - это нарушение п.5j Правил форума
Премного благодарен Вам за решение моей проблемки! Буду ковыряться, теперь нужно всё это подставить под мой оригинальный файл, надеюсь, что разберусь с этим)
Да и вообще, нужно мне браться за изучение макросов)
Излишнее цитирование удалено администрацией - это нарушение п.5j Правил форума
Премного благодарен Вам за решение моей проблемки! Буду ковыряться, теперь нужно всё это подставить под мой оригинальный файл, надеюсь, что разберусь с этим)
Да и вообще, нужно мне браться за изучение макросов)Narahon
Логику макроса я частично понял, но как переделать его полностью под свой формат не понял совершенно((
Проблема в том, что в базе данные выглядят неким другим образом, нежели в титульном листе, например, в базе серия и номер паспорта прописаны в одной строке, через пробел, а в титульном серия на одной строке, номер на другой, телефон в базе прописан с пробелами, а в титульном без них, это сделано для того, чтобы было удобно все эти данные выгружать на договор. Как сделать это формулами я понимаю, а вот как макросом это сделать, к сожалению нет((
Высылаю файл оригинала (урезанный немного), если поможете, буду бесконечно благодарен! Внутри макрос немного переделанный мной, было бы интересно узнать ошибки, которые я совершил)
Логику макроса я частично понял, но как переделать его полностью под свой формат не понял совершенно((
Проблема в том, что в базе данные выглядят неким другим образом, нежели в титульном листе, например, в базе серия и номер паспорта прописаны в одной строке, через пробел, а в титульном серия на одной строке, номер на другой, телефон в базе прописан с пробелами, а в титульном без них, это сделано для того, чтобы было удобно все эти данные выгружать на договор. Как сделать это формулами я понимаю, а вот как макросом это сделать, к сожалению нет((
Высылаю файл оригинала (урезанный немного), если поможете, буду бесконечно благодарен! Внутри макрос немного переделанный мной, было бы интересно узнать ошибки, которые я совершил)Narahon
Уважаемый _Boroda_, решил я всё таки переделать свою базу под формат этого макроса, всё работает отлично, но появился маленький вопрос, можно ли сделать поиск по паспорту, если они разделены на 2 строчки (серия и номер) или это так же меняет всю логику формулу и будет легче их объединить?
Заранее спасибо!)
Уважаемый _Boroda_, решил я всё таки переделать свою базу под формат этого макроса, всё работает отлично, но появился маленький вопрос, можно ли сделать поиск по паспорту, если они разделены на 2 строчки (серия и номер) или это так же меняет всю логику формулу и будет легче их объединить?
Забыл я. Кладите новый файл. И вопрос - на какую ячейку вешать запуск макроса - на серию или на номер? Например, если Вы заводите серию, то ничего не происходит, если Вы заводите номер, то проверяем серию и, если там не пусто, то запускаем поиск.
Забыл я. Кладите новый файл. И вопрос - на какую ячейку вешать запуск макроса - на серию или на номер? Например, если Вы заводите серию, то ничего не происходит, если Вы заводите номер, то проверяем серию и, если там не пусто, то запускаем поиск._Boroda_