Microsoft Excel FAQ (часть 2) - [25] :: Программы :: Компьютерный форум Ru.Board

init0
2 способа.

1. С формулами. Плюс - не нужно использовать VBA. Минусы - если наименование не найдено или не введено, выдаст ошибку; при вводе новых строк, нужно также копировать формулы.

2. Через VBA. Плюсы и минусы противоположные формулам. А также более гибкая обработка ошибок; макрос запускается при любом изменении на листе расчетов.

Какой вариант выбрать - решать тебе.
Итак,

1. В ячейку B2 листа ЛистКалькуляция вводишь формулу:

Код:

=ИНДЕКС(ЛистСортамент!B$2:B$200;ПОИСКПОЗ(ЛистКалькуляция!$A2;ЛистСортамент!$A$2:$A$200;0);1)

Обрати внимание на расстановку $, это важно.
Копируешь формулу в ячейки C2, F2, G2. В ячейках F2 и G2 надо будет формулу подредактировать, заменив F на Д и G на E.
Затем копируешь (или Ctrl-D) всю строку с формулами вниз. Если $ расставлены правильно, ссылки должны измениться со строкой.
Если ячейка в столбце А пустая или это наименование не найдено, в ячейках с формулами будет ошибка, что-то типа НЕТ ДАННЫХ. Можно, конечно, поместить формулы в ЕСЛИ(ЕНД(...);...;""), но это дополнительное усложнение.

2. Жмешь Alt-F11 для перехода в редактор VBA. В левой панели находишь свою книгу и лист ЛистКалькуляция. Правым кликом открываешь страницы с Кодом. Туда вводишь:

Код:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Target.Column = 1 Then
Set r = Worksheets("ЛистСортамент").Range("A2:A29").Find(Target.Value, LookIn:=xlValues, LookAt:=xlWhole)
If r Is Nothing Then
Cells(Target.Row, 2).Value = ""
Cells(Target.Row, 3).Value = ""
Cells(Target.Row, 6).Value = ""
Cells(Target.Row, 7).Value = ""
Exit Sub
End If
Cells(Target.Row, 2).Value = Worksheets("ЛистСортамент").Cells(r.Row, 2).Value
Cells(Target.Row, 3).Value = Worksheets("ЛистСортамент").Cells(r.Row, 3).Value
Cells(Target.Row, 6).Value = Worksheets("ЛистСортамент").Cells(r.Row, 4).Value
Cells(Target.Row, 7).Value = Worksheets("ЛистСортамент").Cells(r.Row, 5).Value
End If
End Sub

Если в листе ЛистСортамент есть несколько строк с одинаковым наименованием, в обоих вариантах находится первая строка. Наименование должно быть введено полностью.

Чтобы в листе ЛистКалькуляция можно было выбрать наименование из списка. Можно было бы сделать через Данные - Проверка - Список. Но область списка не может быть из другого листа. Потому только через макрос (вставляется в новый модуль):

Код:

Sub FillList()
Dim frm As String
Dim nr As Long
Dim c As Range
frm = ""
With Sheets("ЛистСортамент")
nr = .UsedRange.Rows.Count
For Each c In Range(.Cells(2, 1), .Cells(nr, 1))
frm = frm & "," & c.Value
Next
End With
With Selection.Validation
.Delete
.Add Type:=xlValidateList, Formula1:=frm
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = False
End With
End Sub

Макрос можно повесить на кнопку (ячейки, для которых нужен список должны быть предварительно выделены) или (с небольшими изменениями) на изменение листа ЛистСортамент. При добавлении строк в ЛистКалькуляция макрос перезапускать не обязательно, достаточно скопировать формат предыдущих ячеек.

Вроде все.

Модерирует : gyra, Maz
Widok (17-05-2007 19:48): лимит страниц. продолжаем здесь	Версия для печати • Подписаться • Добавить в закладки
Страницы: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101