Страницы: 1
RSS
VLOOKUP3 на всех листах
 
Добрый день!
Нужна очень помощь в таком вот вопросе.
Имеется несколько листов с одинаковыми таблицами.
С помощью функции VLOOKUP3 я ищу слово в одной таблице, т.к. оно может повторятся и затем беру по этому слову все значения. Это по одному листу, а как сделать так чтобы функция брала все значения этого слова на всех листах, без суммирования?

Подскажите пожалуйста.
 
так ?
 
Могли показать код своей VLOOKUP3, ну и какой результат нужен.
А так и сказать нечего...
 
Синтаксис
=VLOOKUP3(Table; SearchColumnNum; SearchValue; ResultColumnNum)
где
Table - диапазон ячеек, в котором производится поиск и последующая выборка значений
SearchColumnNum - порядковый номер столбца диапазона Table, в котором производится поиск искомого значения
SearchValue - искомое значение, которое ищется в столбце SearchColumnNum диапазона Table
ResultColumnNum
- порядковый номер столбца таблицы Table из которого берется нужное нам значение


Результат должен быть, чтобы по всем таблицам на n-листах я мог вывести значения на один лист по слову Яблоко, при этом не суммируя их, можно и в один столбец, чтобы потом из результатов посчитать среднее значение.
 
Цитата
v.fix86 пишет:
так ?
А можно как-то сделать в один столбец результаты, и другим способом, т.к. я понимаю формула все равно связана с ссылками на лист.
 
Код самой функции я не знаю, нет её у меня, и Вы не показали, да и ладно - это судя по примеру v.fix86 код из платной надстройки.
Я ещё потому спрашивал код и результат - там на листе по 2 яблока, и что именно нужно получить с листа, непонятно. Сумму или первое? Код UDF мог это прояснить.
А вообще можно всё сделать одним кодом, только его нужно изменить - добавить ещё один параметр "номера или имена листов", определиться нужна сумма по листу или первое значение, ну и сразу можно считать среднее.

А вообще UDF VLOOKUP3() может делать что угодно - название ни к чему не обязывет :)
 
Код вот такой:

Function VLOOKUP3(Table As Variant, SearchColumnNum As Integer, _ SearchValue As Variant, ResultColumnNum As Integer) 'улучшенная функция ВПР вводится как формула массива 'ищет указанной значение в указанном столбце и выводит все соответствия из указанного столбца
Код
Dim i&, j& 
Dim a If 
IsObject(Table) Then a = Table.Value Else a = Table 
ReDim Out(1 To UBound(a), 1 To 1) As Variant 
For i = 1 To UBound(a) If a(i, SearchColumnNum) = SearchValue Then j = j + 1 Out(j, 1) = a(i, ResultColumnNum) 
End If 
Next i VLOOKUP3 = Out 
End Function



Просто прописивая все это через мастер функций не понимаю как все это сделать с листами, не могу понять.
 
А, ну понятно - выводит все совпадения в столбец.
Тогда выводите в следующий столбец данные следующего листа, и так сколько их есть. Каждый экземпляр функции пусть обрабатывает один лист.
А далее считайте среднее.
 
А если например 100 листов, не буду же я все это в ручную проставлять в формулу. Вот я и решил открыть тему, может кто подскажет, как попроще все это сделать.
 
Например такая модификация:

Код
Function VLOOKUP4(Table As Variant, SearchColumnNum As Integer, _
                  SearchValue As Variant, ResultColumnNum As Integer, sheetsdiap As String)    'считаем среднее по листам указанным по крайним индексам n|m (от n до m)
    Dim i&, j&, shind&
    Dim a
    For shind = Split(sheetsdiap, "|")(0) To Split(sheetsdiap, "|")(1)
    
     If IsObject(Sheets(shind).Range(Table.Address)) Then a = Sheets(shind).Range(Table.Address).Value Else a = Sheets(shind).Range(Table.Address)
        For i = 1 To UBound(a)
            If a(i, SearchColumnNum) = SearchValue Then
                j = j + 1
                VLOOKUP4 = VLOOKUP4 + a(i, ResultColumnNum)
            End If
        Next i
    Next
    VLOOKUP4 = VLOOKUP4 / j
End Function
 
На листе

Код
=VLOOKUP4(C4:H6,1,D11,6,"1|2")
 
Изменено: Hugo - 10.02.2014 15:35:01
 
если у Вас 100 листов, то скорее всего у них есть правило, по которым они называются, например:
1) 1,2,3,4,5....100;
2) Урожай-Январь10,  Урожай-Февраль10 ....  Урожай-Январь14

и много еще чего можно придумать.

ячейка может все эти названия показывать как результат вычислений. и тогда, если взять тот пример, который я прикладывал выше, вместо названий "Лист1" и "Лист2" можно формулой вывести названия листов.
Правда этот вариант будет работать, если существует какое то правило присвоения имени для листа
 
Мой вариант работает со всеми листами от|до, например 3|100
 
Hugo, добрый день.

Могли  бы помочь дописать эту функцию добавив еще макрос . Не знаю как его впихнуть в vlookup 3 или 4 . Что бы все найденные значения вносил в одну ячейку с разделителем пробел или знаком /символом .
Код
Sub MergeToOneCell()
   Const sDELIM As String = " "     'символ-разделитель
   Dim rCell As Range
   Dim sMergeStr As String
   If TypeName(Selection) <> "Range" Then Exit Sub   &#39;если выделены не ячейки - выходим
   With Selection
 For Each rCell In .Cells
sMergeStr = sMergeStr & sDELIM & rCell.Text  &#39;собираем текст из ячеек
           rCell = "" &#39;(добавляем строку)
 Next rCell
 Application.DisplayAlerts = False   &#39;отключаем стандартное предупреждение о потере текста
       &#39;.Merge Across:=False       &#39;объединяем ячейки (комментим эту строку)
 Application.DisplayAlerts = True
 .Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM))    &#39;добавляем к объед.ячейке суммарный текст
   End With
End Sub
 
Цитата
SSL написал:
все найденные значения вносил в одну ячейку с разделителем пробел или знаком /символом
- для этого написана VLOOKUPCOUPLE(), см. в копилке:
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=2&TID=10&TITLE_SEO=10
Изменено: Hugo - 17.01.2020 19:47:23
Страницы: 1
Наверх