Теми статей
Обрати теми

Підсумки разом з автофільтром

Редакція БК
Відповідь на запитання

Підсумки разом з автофільтром

 

Шановні працівники редакції! У програмі Excel є дуже зручний інструмент обробки баз даних — «

Итоги». На мій погляд, для бухгалтера він просто незамінний! Але, на жаль, під час роботи з цим інструментом є одна незручність. Справа в тому, що Excel заповнює в рядках підсумків лише ті комірки, в яких здійснювалися обчислення, а решту позицій залишає порожніми. У результаті після обробки бази даних «Итогами» ми отримуємо нерегулярну таблицю. Якщо до такої таблиці застосувати, скажімо, автофільтр, то частину записів він вибиратиме неправильно. Для мене, наприклад, це серйозний недолік. Підкажіть, чи є спосіб вирішити таку проблему? Наперед завдячую.

Володимир Крамаренко, головний бухгалтер, м. Харків

Відповідає

Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства

 

Вважаю, перш ніж перейти до вирішення проблеми, є сенс наочно показати, у чому власне вона полягає. Я зроблю це на прикладі бази даних, зображеної на рис. 1. У ній зібрано операції щодо каси, отримані звітом за проводками з програми «

1С:Бухгалтерия». У базі є такі поля: дата проводки, рахунок дебету і кредиту (колонки «СчД» і «СчК»), сума по дебету і кредиту (колонки «Д» і «К»), а також назва контрагента. Припустимо, я хочу підрахувати сумарний оборот по касі за кожен день, використовуючи режим Excel «Итоги». У цьому випадку виконуємо такі дії:

img 1

1) відкриваємо документ, ставимо покажчик на область бази даних;

2) викликаємо «Данные → Сортировка…». З’явиться вікно настройок параметрів сортування, як на рис. 2;

img 2

3) у ньому клацаємо по полю «Сортировать по», вибираємо зі списку, що розкрився, елемент «Дата». Тут усе зрозуміло — ми підбиватимемо підсумки за датами, тому таблицю потрібно завчасно відсортувати у хронологічному порядку;

4) у вікні «Сортировка диапазона» натискуємо на «ОК». Excel відсортує таблицю за збільшенням дат;

5) викликаємо меню «Данные → Итоги…». З’явиться вікно настройок, як на рис. 3;

img 3

6) у цьому вікні клацаємо по полю «При каждом изменении в:», зі списку, що розкрився, вибираємо «Дата»;

7) клацаємо по елементу «Операция:», вибираємо значення «Сумма»;

8) у вікні «Добавить итоги по:» ставимо прапорці біля елементів «Д» і «К». Це єдині поля бази даних, які є сенс підсумовувати;

9) у вікні «Промежуточные итоги» натискуємо на «ОК». Результат таблиці з підсумками показано на рис. 4.

img 4

Тепер спробуємо застосувати до отриманої таблиці автофільтр та подивимося, що в нас вийде. Виконуємо такі дії:

1) утримуючи натисненою ліву кнопку миші, виділяємо колонки «A:F»;

2) викликаємо меню «Данные → Фильтр → Автофильтр». У заголовках бази з’являться значки для вибору значень;

3) клацаємо по значку в колонці «Дата», зі списку, що розкрився, вибираємо «(условие…)». Відкриється вікно «Пользовательский автофильтр»;

4) у вікні настройок автофільтра заповнюємо параметри, як показано на рис. 5: як операцію вибираємо «меньше», у полі значення вводимо дату «03/03/11». Інакше кажучи, ми вибираємо з бази з підсумками дані про проводки по касі за «01/03/11» і «02/03/11»;

img 5

5) у вікні «Пользовательский автофильтр» натискуємо на «ОК». Результат роботи автофільтра показаний на рис. 6.

img 6

Excel обробив таблицю, але рядки з підсумками тепер не можна побачити. Подивіться на рядок «7» листа «Касса» (рис. 6). Після нього відразу йде рядок номер «9».

Причина криється в тому, як Excel оформив записи з результатами розрахунків. Повернемося до бази даних на рис. 4 і подивимося на значення в рядку номер «8». У комірці «A8» записано текст «01.03.11 Итог». Це саме текст, а не дата, — звичайний коментар, який Excel упровадив до таблиці при підбитті підсумків. Коли ми наклали на таблицю фільтр та відібрали конкретні дати, то рядки з таким коментарем Excel, зрозуміло, не показуватимемо. Отже, у такій ситуації (після підбиття підсумків!) ми не можемо відібрати частину записів з бази за певний період і разом з ними побачити проміжні підсумки.

Щоб розібратися з такою ситуацією, можна вставити до бази робочу колонку, заповнити її потрібними значеннями і згодом використовувати їх при фільтрації даних. От як це зробити для нашого прикладу:

1) відкриваємо вихідну базу даних, як на рис. 1;

2) ліворуч від таблиці додаємо стовпець;

3) формуємо для нового стовпця заголовок. Наприклад, до комірки «A1» записуємо текст «Дата_»;

4) до комірки «A2» вводимо формулу «=B2»;

5) копіюємо цю формулу на всю висоту таблиці. Ми створили допоміжний стовпець, вміст якого точно повторює колонку «Дата»;

6) залишаючись усередині бази даних, викликаємо «Данные → Итоги…». З’явиться вікно настройок. Заповнюємо його параметрами, як показано на рис. 3;

7) у вікні «Промежуточные итоги» натискуємо на «ОК». Отримуємо таблицю, як на рис. 7. У ній з’явилася додаткова колонка з датами проводок (вона називається «Дата_»). У тих рядках, де Excel упровадив підсумки, комірки у колонці «Дата_» залишилися порожніми. Тепер наше завдання — заповнити ці комірки конкретними значеннями, а саме: до кожної порожньої комірки колонки «Дата_» ми повинні скопіювати значення з розташованої вище комірки. Наприклад, до комірки «A8» потрібно ввести значення дати «A7», до «A18» — значення з комірки «A17» тощо. Це зручно зробити формулою. Для цього в комірці «A8» достатньо записати вираз «=A7», до комірки «A18» увести вираз «=A17» і в такий спосіб відкоригувати всю таблицю. Ми не будемо заповнювати ці комірки вручну, а скористаємося масивом формул. Виконуємо такі дії:

img 7

1) клацаємо по заголовку колонки «A» (виділяємо її);

2) натискуємо на комбінацію клавіш «Ctrl+G» (або викликаємо «Правка → перейти…»). З’явиться вікно, як на рис. 8;

img 8

3) у цьому вікні натискуємо на кнопку «Выделить». Excel відкриє вікно «Выделение группы ячеек», як на рис. 9;

img 9

4) у ньому перемикач «Выделить» ставимо в положення «пустые ячейки»;

5) у вікні «Выделение группы ячеек» натискуємо на «ОК». Усі незаповнені комірки в межах колонки «А» буде виділено до одного незв’язного блока. При цьому активною виявиться перша комірка цього блока (у нашому прикладі — це «A8»);

6) не знімаючи виділення (!), уводимо символ «=» і клацаємо по комірці «A7». У такий спосіб до комірки «A8» ми записали формулу «=A7»;

7) натискуємо на комбінацію клавіш «Ctrl+Enter». Усі порожні комірки в колонці «A» буде заповнено посиланнями на попередню комірку. У результаті в комірці «A18» з’явиться формула «=A17», у «A40» — формула «=A39» тощо;

8) виділяємо всю базу даних та викликаємо «Данные → Фильтр → Автофильтр»;

9) клацаємо по значку в колонці «Дата», зі списку, що розкрився, вибираємо «(условие…)» та у вікні «Пользовательский автофильтр» заповнюємо параметри, як показано на рис. 5;

10) у вікні «Пользовательский автофильтр» натискуємо на «ОК». Результат роботи автофільтра показаний на рис. 10.

img 10

Завдання вирішено. Тепер фільтр обробляє всі рядки бази даних, уключаючи записи з підсумками (рис. 10).

І останнє. Ми заповнили значеннями комірки лише в колонці «А» (де продублювали дати проводок). У нашому випадку переносити до рядка підсумків значення з інших полів бази даних просто не мало сенсу. На практиці трапляються й інші ситуації, коли в рядку підсумків потрібно продублювати значення з декількох колонок. Щоб вирішити таке завдання, вам навіть не доведеться заповнювати кожен стовпець окремо — усе можна зробити за один прийом! Для цього виділіть декілька колонок, утримуючи натисненою клавішу «Ctrl», і сформуйте масив формул.

 

Успішної роботи! Чекаю ваших запитань, зауважень та пропозицій на

bk@id.factor.ua , nictomkar@rambler.ru .
App
Завантажуйте наш мобільний додаток Factor

© Factor.Media, 1995 -
Всі права захищені

Використання матеріалів без узгодження з редакцією заборонено

Ознайомитись з договором-офертою

Приєднуйтесь
Адреса
м. Харків, 61002, вул. Сумська, 106а
Ми приймаємо
ic-privat ic-visa ic-visa

Ми використовуємо cookie-файли, щоб зробити сайт максимально зручним для вас та аналізувати використання наших продуктів та послуг, щоб збільшити якість рекламних та маркетингових активностей. Дізнатися більше про те, як ми використовуємо ці файли можна тут.

Дякуємо, що читаєте нас Увійдіть і читайте далі