Секретное оружие: массивы 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 . Удачной работы!