«Умное» объединение колонок в MS Excel

Способ решения описанной проблемы есть, причем простой и, я бы сказал, — весьма изящ­ный. К тому же работает он в любой версии программы Excel. Но для начала давайте вернемся к самой задаче и разберемся, о чем пойдет речь. Сделать это я предлагаю на примере таблицы, фрагмент которой показан на рис. 1. Это база данных кассовых операций. Я оставил в ней всего пять полей: «ДАТА» — дата операции; «СчД» — счет дебета проводки; «СчК» — счет кредита проводки; «Д» — сумма по дебету; «К» — сумма по кредиту. Все операции упорядочены по дате, но в каждой колонке есть незаполненные ячейки. Например, в колонке «Д» остались пустыми «D2», «D7», «D10» и т. д. Зато в столбце «К» точно напротив этих ячеек данные есть. И теперь наша задача — объединить информацию из столбцов «Д» и «К» в одно целое. Иными словами, мы должны выборочно скопировать значения из столбца «К» на место пустых ячеек в колонке «Д». Причем сделать все так, чтобы исходные значения доходов остались на месте.

Если действовать «в лоб», то процедура копирования выглядит так. Выделяем одно или несколько значений в колонке «К». Вырезаем их в буфер обмена, а затем вставляем на соответствующую позицию в колонке «Д». В принципе, ничего сложного. Только для большой базы операцию копирования придется повторять многократно. А это долго, утомительно и легко ошибиться. Поэтому я предлагаю воспользоваться инструментом специальной вставки, что позволит справиться с задачей в считанные секунды. Причем независимо от объема исходных данных!

Начнем с простого. Чтобы скопировать значения из колонки «К» на место пустых ячеек в колонке «Д» (рис. 1), делаем так:

1. Открываем документ, выделяем столбец «E» (обороты по кредиту, рис. 1).

2. Копируем данные в буфер обмена (через панель инструментов или комбинацией «Ctrl+С»).

3. Щелчком мышки на заголовке выделяем колонку «D» (обороты по дебету, рис. 1).

4. Вызываем меню «Правка → Специальная вставка». Откроется окно, как на рис. 2.

5. В этом окне включаем флажок «Пропускать пустые ячейки».

6. Нажимаем «ОК». Результат нашей работы показан на рис. 3.

Судя по всему, Excel правильно объединил ячейки. Значения оборотов по кредиту стали на место пустых ячеек в колонке с данными по дебету. Все, что остается сделать, — это удалить колонку «E»: информация в ней нам больше не нужна.

Данные мы скопировали. Но для полноценного решения задачи этого мало. И вот почему. В исходной таблице все обороты были представлены как положительные числа. И, с точки зрения канонов бухучета, — это правильно. Что же касается других категорий расчетов, такая ситуация может оказаться неверной. После объединения ячеек у нас получилось, что в одном потоке смешались и доходы (которые должны идти со знаком «+»), и расходы, которые логично показать со знаком минус. Опять-таки, корректировать таблицу и проставлять знак «-» перед каждым числом — задача неблагодарная. Посмотрим, как можно осуществить такую операцию в программе Excel.

Вначале при помощи специальной вставки мы выделим все пустые ячейки в колонке с оборотами по дебету. Затем превратим эти ячейки в массив и заполним их значением «-1». После этого тем же инструментом специальной вставки мы перенесем обороты по кредиту в колонку «Д» с одновременным умножением данных. Вот такая схема. А дальше делаем так:

1. Открываем документ, щелкаем левой кнопкой на заголовке колонки «D» (выделяем весь столбец).

2. Нажимаем «Ctrl+G» (или вызываем меню «Правка → Перейти…»). Откроется окно, изображенное на рис. 4.

3. В этом окне нажимаем кнопку «Выделить…». Откроется окно «Выделение группы ячеек», как на рис. 5.

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

5. В окне «Выделение группы ячеек» нажимаем «ОК». Все свободные ячейки в колонке «D» Excel выделит в один несвязный блок. При этом активной будет первая ячейка этого блока. Применительно к базе на рис. 1 — это ячейка «D2».

6. Не снимая выделения (!) вводим с клавиатуры значение «-1».

7. Нажимаем комбинацию «Ctrl+Enter». Введенное значение Excel скопирует во все пустые ячейки колонки «D» (рис. 6). Теперь можно приступать к копированию данных.

8. Щелчком мышки на заголовке колонок выделяем столбец «Е» (обороты по кредиту).

9. Копируем данные в буфер обмена (комбинация «Ctrl+C»).

10. Выделяем всю колонку «D» (обороты по дебету).

11. Вызываем меню «Правка → Специальная вставка». Откроется окно, аналогичное изображенному на рис. 2.

12. В этом окне включаем флажок «пропускать пустые ячейки».

13. Переключатель «Вставить» ставим в положение «умножить».

14. В окне специальной вставки нажимаем «ОК». В результате Excel перенесет обороты по кредиту на место пустых ячеек в колонке «D». И тут же умножит каждое значение на содержимое целевой ячейки, куда мы предусмотрительно записали «-1». В результате обороты по кредиту попадут в столбец «D» со знаком «-». Фрагмент итоговой таблицы после копирования показана рис. 7.

15. Удаляем столбец «E» с оборотами по кредиту.

До новых встреч и удачной работы! Жду ваших писем, вопросов, замечаний и предложений на bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum .