Как можно описать формулу "СУКММЕСЛИМН" кодом?

@Иван_777 · Регистрация: 16.05.2018

Студворк — интернет-сервис помощи студентам

Всем привет !

Как можно описать формулу "СУКММЕСЛИМН" с помощью кода?? Прошу хотя бы идею.

В данной выгрузке все платежи за период, в двух столбцах указаны соответствено сумма и статья, сводная таблице (Листт1) показывает сумму всех платежей за период по каждой статье бюджета отдельно.
На листе "Форма" отображается сводка бюджета за период по статьям, соответствующая данным из сводной таблицы. Сейчас колонка "Данные по выгрузке из LOTUS_2" посчитана с помощью формулы "СУММЕСЛИМН".
Задача:
Необходимо написать код, кторый повторял бы действия формулы "СУММЕСЛИМН"

@Hugo121 · 17.06.2018, 22:00

Идея - изучите словарь (scripting.dictionary) - это если на винде. В принципе и для Мака можно почитать, ибо есть один универсальный класс, для Маков делающий всё тоже самое, но на коллекциях.

Добавлено через 8 минут
Вот. Выгрузку сделал рядом, чтоб сравнить результат, подправьте как угодно. Ведь нужно хоть что-то сделать самому

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
Sub tt()
    Dim a, i&
 
    With CreateObject("Scripting.Dictionary")
        a = Sheets("Выгрузка").[a1].CurrentRegion.Value
        For i = 2 To UBound(a): .Item(a(i, 1)) = .Item(a(i, 1)) + a(i, 2): Next
        a = Sheets("Форма").[a1].CurrentRegion.Value
        For i = 2 To UBound(a): a(i, 1) = .Item(a(i, 1)): Next
        Sheets("Форма").[c1].Resize(UBound(a), 1) = a
    End With
 
End Sub

@Иван_777 · 18.06.2018, 15:38 **[ТС]**

Огромнейшее спасибо, лучи добра вам!

А теперь можно подробней))
Сложно, для такого нуба, как я, ес не затруднит:

Visual Basic
1
  a = Sheets("Выгрузка").[a1].CurrentRegion.Value         For i = 2 To UBound(a): .Item(a(i, 1)) = .Item(a(i, 1)) + a(i, 2): Next

То что мы выделили область и присвоили области значение а я понял, но шо делает .item не разберу - присваивает индекс значениям??

@Hugo121 · 18.06.2018, 15:45

Сообщение от Иван_777

мы выделили область и присвоили области значение

- наоборот: определили область, взяли её значения в массив.

Сообщение от Иван_777

шо делает .item не разберу - присваивает индекс значениям??

- нет, это значение по ключу в словаре, и оно суммируется.

@Иван_777 · 19.06.2018, 10:25 **[ТС]**

- нет, это значение по ключу в словаре, и оно суммируется.

т.е в этой части, я так понимаю, мы сначала заносим значения в словарь через ключ .Item(a(i,1) и считаем сумму

Visual Basic
1
2
        a = Sheets("Выгрузка").[a1].CurrentRegion.Value
        For i = 2 To UBound(a): .Item(a(i, 1)) = .Item(a(i, 1)) + a(i, 2): Next

а в этой заполняем форму на втором листе, на основании того, что ключи из библиотеки совпадают и мы так просто можем поменять массив на другой ?

Visual Basic
1
2
        a = Sheets("Форма").[a1].CurrentRegion.Value
        For i = 2 To UBound(a): a(i, 1) = .Item(a(i, 1)): Next

@Hugo121 · 19.06.2018, 10:47

Да. Вот так просто.
Вообще я тут несколько расточителен - во втором случае в массив достаточно брать всего один столбец, а не все значения [a1].CurrentRegion - будет меньше затрат памяти, чуть быстрее работа, да и если вдруг этот [a1].CurrentRegion большой, то может на всё вообще памяти не хватить. Но пример не такой.
Т.е. сперва в словаре для одного массива посчитали суммы для каждого ключа, затем для другого массива по этим ключам суммы извлекли, заменив ими сами ключи ради экономии вообще всего (памяти, сложности/букв кода).

@Иван_777 · 19.06.2018, 13:50 **[ТС]**

Да. Вот так просто.

HAKERMAN

...то может на всё вообще памяти не хватить. Но пример не такой.

И это правда, в таблице присутствуют еще столбцы(12 где-то), мб скажите как обратится лучше в данном случае к конкретному столбцу ??

@Hugo121 · 19.06.2018, 13:56

Можно

Visual Basic
1
a = Sheets("Форма").[a1].CurrentRegion.columns(1).Value

но вообще нужно видеть файл - может этот куррентрегион не всегда будет захватывать все данные. Вы знаете что такое CurrentRegion?

@Иван_777 · 19.06.2018, 16:30 **[ТС]**

Вы знаете что такое CurrentRegion?

Ну как Ctrl+* насколько я понял.

Visual Basic
1
 Sheets("Форма").[c1].Resize(UBound(a), 1) = a

А вот это ресайз, он что делает ? Простоя не совсем понял как данные в калонке C оказались.

@Hugo121 · 19.06.2018, 19:26

Про CurrentRegion так и есть. Т.е. понятно, что дыр быть не должно.
Ресайз делает "переразметку" диапазона из указанной ячейки на высоту массива a, и на указанную ширину (т.е.1 столбец), куда и выгружает этот массив (ну скока поместится в этот диапазон).

@Иван_777 · 21.06.2018, 13:03 **[ТС]**

Про CurrentRegion так и есть. Т.е. понятно, что дыр быть не должно.
Ресайз делает "переразметку" диапазона из указанной ячейки на высоту массива a, и на указанную ширину (т.е.1 столбец), куда и выгружает этот массив (ну скока поместится в этот диапазон).

Спасибо! Главное, что я понял что можно в словарь через ключ данные скопировать и достать их потом, таким простым способом.

Ес не сложно, можно еще спросить. Сейчас файл один смотрю - по аналогии надо сделать, код должен забирать на лист инфу из другого файла. Вот это начало, оно что делает. Устанавливает текущую дату ?

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub ImportCred()
    Dim i As Long
    Dim r As Range
    Dim reportDates As String
    
    Set r = ActiveCell
    
    fPath = "V:\_spool1.tmp"
    
    Open fPath For Input As #1
    Line Input #1, reportDates
    Close #1
    
    Cells(1, 2).ClearContents
    Cells(1, 2) = CDate(reportDates)

@Hugo121 · 21.06.2018, 22:06

Берёт данные из первой строки файла "V:\_spool1.tmp", преобразовывает что взял в дату, пишет это в ячейку, зачем-то сперва её очищая.

@Иван_777 · 17.07.2018, 16:42 **[ТС]**

Приветствую вас !!

не подскажите еще момент, что делать, если нет возможности заполнить столбец масива a(i,1), а есть возможность заполнить только a(i,7),

Sheets("Форма").[c1].Resize(UBound(a), 1) = a

на этом этапе, как вставить именно 7й столбец в ячейку [C1] ??

@Hugo121 · 18.07.2018, 20:38

Переложить в цикле в другой массив (на один столбец), его выгрузить.
Ну или в цикле сразу заполнять ячейки, если их до тысячи.
Ну или использовать CopyMemory, но я не в теме, не использую, ибо там есть ограничения, что на практике трудноприменимо. Но примеры в сети есть.

@Иван_777 · 20.07.2018, 16:24 **[ТС]**

Спасибо!

Словарем вашим с массивами сейчас постоянно пользуюсь, очень помогает

@Hugo121 · 20.07.2018, 20:11

Ну тогда вот ещё на будущее, пригодится (написано почти 6 лет назад, периодически использую):

Visual Basic
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
Sub PereborFailov() 'коллекция в словаре
    Dim a, i&, t$, Dic As Object
    Dim el, col
    
    a = Range("C3", Cells(Rows.Count, "A").End(xlUp)).Value
    Set Dic = CreateObject("Scripting.Dictionary")
    With Dic
        .CompareMode = 1
        For i = 1 To UBound(a)
            t = a(i, 1)
            If Not .exists(t) Then .Add t, New Collection
            .Item(t).Add a(i, 2) & "|" & a(i, 3) & "|" & i
        Next
    End With
    
    For Each el In Dic.keys
        Debug.Print "Открываем файл " & el
        For Each col In Dic.Item(el)
            Debug.Print "Ищем данные " & col
        Next
        Debug.Print "Закрываем файл " & el
    Next
 
End Sub
 
Sub PereborFailov2() ' словарь в словаре
    Dim a, i&, t$, Dic As Object, Dic2 As Object
    Dim el, col
    
    a = Range("C3", Cells(Rows.Count, "A").End(xlUp)).Value
    Set Dic = CreateObject("Scripting.Dictionary")
    With Dic
        .CompareMode = 1
        For i = 1 To UBound(a)
            t = a(i, 1)
            If Not .exists(t) Then .Add t, CreateObject("Scripting.Dictionary")
            .Item(t).Item(a(i, 2) & "|" & a(i, 3) & "|" & i) = 0&
            
        Next
    End With
    
    For Each el In Dic.keys
        Debug.Print "Открываем файл " & el
        Set Dic2 = Dic.Item(el)
        For Each col In Dic2.keys
            Debug.Print "Ищем данные " & col '& "|" & Dic2.Item(col)
        Next
        Debug.Print "Закрываем файл " & el
    Next
 
End Sub

@Иван_777 · 26.07.2018, 10:10 **[ТС]**

Огромное спасибо! Освобожусь немного, обязательно разберу.

Не могли бы пока еще подсказать на счет ошибки Type mismatch (Error 13), сейчас, почему-то, в каждом месте вылезает раньше не было. Не могу понять. Даже, когда просто массив хочу задать. Ячейки текстового формата, но там цифры.

Visual Basic
1
2
3
4
Sub PP()
Dim a&
a = Sheets(Ëèñò5).Cells(15, 6).CurrentRegion.Value
End Sub

@Hugo121 · 26.07.2018, 10:13

Потому что переменная типа Long не может быть массивом.

@Иван_777 · 26.07.2018, 10:34 **[ТС]**

Но вот сейчас:

Visual Basic
1
2
3
4
Sub PP()
Dim a
 
a = Sheets(Ëèñò5).Cells(15, 3).CurrentRegion.Value

У вас же так, вроде, было и все равно Type mismatch (Error 13).

@Hugo121 · 26.07.2018, 10:37

Ошибка будет только в том случае, если нет листа, имя которого скрывается в текстовой переменной Г‹ГЁГ±ГІ5

Новые блоги и статьи Все статьи Все блоги /
Модель заражения группы наркоманов alhaos 17.04.2026 Условия задачи сформулированы тут Суть: - Группа наркоманов из 10 человек. - Только один инфицирован ВИЧ. - Колются одной иглой. - Колются раз в день. - Колются последовательно через. . .	Мысли в слух. Про "навсегда". kumehtar 16.04.2026 Подумалось тут, что наверное очень глупо использовать во всяких своих установках понятие "навсегда". Это очень сильное понятие, и я только начинаю понимать край его смысла, не смотря на то что давно. . .	My Business CRM MaGz GoLd 16.04.2026 Всем привет, недавно возникла потребность создать CRM, для личных нужд. Собственно программа предоставляет из себя базу данных клиентов, в которой можно фиксировать звонки, стадии сделки, а также. . .	Знаешь почему 90% людей редко бывают счастливыми? kumehtar 14.04.2026 Потому что они ждут. Ждут выходных, ждут отпуска, ждут удачного момента. . . а удачный момент так и не приходит.
Фиксация колонок в отчете СКД Maks 14.04.2026 Фиксация колонок в СКД отчета типа Таблица. Задача: зафиксировать три левых колонки в отчете. Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка) / / . . .	Настройки VS Code Loafer 13.04.2026 { "cmake. configureOnOpen": false, "diffEditor. ignoreTrimWhitespace": true, "editor. guides. bracketPairs": "active", "extensions. ignoreRecommendations": true, . . .	Оптимизация кода на разграничение прав доступа к элементам формы Maks 13.04.2026 Алгоритм из решения ниже реализован на нетиповом документе, разработанного в конфигурации КА2. Задачи, как таковой, поставлено не было, проделанное ниже исключительно моя инициатива. Было так:. . .	Контроль заполнения и очистка дат в зависимости от значения перечислений Maks 12.04.2026 Алгоритм из решения ниже реализован на примере нетипового документа "ПланированиеПерсонала", разработанного в конфигурации КА2. Задача: реализовать контроль корректности заполнения дат назначения. . .

@Hugo121 6998 / 2896 / 555 Регистрация: 19.10.2012 Сообщений: 8,804
	19.06.2018, 10:47
	Да. Вот так просто. Вообще я тут несколько расточителен - во втором случае в массив достаточно брать всего один столбец, а не все значения [a1].CurrentRegion - будет меньше затрат памяти, чуть быстрее работа, да и если вдруг этот [a1].CurrentRegion большой, то может на всё вообще памяти не хватить. Но пример не такой. Т.е. сперва в словаре для одного массива посчитали суммы для каждого ключа, затем для другого массива по этим ключам суммы извлекли, заменив ими сами ключи ради экономии вообще всего (памяти, сложности/букв кода). 0

@Hugo121 6998 / 2896 / 555 Регистрация: 19.10.2012 Сообщений: 8,804
	19.06.2018, 19:26
	Про CurrentRegion так и есть. Т.е. понятно, что дыр быть не должно. Ресайз делает "переразметку" диапазона из указанной ячейки на высоту массива a, и на указанную ширину (т.е.1 столбец), куда и выгружает этот массив (ну скока поместится в этот диапазон). 0

@Hugo121 6998 / 2896 / 555 Регистрация: 19.10.2012 Сообщений: 8,804
	21.06.2018, 22:06
	Берёт данные из первой строки файла "V:\_spool1.tmp", преобразовывает что взял в дату, пишет это в ячейку, зачем-то сперва её очищая. 0

@Hugo121 6998 / 2896 / 555 Регистрация: 19.10.2012 Сообщений: 8,804
	18.07.2018, 20:38
	Переложить в цикле в другой массив (на один столбец), его выгрузить. Ну или в цикле сразу заполнять ячейки, если их до тысячи. Ну или использовать CopyMemory, но я не в теме, не использую, ибо там есть ограничения, что на практике трудноприменимо. Но примеры в сети есть. 0

@Иван_777 0 / 0 / 0 Регистрация: 16.05.2018 Сообщений: 105
	20.07.2018, 16:24 [ТС]
	Спасибо! Словарем вашим с массивами сейчас постоянно пользуюсь, очень помогает 0

@Hugo121 6998 / 2896 / 555 Регистрация: 19.10.2012 Сообщений: 8,804
	26.07.2018, 10:13
	Потому что переменная типа Long не может быть массивом. 0

@Hugo121 6998 / 2896 / 555 Регистрация: 19.10.2012 Сообщений: 8,804
	26.07.2018, 10:37
	Ошибка будет только в том случае, если нет листа, имя которого скрывается в текстовой переменной Г‹ГЁГ±ГІ5 0