Суммирование с учетом границ ячейки

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по VBA » Суммирование с учетом границ ячейки (Макросы/Sub)

Chikitonik

Дата: Четверг, 16.11.2017, 16:21 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 13

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Добрый день!
Прошу подсказать решение задачи..
Есть таблица с данными по разным контрагентам, контрагенты между собой отделены жирным подчеркиванием. Никак не могу сообразить, как сделать суммирование ячеек между этими строками (кол-во строк разное, более того, строки могут добавляться/удаляться)

На данный момент знаний хватило на то, чтобы написать функцию, которая выводит 1 если есть жирное подчеркивание и 0 если его нет

В идеале конечно хотелось бы написать формулу, чтобы работала при обновлении данных, макросом производить подсуммирование было бы не так удобно.
Буду рад любой подсказке.

К сообщению приложен файл: 2795972.xlsm (13.9 Kb)

Сообщение отредактировал Chikitonik - Четверг, 16.11.2017, 16:26

Ответить

_Boroda_

Дата: Четверг, 16.11.2017, 16:24 | Сообщение № 2

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16675

Репутация: 6481 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

- Прочитайте Правила форума
- Оформите код тегами (в режиме правки поста выделите код и нажмите кнопку #, пояснялка здесь)

И, если хотите формулой, то приведите не самописный, а нормальный пример, в котором было бы логически понятна конструкция таблицы. Ведь у Вас жирная линия не просто так откуда-то берется, Вы ее по какой-то причине ставите? По какой?

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Chikitonik

Дата: Четверг, 16.11.2017, 16:54 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 13

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Жирной линией отделяются разные контрагенты друг от друга.
Название контрагента вводится один раз, например Вася (как на скриншоте), потом ниже еще могут быть какие-то записи, но они значения не имеют. Имеет значение только название контрагента(Вася) и то, что все его данные отделены жирным подчеркиванием от другого контрагента. В его данных различные цифры и пустые строки, вот цифры надо и суммировать в желтом столбце напротив контрагента.

Ответить

_Boroda_

Дата: Четверг, 16.11.2017, 17:52 | Сообщение № 4

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16675

Репутация: 6481 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Ну как хотите, не даете реальный пример, тогда только или макросом, или макрофункцией
Макрофункцией см. файл. Хоть макросов там и нет, но они должны быть разрешены
Формула для D2

Код

=ЕСЛИ(Гр;СУММ(B2:C999)-СУММ(D3:D999);"")

Что такое "Гр" можно посмотреть в диспетчере имен, нажав Контрл F3

Код

=(((ПОЛУЧИТЬ.ЯЧЕЙКУ(12;Лист1!$A1)=2)+(ПОЛУЧИТЬ.ЯЧЕЙКУ(11;Лист1!$A2)=2))>0)+ЛЕВБ(СЛЧИС())

Дает единицу? если в столбце А жирная или верхняя граница текущей строки или нижняя граница строки выше

К сообщению приложен файл: 2795972_1.xlsm (9.7 Kb)

Ответить

Chikitonik

Дата: Вторник, 21.11.2017, 08:20 | Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 13

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Спасибо за помощь!
Ваша формула в "Гр" по сути выполняет то же, что и моя функция "жирная_нижняя_граница", т.е. определят если жирная граница.

Формула для D2

Код

=ЕСЛИ(Гр;СУММ(B2:C999)-СУММ(D3:D999);"")

также подходит, но она, если можно так выразиться, не гибкая. Ее нельзя использовать на весь столбец, нельзя вставлять какие-либо другие данные в столбец (например общий итог по столбцу).

Я, впринципе, написал формулы, которые решают мою задачу, но с использованием промежуточного столбца, а хотелось бы без него, возможно сможете подсказать?

Вначале использую формулу, которая проставляет контрагента в каждую ячейку между жирными границами (формула в F2)

Код

=ЕСЛИ(жирная_нижняя_граница(A1);A2;F1)

Затем суммирование по созданному диапазону (формула в D2)

Код

=ЕСЛИ(жирная_нижняя_граница(A1);СУММЕСЛИ(F:F;F2;B:B)+СУММЕСЛИ(F:F;F2;C:C);"")

Решение не нравится тем, что добавляется столбец, а также в формуле слишком грубо добавлено суммирование по каждому столбцу, наверняка можно сделать изящнее

К сообщению приложен файл: 5861194.xlsm (14.2 Kb)

Сообщение отредактировал Chikitonik - Вторник, 21.11.2017, 08:21

Ответить

_Boroda_

Дата: Вторник, 21.11.2017, 09:21 | Сообщение № 6

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16675

Репутация: 6481 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Цитата Chikitonik, 21.11.2017 в 08:20, в сообщении № 5 ()

Ваша формула в "Гр" по сути выполняет то же, что и моя функция "жирная_нижняя_граница", т.е. определят если жирная граница.

Да, только работает она гораздо быстрее - это "вшитая" в Excel функция, она по-любому быстрее самописного макроса. Вы нормальный файл так и не дали, поэтому ориентироваться можно только на жирную границу. Был бы нормальный файл, мы бы скорее всего придумали , как найти размер одного блока без просмотра границ.

Цитата Chikitonik, 21.11.2017 в 08:20, в сообщении № 5 ()

Формула для D2
=ЕСЛИ(Гр;СУММ(B2:C999)-СУММ(D3:D999);"")
также подходит, но она, если можно так выразиться, не гибкая. Ее нельзя использовать на весь столбец, нельзя вставлять какие-либо другие данные в столбец

Ну вот интересное заявление! Вы раньше хоть словом об этом обмолвились? Что попросили - то получили, а то, что необозначенные хотелки не работают - это сами виноваты, нужно было говорить про них.

Цитата Chikitonik, 21.11.2017 в 08:20, в сообщении № 5 ()

Я, впринципе, написал формулы ... Решение не нравится тем

Мне оно тоже не нравится, но, снова-здорово, возвращаемся к
1. Нужен реальный файл;
2. Нужно описание хотелок.

Цитата Chikitonik, 21.11.2017 в 08:20, в сообщении № 5 ()

Я, впринципе, написал формулы ... Решение не нравится тем

Мне оно тоже не нравится, но, снова-здорово, возвращаемся к
1. Нужен реальный файл;
2. Нужно описание хотелок._Boroda_

Ответить

Сообщение

Цитата Chikitonik, 21.11.2017 в 08:20, в сообщении № 5 ()

Я, впринципе, написал формулы ... Решение не нравится тем

Мне оно тоже не нравится, но, снова-здорово, возвращаемся к
1. Нужен реальный файл;
2. Нужно описание хотелок.

Автор - _Boroda_
Дата добавления - 21.11.2017 в 09:21

Chikitonik

Дата: Вторник, 21.11.2017, 09:57 | Сообщение № 7

Группа: Пользователи

Ранг: Новичок

Сообщений: 13

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Реальный пример приложил, но в первоначальном примере я привел все данные, что играют ключевую роль. Кол-во столбцов, строк и простые арифметические действия в исходном файле опустил, оставил только суть, чтобы не перегружать лишней информацией.

Ну а хотелка та же самая - ищу формулу, которая будет отображать итог напротив контрагента в выделенном столбце по всем строкам между жирным подчеркиванием (сейчас там ручками забита формула СУММ), ну и хочется, чтобы это бы красиво, элегантно и универсально

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

К сообщению приложен файл: ___2.xlsx (11.3 Kb)

Сообщение отредактировал Chikitonik - Вторник, 21.11.2017, 09:58

Ответить

_Igor_61

Дата: Вторник, 21.11.2017, 11:14 | Сообщение № 8

Группа: Проверенные

Ранг: Ветеран

Сообщений: 504

Репутация: 90 ±

Замечаний: 0% ±

Excel 2007

Цитата Chikitonik, 21.11.2017 в 09:57, в сообщении № 7 ()

не трогая формулу получать нужные данные

Может, пока не поздно, пересмотреть саму организацию данных? Пока не накопились десятки тысяч строк? (для формул большое кол-во строк не очень хорошо, и в конечном итоге все равно придется макрос делать - смотря сколько данных будет в Вашей таблице, конечно)

Со временем можете с таким файлом зайти в тупик. Вариант выхода - макрос, но для Вас

Цитата Chikitonik, 16.11.2017 в 16:21, в сообщении № 1 ()

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

Ответить

Chikitonik

Дата: Вторник, 21.11.2017, 11:30 | Сообщение № 9

Группа: Пользователи

Ранг: Новичок

Сообщений: 13

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Данные да, организованы жутким образом. Я на форуме недавно также просил помощи с формулой для обработки данных. Но переучить, к сожалению нельзя, пытаюсь только облегчить работу людям, которые работают с текущими данными.
Если с формулой вариантов не будет, то можно конечно и макросом, но их там уже и там хватает для других задач, хотелось бы автоматизма без вмешательства пользователя. Ну и так как для меня важно саморазвитие, то стараюсь не использовать простых но громоздких вариантов с дополнительными столбцами, а искать элегантное решение.

Ответить

alex77755

Дата: Вторник, 21.11.2017, 12:49 | Сообщение № 10

Группа: Проверенные

Ранг: Обитатель

Сообщений: 362

Репутация: 64 ±

Замечаний: 0% ±

Цитата

а искать элегантное решение.

самое не элегантное решение:

Цитата

контрагенты между собой отделены жирным подчеркиванием

Могу помочь в VB6, VBA
Alex77755@mail.ru

Ответить

Chikitonik

Дата: Вторник, 21.11.2017, 13:06 | Сообщение № 11

Группа: Пользователи

Ранг: Новичок

Сообщений: 13

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Цитата alex77755, 21.11.2017 в 12:49, в сообщении № 10 ()

самое не элегантное решение:

Это не решение, а задача)
Ну так-то согласен, что это топорно. Но что есть, то есть.

Ответить

sboy

Дата: Вторник, 21.11.2017, 13:24 | Сообщение № 12

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2566

Репутация: 724 ±

Замечаний: 0% ±

Excel 2010

Добрый день.
Написал UDF, но пришлось отключить автопересчет, т.к. даже на таком маленьком примере подвисал.
поэтому пришлось переделать, но тогда нули не нужные появились...
в общем

Цитата Chikitonik, 21.11.2017 в 11:30, в сообщении № 9 ()

Данные да, организованы жутким образом

[vba]

Код

Function СУММ_ЖИР(ByVal cl As Range)
Application.Volatile 'отключил пересчет, т.к. файл подвисает
    If cl.Borders(xlEdgeTop).Weight <> xlMedium Then
        СУММ_ЖИР = Empty
        Exit Function
    End If
    flag = False
        Do
            i = i + 1
            If cl.Offset(i, 0).Borders(xlEdgeBottom).Weight = xlMedium Then flag = True
        Loop While flag = False
    СУММ_ЖИР = WorksheetFunction.Sum(Range(cl, Application.Caller.Offset(i, -1)))
End Function

[/vba]
в функцию передается верхняя левая ячейка диапазона