Трюк №63. Как в Excel 2010 заполнить в списке все пустые ячейки

Часто пользователи оставляют ячейки пустыми, если данные в этих ячейках такие же, как и в ячейках выше. Это упрощает чтение списков, но ухудшает структуру данных. При помощи трюков этого раздела можно быстро и просто заполнить все пустые ячейки в списке.

Многие функции Excel 2010 предназначены для работы со списками. Чтобы обеспечить правильную работу большинства из них, списки не должны содержать пустые ячейки, а форматирование заголовков столбцов должно отличаться от форматирования данных в списке. При настройке данных в Excel полезно убеждаться, что заняты все ячейки в списке. Однако часто списки задаются, как показано на рис. 6.3.

Рис. 6.3. Список с пустыми ячейками на рабочем листе

Хотя в столбце Цена цены повторяются, виды фруктов в столбце Fruits — нет. Как упоминалось, такая организация данных создаст много проблем при использовании таких средств, как промежуточные итоги (Subtotals) и сводные таблицы (PivotTables). В большинстве случаев Excel ожидает, что связанные данные помещены в непрерывный список или таблицу без пустых ячеек. Есть два способа заполнения пустых ячеек в списке: при помощи формулы и при помощи макроса.

Способ 1. Заполнение пустых ячеек при помощи формулы

Предположим, есть список записей в столбце А, например, как в предыдущем примере, и в этом списке есть пустые ячейки. Вот как быстро и просто можно заполнить эти ячейки значениями из ячеек над ними. Выделите все данные в столбце А, выберите команду Правка → Перейти (Edit → Go То) (сочетание клавиш Ctrl+G) и щелкните кнопку Выделить (Special). Установите переключатель-флажок Пустые ячейки (Blanks) и щелкните кнопку ОК. Теперь в списке выделены только пустые ячейки. Нажмите клавишу = (клавишу знака равенства), а затем стрелку вверх. Удерживая клавишу Ctrl, нажмите клавишу Enter.

Можно быстро преобразовать формулы в значения, выделив столбец А целиком. Выберите команду Правка → Копировать (Edit → Copy) (сочетание клавиш Ctrl+C), затем выберите команду Правка → Специальная вставка (Edit → Paste Special), установите переключатель Значения (Values) и щелкните на кнопке ОК.

Способ 2. Заполнение пустых ячеек при помощи макроса

Если вам часто приходится заполнять пустые ячейки, лучше делать это при помощи макроса. Чтобы создать его, выберите команду Разработчик → Редактор Visual Basic (Developer → Visual Basic Editor) (сочетание клавиш Alt/Option+Fll), затем выберите команду Insert → Module и введите код из листинга 6.1.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

// Листинг 6.1
Sub FillBlanks()
Dim rRangel As Range. rRange2 As Range
Dim IReply As Integer
	If Selection.Cells.Count = 1 Then
		MsgBox "нужно выделить лист и включить пустые ячейки",
		vblnformation, "Excel2010.ru"
		Exit Sub
	Elself Selection.Columns.Count → 1 Then
		MsgBox "нужно выделить только один столбец".
		vblnformation, "Excel2010.ru"
		Exit Sub
	End If
 
	Set rRangel = Selection
 
	On Error Resume Next
	Set rRange2 = rRangel.SpecialCells(xlCellTypeBlanks)
	On Error GoTo 0
 
	If rRange2 Is Nothing Then
		MsgBox "пустые ячейки не найдены",
			vblnformation. "Excel2010.ru"
		Exit Sub
	End If
 
	rRange2.FormulaRlCl = "=R[-1]C"
 
	IReply = MsgBox("преобразовать в значения", vbYesNo + vbQuestion."Excel2010.ru")
	If IReply = vbYes Then rRangel = rRangel.Value
End Sub

Закончив, закройте окно, чтобы вернуться в Excel, и сохраните рабочую книгу. Теперь выберите команду Разработчик → Макрос → Макросы (Developer → Macro → Macros) (сочетание клавиш Alt/Option+F8), выберите Fill Blanks и щелкните кнопку Выполнить (Run), чтобы выполнить макрос, или кнопку Параметры (Options), чтобы назначить клавишное сочетание.