Главная » Статьи » Excel » Макросы и программы VBA

Как на ёлку влезть и ничего не ободрать...

Задача

Предположим, вы хотите вести ежемесячный учёт неких типовых затрат по нескольким торговым точкам. Так есть, к примеру, торговая точка А, которая понесла затраты на аренду в размере 15000 рублей в марте 2015 года, есть точка Б, которая потратила 3000 руб на электричество в апреле 2015 и так далее. Как лучше всего организовать подобный учёт?

Тут не всё так просто, как хотелось бы. Вот наши исходные данные:

У нас есть 13 торговых точек и их количество с высокой вероятностью может расти. И есть 10 видов затрат и добавление новых видов возможно, но маловероятно.

С точки зрения удобства ввода информации она должна выглядеть так:

А вот с точки зрения последующего беспроблемного извлечения информации (особенно формулами) она должна выглядеть совершенно по-другому:

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

Файл примера

Скачать

Мой метод

Посмотрим, как всё устроено:

  1. Лист Затраты состоит из трёх таблиц.

    Таблица 1 видна пользователю, таблицы 2 и 3 - служебные, поэтому скрыты. Первая таблица отображает данные за тот период времени, который выбран при помощи полей Год и Месяц во второй строке. Эти данные формулами извлекаются во вторую таблицу из умной таблицы Smart листа Expenses. Значения из второй таблицы в первую переносятся макросом. Поскольку пользователь может вносить в таблицу свои значения, то нам необходимо отслеживать ячейки, которые изменены. Мы это делаем при помощи цвета фона ячеек первой таблицы. Зелёный фон означает, что значение совпадает со значением, имеющимся в Smart для данного магазина, вида затрат, периода. Красный фон означает, что значение не совпадает. Цвет определяется правилами условного форматирования, которые ориентируются на таблицу 3, сравнивающую формулами между собой ячейки первых двух таблиц.

  2. При активации листа Затраты запускается процедура считывания данных (ReadExpense) в таблицу 1. То же самое происходит, когда пользователь меняет год или месяц.

  3. При деактивации листа Затраты запускается процедура обновления данных в умной таблице (UpdateExpense). То же самое происходит, когда пользователь всего лишь встаёт на ячейки выбора периода (год / месяц).

  4. Ну и основной модуль с вышеназванными процедурами + универсальная процедура замены данных в умной таблице (ReplaceSmartFilteredRange).

Категория: Макросы и программы VBA | Добавил: dsb75 (13.09.2015) | Автор: Батьянов Денис E W
Просмотров: 2463 | Комментарии: 16 | Теги: Работа с диапазонами | Рейтинг: 0.0/0
Всего комментариев: 16
0
6 mike   (22.09.2015 19:34)
Здоровская вещь.
Хотелось бы, чтобы эта схема работала с Днями.

Подскажите: что это за объект Год и Месяц с выпадающими значениями. в ячейках E2 и H2?

Я хочу сделать аналогичный День (со значениями 1-31)

Всё остальное:
именованный диапазон rngExрDay
колонку в таблице Day
строки в коде, по аналогии rngExрYear и rngExрMonth

я добавил.
Но Ваша обработка после моей модификации не работает. Полагаю, что причина в самом начале: в объекте День, который создал копированием Месяца и созданием именованного диапазона rngExрDay

0
7 dsb75   (22.09.2015 20:02)
Пришлите файл

0
8 mike   (22.09.2015 21:00)
Отправил Вам на  db@perfect-excel.ru

0
9 dsb75   (22.09.2015 21:10)
Единственное место, где вы ошиблись:
iDay = wsExpenses.Range(cExpense_rngDay) - у вас было слева iMonth. Не доглядели просто. А так всё исправили - моё почтение smile

0
10 dsb75   (22.09.2015 21:11)
А, ещё что-то не работает... сейчас подумаю

0
11 dsb75   (22.09.2015 21:15)
Во второй таблице надо формулу исправить было - добавить условие для DAY.

13 mike   (22.09.2015 22:12)
Вообще, откуда берутся значения массивов "Год" и "Месяц".

Я так и не нашёл, где они хранятся.

0
14 dsb75   (22.09.2015 22:28)
Смотрите в меню "Дата" - "Проверка данных..." для соответствующих ячеек.

0
16 mike   (22.09.2015 23:34)
как всё просто )))

12 mike   (22.09.2015 21:30)
Ну, Вы шаман))))

Я так и не понял, что Вы подправили.

В выпадающем списке "День" возможные значения в диапазоне 1-12 - я его скопировал с месяца.

А нужно 1-31.

0
15 dsb75   (22.09.2015 22:29)
Так я же написал что - одна строка кода в модуле + формулы в таблице 2.

0
2 mike   (18.09.2015 18:06)
Здравствуйте, Денис.

----------------------------
добавление новых видов возможно, но маловероятно.
----------------------------

Как, всё таки добавлять столбцы?

0
3 dsb75   (18.09.2015 18:51)
Здравствуйте. Добавьте столбцы в таблицы 1,2,3. Распространите формулы на них. Вроде бы всё.

4 mike   (18.09.2015 18:58)
Как увидеть таблицы 2  и 3?

В таблице 1 я догадался как сделать, а 2 и 3 не могу отредактировать потому что не пойму как их сделать видимыми.

Прошу не смеяться.

0
5 dsb75   (18.09.2015 20:30)
Там скрытые столбцы. Сделайте их видимыми.

0
1 dsb75   (15.09.2015 00:07)
Друзья мои, в статье я ничего не разжёвывал, поэтому, если кому-то тема интересна, но некоторые нюансы остались непонятыми, то не стесняйтесь задавать вопросы в комментариях.

Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика