Сумма столбца макросом

[Ingwar]

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

[economist]

Alt+= под данными вставит формулу.

[economist]

Макрос выглядит в общем случае так:

Код Выделить Развернуть

option vbasupport 1

Sub Main
[B17].FormulaLocal = "=СУММ(B2:B16)" ' Формула в яч. [B17]
End Sub
Есть стандартный путь самостоятельного решения подобных задач:
1) записать макрос в Excel, нажать Alt+F11 и просто посмотреть, закрыть-сохранить 
2) открыть этот же файл в Calc, в Alt+F12 - Безопасность - ...макросов (все разрешить).
3) пользоваться макросом, назначив его на Клавишу, Кнопку/Пункт меню или сделав Автозапуск при открытии файла или по Событиям (их много).

Записанный в Excel макрос в 80% заработает сразу. В 20% заработает с небольшой правкой, которая гуглится влет.

Особенно радует то что "форматирующие", самые муторные макросы, записанные в Excel - почти всегда работают в Calc без танцев с бубном.

Макрорекордер в самом Calc, к сожалению, понимает мало действий (~половину) и часто дает "полуфаборикат" кода, но тоже полезен для записи простых действий.

Если прислушаться к умным книжкам по эргономике интерфейсов, то выяснится что мы всё делаем неправильно: итоги и формулы их подсчета нужно размешать вверху документа на первой странице, а не внизу на последней.

Во-первых так удобнее смотреть. А во вторых, вверху можно написать "вечную" формулу, которую не нужно изменять при росте числа строк. Просто сделайте ее с запасом, не на 10 строк, а на 50000:  "=СУММ(B2:B50000)" Или даже миллион. Основные функции СУММ СУММЕСЛИ ВПР ГПР итд - не обращают внимания на пустые ячейки и не "тормозят", но некоторые другие - могут. Поэтому если в списке не может быть больше тысячи строк - не надо делать формулу на миллион строк. И уж тем более нельзя включать в формулы ссылки вида B:B (на целые столбцы).

PS: Опытные товарищи говорят что FormulaLocal использовать в коде нежелательно (вдруг у вас язык не русский или функции в Calc на английском)?
Они правы, но правильный путь в OpenOffice|LibreOffice, как правило, сложнее чем записанный или взятый из VBA Excel. Настолько, что может казаться непреодолимым. Решите для себя что вам ближе

Код Выделить Развернуть

.FormulaLocal = "=СУММ(B2:B16)"
.FormulaR1C1 = "=SUM(R[-16]C:R[-1]C)"

Также не очень хорошо использовать опцию совместимости option vbasupport 1, то есть лучше писать на родном LO Basic (труднее, многословнее).

И, конечно же, нельзя разрешать все макросы навсегда. Лучше каждый раз тратить время на осознание что открылось, смотреть код макроса, убеждаться что он ничего вредного не делает и разрешать работу макросов кнопкой вверху экрана.

А лучше вообще не включать макросы, а пользоваться, с чего я и начал, Alt+=

[mikekaganski]

Код Выделить Развернуть

.FormulaLocal = "=СУММ(B2:B16)"
.FormulaR1C1 = "=SUM(R[-16]C:R[-1]C)"


Либо, если FormulaR1C1 (идеальный вариант для относительных ссылок) слишком сложна, можно пользоваться стандартным нелокализованным синтаксисом A1, также работающим независимо от локализации, используя Formula:

Код Выделить Развернуть

.Formula = "=SUM(B2:B16)"

[Ingwar]

Большое спасибо за оказанную помощь. Завтра на работе испытаю. Просто есть таблица нарушений работы водителей автобусов состоит из трех столбцов, первый столбец (краткое описание) в ячейке до 20 символов, второй столбец (суть нарушения) до 60 символов, и третий столбец (пункт инструкции водителя который он нарушил) до 300 символов. Всего в таблице порядка 120 строк, чтобы проще было искать в базе, решил через instr в цикле пройтись по столбцам в ячейку справа заносится результат поиска если есть совпадения значит 1, нет 0. Дальше идёт сортировка через False и строки в которых есть совпадения оказываются сверху. Просуммировав единички от совпадений, которые укажут на количество строк, выбранный масив должен попасть на другой лист, а уже изновой таблицы в Listbox на диалоговом окне. Сумма нужна для переменной, так что вставить формулу в ячейку не совсем тот метод. Бьюсь за то, чтобы книга была без единой формулы. Предыдущая книга сдержала около 5 тыс формул, размер 4 мб, файл открывается около 2 минут, поэтому складывать лишние строки совсем не хочется. Кстати Питоньяк писал о каких-то огромных величина времени при работе с ячейками а не с массивом, могу сказать, что поиск по ключевому и слову и сортировка строк в описанной мною таблице, происходит за считанные доли секунды. Сегодня нарыл рабочий вариант сложения чисел из столбца через метод Access. и он действительно работает в отличии от VBA и кучи его диалектов. Завтра проверю что лучше.

[economist]

Файл-образец был бы лучшим объяснением того что нужно достичь (и что вам нужна не сумма, а счет значений).

Комбинация простых полу-ручных операций, описанная вами, не может быть хорошим решением, только как временное. Сложные формулы могут тормозить, если циклически ссылаются друг на друга (или всегда тормозят формулы массива, которые проще перевставить макросом как значения). Я постоянно работаю с большими таблицами с десятками тыс. формул и ни разу не наблюдал открытия или пересчета файла дольше 30 секунд, кроме как с формулами массива (которые сейчас в новых версиях Calc стали почти не нужны).

Довешивание диалогов с ListBox там где лучше справится обычный функционал ячеек (Данные-Проверка-Диапазон) - это тоже перебор. Изучите базовый функционал электронных таблиц Calc/Excel (он одинаков), вы там найдете гораздо более простые пути автоматизации и построения приложений. Макросы и тем более Диалоги - это "приправа" к Calc/Excel, а не основной ингредиент.

[mikekaganski]

Бьюсь за то, чтобы книга была без единой формулы

Это кошмар. Формулы - единственное, что делает табличные процессоры тем, что они есть. Бывают баги; бывают патологические сочетания, которые можно оптимизировать. Но идея о книгах без единой формулы - просто непонимание того, чем Вы пользуетесь.