Таємна зброя: масиви Excel

Таємна зброя: масиви Excel

 

Шановна редакціє! У мене таке запитання. Є список (база даних) Excel. У цьому с писку замість елементів, що повторюються, у ключовій колонці стоять порожні значення. До такого списку не можна застосувати інструменти обробки даних («Итоги», «Сортировка, «Сводная таблица»). Для цього пропущені значення потрібно продублювати. Для довгого списку така робота забирає багато часу. Чи можна її якось прискорити? Дякую.

В. Івахненко, м. Харків

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

 

Є кілька способів вирішення цієї проблеми. Але спочатку поясню докладніше нашим читачам, про що йдеться.

Звернемося до таблиці на рис. 1. Такі форми звітів дуже часто зустрічаються у практиці будь-якого бухгалтера. На перший погляд, перед нами база даних Excel. Але не зовсім. У колонці «

Дата», наприклад, замість значень, що повторюються, записано порожні комірки. Застосувати до такої таблиці засоби обробки даних Excel не можна. Припустимо, я хочу побудувати підсумкову таблицю про кількість товарів, що надійшли (чи вибули), на кожну дату. Інструмент для цього є, спробуємо ним скористатися. Викликаємо Майстра зведених таблиць, до області рядків переносимо поле «Дата», до області даних переносимо «Кол.» та отримуємо результат, як показано на рис. 2.

 

Excel усе зробив правильно. До підсумкового значення за надходженням за «

01/09/09» у розмірі 600 шт. він уключив дані лише для місця зберігання «Осн. склад» із другого рядка робочого листа. Для інших записів за «01/09/09» у полі «Дата» стоять порожні значення. Ці надходження Excel переніс до підсумкового рядка з назвою «(пусто)».

Рішення очевидне:

потрібно заповнити всі значення дат, продублювавши відповідні комірки в колонці «А». Інакше кажучи, перед обробкою даних потрібно побудувати таблицю, як показано на рис. 3.

Звичайно, для нашого прикладу із 17 рядків на вирішення завдання буде витрачено не більше однієї хвилини, але бухгалтер працює з великими таблицями. І тут уже виникає проблема. Підрахуйте самі, скільки часу забере копіювання даних, якщо в базі на рис. 1 буде записано відомості за звітний рік!

Прискорити процес можна принаймні трьома способами: за допомогою формул, масивів та з використанням макросів. На мій погляд, найзручніший із них — це робота з масивами. З нього й почнемо.

 

Заповнення комірок через функцію масиву

Щоб скористатися цим прийомом, виконуємо такі дії:

1) відкриваємо документ. У нас це таблиця, як на рис. 1;

2) виділяємо колонку «

A» (поле «Дата»);

3) викликаємо меню «

Правка → Перейти…» або натискуємо на «Ctrl+G». З’явиться вікно, зображене на рис. 4;

4) у вікні клацаємо по кнопці «

Выделить». З’явиться вікно «Выделение группы ячеек», як показано на рис. 5;

5) у цьому вікні встановлюємо перемикач «

Пустые ячейки» та натискуємо на «ОК». Excel виділить порожні комірки в колонці «A»;

6)

не знімаючи виділення, вводимо до першої комірки знак «=» (дорівнює) та клацаємо на попередню комірку, тобто в комірці «A3» (рис. 1) створюємо посилання на попередню комірку («A2»);

7) натискуємо на «

Ctrl+Enter». Excel заповнить формулою всі виділені комірки. Таблиця набуде вигляду, як показано на рис. 3.

Важливо!

При введенні формули слід натискувати саме на «Ctrl+Enter». Це і є команда створення формули-масиву. Якщо натиснути просто на «Enter», нічого не вийде.

На мій погляд,

використання формули-масиву — найвитонченіший та найшвидший спосіб вирішення завдання, однак не єдиний. Ось ще кілька варіантів.

 

Заповнення комірок за допомогою формул

Другий спосіб вирішення проблеми ґрунтується на звичайних формулах. Виконуємо такі дії:

1) до комірки «

E1» записуємо заголовок нової колонки. Я назвав її «Дата_»;

2) до комірки «

E2» вводимо формулу «=ЕСЛИ(A2=””;E1;A2)»;

3) копіюємо формулу на всю висоту таблиці. Отримуємо результат, як показано на рис. 6.

Тепер при формуванні зведеного звіту замість поля «

Дата» можна використовувати поле «Дата_».

Якщо робочу колонку залишати не хочеться, перенесіть значення з поля «

Дата_» до колонки «Дата». Тільки не забудьте, що копіювати потрібно значення. Для цього виконуємо такі дії:

1) виділяємо блок даних у колонці «

Дата_» (у нашому прикладі це «E2:E17»);

2) копіюємо їх до буфера обміну («

Ctrl+C»);

3) робимо активною комірку «

A2»;

4) викликаємо меню «

Правка → Специальная вставка…», у вікні настройки параметрів (рис. 7) ставимо перемикач «значення» і натискуємо на «ОК»;

5) колонку «

Дата_» видаляємо.

Завдання вирішено, спосіб працює. Щоправда, у нього є

недоліки: потрібно створювати робочу колонку, набирати і копіювати формули. А все це забирає час.

 

Використовуємо макрос

Тим, хто бажає попрацювати з VBA, можу порадити такий спосіб:

1) відкриваємо вихідну таблицю (рис. 1);

2) клацаємо правою кнопкою по ярличку робочого листа з базою даних. Відкриється вікно редактора Visual Basic;

3) уводимо текст програми:

 

Sub CellNotEmpty()

For Each cel In Selection

If cel.Offset(1, 0) = "" Then cel.Offset(1, 0) = cel

Next

End Sub

;

 

4) закриваємо вікно Visual Basic та зберігаємо файл.

Щоб виконати макрос, виконуємо такі дії:

1) відкриваємо документ (рис. 1);

2) виділяємо блок комірок для заповнення («

A2:A17»);

3) заходимо до меню «

Сервис → Макрос → Макросы…». З’явиться вікно зі списком доступних макросів;

4) знаходимо у списку «

CellNotEmpty» і натискуємо на кнопку «Выполнить».

Таблиця набуде форми, як показано на рис. 3.

Важливо!

Макрос «Sub CellNotEmpty» заповнить одну зайву комірку в кінці виділеного діапазону. Це — наслідок максимального спрощення тексту програми. Зайве значення потрібно видалити. А краще спочатку виділити блок на одну комірку менше, тобто замість «A2:A17» вказуємо «A2:A16».

Основний недолік роботи з макросом: після його виконання не можна скористатися функцією «

Откат».

І останнє. Проблема заповнення порожніх комірок часто виникає при роботі з меню «

Итоги». А це — один з основних інструментів бухгалтера. Поясню на конкретному прикладі.

Беремо базу на рис. 1. Я хочу побачити надходження за кожним місцем зберігання та(!) за кожну дату. Для цього виконуємо такі дії:

1) ставимо покажчик активної комірки на область бази даних. Викликаємо «

Данные → Сортировка…»;

2) у полі «

Сортировать по» вибираємо «Дата», у полі «Затем по» ставимо «Место хранения»;

3) натискуємо на «

ОК». Тепер вихідну таблицю відсортовано за датами, а всередині дат — за місцями зберігання;

4) викликаємо «

Данные → Итоги…» та заповнюємо вікно, як показано на рис. 8. Ключовим полем у нас буде «Место хранения». Як обробку ми вибрали операцію підсумовування за полем «Кол.». У результаті Excel після кожної зміни вставить до поля «Место хранения» рядок підсумків. А в цьому рядку він підрахує суму за полем «Кол.». Результат обробки показано на рис. 9;

 

5) зменшимо деталізацію, залишивши на екрані тільки підсумкові значення. Для цього клацаємо по кнопці групування з написом «

2» (рис. 9). Отримаємо таблицю, як показано на рис. 10. У ній ми бачимо інформацію про місця зберігання. Але в колонці «Дата» всі комірки виявилися порожніми: при роботі з підсумками Excel заповнив лише суми та стовпець з ключовим полем. Ліквідуємо цей недолік;

6) клацаємо по кнопці групування з написом «

3», щоб повернутися до таблиці на рис. 9;

7) виділяємо колонку «

A» (поле «Дата»);

8) викликаємо меню «

Правка → Перейти…» або натискуємо на «Ctrl+G». З’явиться вікно «Переход»;

9) клацаємо по кнопці «

Выделить». З’явиться вікно «Выделение группы ячеек» (рис. 5);

10) у ньому встановлюємо перемикач «

Пустые ячейки» та натискуємо на «ОК». Excel виділить усі порожні комірки в колонці «А». Першою з них буде «A4»;

11) не знімаючи виділення, уводимо до «

A4» формулу «=A3»;

12) натискуємо на «

Ctrl+Enter». Excel заповнить усі порожні комірки в колонці «Дата»;

13) клацаємо по кнопці групування з написом «

2», щоб згорнути таблицю до другого рівня деталізації. Результат показано на рис. 11. Тепер у таблиці можна побачити і дати надходження, і місця зберігання ТМЦ.

 

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

bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum . Успішної роботи!