Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Быстрый поиск по диапазону и подсчет значений / 5 сообщений из 5, страница 1 из 1
30.09.2011, 15:08
    #37463010
Диклевич Александр
Диклевич Александр
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый поиск по диапазону и подсчет значений
Помогите разобраться со следующим:
Есть книга Excel с двумя листами 1 - для отчета, другой - это прямоугольная таблица (вроде БД).
имена столбцов - это имена переменных, для которых по некоему критерию надо подсчитать количество значений в соответсвующем столбце. однако критерий может быть множественный и обычно это выглядит как перечисление значений через запятую.

К примеру, есть переменная "arrival_day", и я хочу для соотв. столбца подсчитать количество значений по условию "7,9,10,12" (условие может быть произвольным и не только числовым). Т.е. если листе с данными я должен подсчитать кол-во строк в столбце "arrival_day" если значение переменной равно одному из значений в условии.

Переведя на SQL это было бы так, к примеру
Код: plaintext
SELECT COUNT(t.arrival_day) FROM Table1 t WHERE t.arrival_day IN ( 7 , 9 , 10 , 12 )

Я написал UDF которая так и поступает, однако использование Excel в качестве БД и выполнение запросов к нему чревато неадекватным поведением (зависанием процесса в памяти, невозможностью сохранить файл и т.п.), поэтому SQL хотелось бы избежать.

Как вариант - перебор всех строк столбца на вхождение в условие работает нормально, но долго, в случае если отчет содержит много строк с условиями, а таблица с БД - соотв. много данных.
Т.е. хотелось бы что нибудь без переборов, SQL и фильтров, чтобы работало быстро....
Код за меня писать не прошу, проблем с этим нет, а прошу подсказать алгоритм (а может Excel уже содержит формулы которые могут помочь, т.к. те что есть ищут по единичным условиям, типа больше меньше равно чему то одному).

Пример прилагаю. Ф-я принимает в качестве вход. параметров: имя переменной, диапазон с условиями, доп. условие (тут проще, т.к. доп. всегда одно), и лог. параметр который показывает или мы считаем вхождения по условию или исключения.
Спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2011, 16:30
    #37463186
R Dmitry
R Dmitry
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый поиск по диапазону и подсчет значений
Диклевич Александр,


если нужна UDF, то лучше сделайте формулу массива,
вот так похоже будет очень быстро

 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
Sub tttt()
Dim e&, i&, j&, a, b, c, d
a = Sheets( 2 ).[a1].CurrentRegion.Value
b = Sheets( 1 ).[c10:c15].Value
On Error Resume Next
ReDim c( 1  To UBound(b),  1  To  1 )
d = c
For j =  1  To UBound(c)
c(j,  1 ) = Split(Replace(b(j,  1 ), " ", ""), ",")
Next
    For i =  2  To UBound(a)
        For j =  1  To UBound(b)
            e = Application.Match(CStr(a(i,  2 )), c(j,  1 ),  0 )
            If e >  0  Then d(j,  1 ) = d(j,  1 ) +  1 : e =  0 : Exit For
        Next
    Next
    Range("i10").Resize(UBound(d)).Value = d
End Sub
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2011, 16:57
    #37463246
R Dmitry
R Dmitry
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый поиск по диапазону и подсчет значений
Диклевич Александр,

авторЯ написал UDF которая так и поступает, однако использование Excel в качестве БД и выполнение запросов к нему чревато неадекватным поведением (зависанием процесса в памяти, невозможностью сохранить файл и т.п.), поэтому SQL хотелось бы избежать.



 
как то Вы небрежно, с SQL обошлись. Не заслуживает он этого, все зависит от рук программиста......
ps...... а форум Вы почему то SQL.RU открыли
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2011, 17:38
    #37463336
AndreTM
AndreTM
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый поиск по диапазону и подсчет значений
Диклевич Александр,

Вообще, неплохо бы определиться. Вам нужен именно динамический расчет?
То есть обязательно на ходу и формулами... и при этом вы используете монстроидальные UDF. И при этом отказываетесь от Select-SQL. И при этом оперируете некими "именами полей (столбцов БД)", которые при таком подходе вообще не требуются.

Или ваше "неприятие" SQL произрастает из того, что вы пытались сообразить "летучую" UDF с запросом?

ИМХО (как и R Dmitry), нормально реализовать вашу задачу можно именно (и только) с помощью запросов (ну и некоторого необходимого количества формул листа).

Вы бы "поплотнее" сформулировали задачу. Например, количество полей в DATA будет бОльшим, чем сейчас? Выборки будут осуществляться по бОльшему количеству условий (навскидку - а где год)? Таблица DATA должна быть именно такого формата? И т.д.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2011, 22:08
    #37463653
Hugo121
Hugo121
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстрый поиск по диапазону и подсчет значений
AndreTM, почему же только запрос?
Например такой вариант - по скорости чуть выигрывает у варианта R Dmitry ( 0,4375 vs 0,78125), и отбор по датам есть и легко нарастить.
Я пошёл другим путём - сперва собрал данные в словарь, потом из словаря тянул количество повторов.
Можно (как обычно) количество повторов в параллельный массив складывать - есть свои плюсы, можно больше разных данных собирать.

Код: plaintext
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.
Sub Otbor()
'    Dim tm: tm = Timer
    Dim a, b, arr, i As Long, temp As String, e, mnth

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False

        a = Sheets( 2 ).[a1].CurrentRegion.Value

        With CreateObject("Scripting.Dictionary")
            .CompareMode = vbTextCompare

            For i =  1  To UBound(a)

                temp = Application.Trim(a(i,  1 )) & "|" & Application.Trim(a(i,  2 ))
                If Not .Exists(temp) Then
                    .Add temp, CStr( 1 )
                Else
                    .Item(temp) = .Item(temp) +  1 
                End If
            Next

            b = Sheets( 1 ).[c10:c15].Value
            ReDim c( 1  To UBound(b),  1  To  1 )
            mnth = [f4].Value
            For i =  1  To UBound(b)
                arr = Split(b(i,  1 ), ",")
                For Each e In arr
                    temp = mnth & "|" & Trim(e)
                    If .Exists(temp) Then c(i,  1 ) = c(i,  1 ) + .Item(temp)
                Next
            Next

        End With

        Range("i10").Resize(UBound(c)).Value = c

        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
'    Debug.Print Timer - tm
End Sub
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Быстрый поиск по диапазону и подсчет значений / 5 сообщений из 5, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 312ms
Закрыть
start [/forum/topic.php?fid=61&tablet=1&tid=2176439]:
 0ms
get settings:
 8ms
get forum list:
 10ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 44ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 28ms
get tp. blocked users:
 1ms
others: 205ms
total:  312ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]