Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Array formula
 
#1
13.06.2012 10:43:44
Есть лист DATA, в столбцах A2:A100 есть список:    
1#2012.03.01-2012.03.21  
2#2012.03.22-2012.04.11  
3#2012.04.12-2012.05.02  
4#2012.05.03-2012.05.23  
5#2012.05.24-2012.06.12  
который соответствует названиям листов в этой же книге и постоянно дополняется.  
В этих листах в столбцах B:B есть спикок продавцов:  
Продавец1  
Продавец1  
Продавец3  
Продавец1  
Продавец4  
и т.д в разброс  
 
на листе DATA в столбце B2:B100 список продавцов:  
Продавец1  
Продавец2  
Продавец3  
Продавец4  
Продавец5  
на листе DATA в ячейке a1 выбор продавцев из списка,  
 
Нужно при выборе продавца в ячейке a1 на листе DATA в столбце c2:c100 вывести список листов на которых присутствует выбранный продавец:    
Пример:  
на листе DATA в ячейке a1 выбран продавец1  
ищем на всех листах продавец1 в столбцах B:B  
и выводим на листе DATA в столбце c2:c100  
 
           A                    B            C  
                                         Продавец1  
1#2012.03.01-2012.03.21      Продавец1    2#2012.03.22-2012.04.11    
2#2012.03.22-2012.04.11      Продавец2    3#2012.04.12-2012.05.02    
3#2012.04.12-2012.05.02      Продавец3    5#2012.05.24-2012.06.12    
4#2012.05.03-2012.05.23      Продавец4        
5#2012.05.24-2012.06.12      Продавец5        
 
на листе DATA в ячейке a1 выбран продавец3  
ищем на всех листах продавец1 в столбцах B:B  
и выводим на листе DATA в столбце c2:c100  
 
           A                    B            C  
                                         Продавец3  
1#2012.03.01-2012.03.21      Продавец1    1#2012.03.01-2012.03.21    
2#2012.03.22-2012.04.11      Продавец2    3#2012.04.12-2012.05.02    
3#2012.04.12-2012.05.02      Продавец3    4#2012.05.03-2012.05.23    
4#2012.05.03-2012.05.23      Продавец4    5#2012.05.24-2012.06.12    
5#2012.05.24-2012.06.12      Продавец5
 
 
 
#2
13.06.2012 11:03:09
Раз уж столько понаписали - сделайте малость и покажите всё это в файле.  
Есть конечно надежда, что кто-то из помогающих станет сам сочинять такой файл по Вашему описанию.    
Но мизерная, и глупо. И сочинять, и надеятся. Ибо % успеха минимален.  
P.S. Я решать формульные вопросы не берусь.
 
 
 
#3
13.06.2012 11:18:04
Нужно что-то типо  
 
function CountH(arrayNr as string)  
 
Dim SaveToResultArray (20)  
Dim SheetsArray () as long  
 
For c = 1 to count(A:A)  
   SheetsArray©=cells(c+1,1)    
Next c  
 
Redim SheetsArray()  
 
For i = 1 to count(A:A)  
  If IsError(worksheetFunction.Vlookup($A$1;SheetsArray(i)&"!B:B");1;False)) then  
     Else  
       n=n+1  
       SaveToResultArray(n)=SheetsArray(i)  
  End If  
Next i  
 
CountH=SaveToResultArray(arrayNr)  
 
End function
 
 
 
#4
13.06.2012 11:25:33
{quote}{login=Hugo}{date=13.06.2012 11:03}{thema=}{post}Раз уж столько понаписали - сделайте малость и покажите всё это в файле.  
{/post}{/quote}
 
 
 
#5
13.06.2012 11:27:02
Возможно... Хотя код сырой, перепутанный, нерабочий.  
Но без файла голову ломать я лично не буду.  
Да и это не формула массива.
 
 
 
#6
13.06.2012 11:44:00
Прикрепил
 
 
 
#7
13.06.2012 13:29:33
Наладил 2 варианта.  
1. ваш, но добавил параметры "исходное значение" и "список листов"  
2. мой - "массивная" формула, т.е. вводится сразу в диапазон как формула массива.  
Разница - у Вас просчитывается столько раз, во сколько ячеек она введена, у меня всего один раз на весь диапазон.  
 
Ну и изменил диапазон списка листов (в один столбик, хотя последний лист пустой и не используется) и дал ему имя "listi".  
На листе в ячейки ввожу  
=CountH(1,$A$2,listi)  
(вместо 1 можно поставить СТРОКА()-n)  
или  
=CountHall(A2,listi)  
(тут можно $ не ставить, т.к. протягивать не нужно).  
 
 
Function CountH(Num As String, V1 As String, larr As Range)  
   Application.Volatile  
   Dim SaveToResultArray(20)  
   Dim V2 As Range  
   CA = larr.Rows.Count  
 
   For i = 1 To CA  
       Set V2 = Sheets(larr(i).Value).Range("B:B")  
       If IsError(Application.VLookup(V1, V2, 1, False)) Then  
       Else  
           n = n + 1  
           SaveToResultArray(n) = larr(i).Value  
       End If  
   Next i  
   CountH = SaveToResultArray(Num)  
End Function  
 
 
Function CountHAll(V1 As String, larr As Range)  
   Application.Volatile  
   Dim SaveToResultArray(1 To 20, 1 To 1)  
   Dim V2 As Range  
   CA = larr.Rows.Count  
   For i = 1 To CA  
       Set V2 = Sheets(larr(i).Value).Range("B:B")  
       If IsError(Application.VLookup(V1, V2, 1, False)) Then  
       Else  
           n = n + 1  
           SaveToResultArray(n, 1) = larr(i).Value  
       End If  
   Next i  
   CountHAll = SaveToResultArray  
End Function  
 
 
Вообще как-то не понятно, зачем в B6:B20 этот список customer?  
 
P.S. On-line консультант на полставки за 300Ls не нужен? :)
 
 
 
#8
13.06.2012 14:02:37
Большое спасибо, посмотрю  
Я вроде как решил с помощью офсетов )) Работает.  
Вообще как-то не понятно, зачем в B6:B20 этот список customer? Это для наглядности отчета
 
 
 
Страницы: 1
Читают тему
Loading...