ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Быстрый поиск по диапазону и подсчет значений
5 сообщений из 5, страница 1 из 1
Быстрый поиск по диапазону и подсчет значений
    #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, 15:08
| Ответить | Цитировать | Написать  
Быстрый поиск по диапазону и подсчет значений
    #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:30
| Ответить | Цитировать | Написать  
Быстрый поиск по диапазону и подсчет значений
    #37463246
R Dmitry
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
R Dmitry
Участник
Диклевич Александр,

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



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

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

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

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

Вы бы "поплотнее" сформулировали задачу. Например, количество полей в DATA будет бОльшим, чем сейчас? Выборки будут осуществляться по бОльшему количеству условий (навскидку - а где год)? Таблица DATA должна быть именно такого формата? И т.д.
...
Рейтинг: 0 / 0
30.09.2011, 17:38
| Ответить | Цитировать | Написать  
Быстрый поиск по диапазону и подсчет значений
    #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
30.09.2011, 22:08
| Ответить | Цитировать | Написать  
5 сообщений из 5, страница 1 из 1
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Быстрый поиск по диапазону и подсчет значений
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=61&gotonew=1&tid=2176439]:
 0ms
get settings:
 9ms
get forum list:
 19ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 143ms
get topic data:
 10ms
get first new msg:
 4ms
get forum data:
 2ms
get page messages:
 33ms
get tp. blocked users:
 1ms
others: 236ms
total:  463ms
созд. / загр.: 463ms
x
x
Закрыть


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