Алексей Гладкий - Excel. Трюки и эффекты

В процессе работы с большими объемами данных часто возникает необходимость быстрого поиска максимального значения определенного диапазона (либо всего рабочего листа). Например, если в таблице хранится информация о торговой выручке за каждый день в течение года, то, чтобы найти максимальную дневную выручку вручную, потребуется слишком много времени и усилий. Для автоматизации данного процесса можно применить макрос, код которого приведен в листинге 2.20.

Sub FindMaxValue()

On Error Goto NoCell

If Selection.Count > 1 Then

' Поиск максимального значения в выделенных ячейках

Selection.Find(Application.Max(Selection)).Select

Else

' Поиск максимального значения во всех ячейках листа

ActiveSheet.Cells.Find(Application.Max(ActiveSheet.Cells)).Select

End If

Exit Sub

NoCell:

MsgBox «Максимальное значение не найдено»

End Sub

После запуска данного макроса будет выделена ячейка, содержащая максимальное значение. Если выделен диапазон, поиск осуществляется только в нем; если же диапазон не выделен, то поиск ведется по всему рабочему листу. Если максимальное значение не обнаружено, то по окончании поиска на экране отобразится окно с сообщением Максимальное значение не найдено (текст этого сообщения можно корректировать по своему усмотрению путем внесения соответствующих изменений в код макроса).

Рассмотрим трюк, с помощью которого можно автоматически заменять значения ячеек диапазона на какие-либо фиксированные значения. В листинге 2.21 показан код одного из макросов, которые позволяют решить данную задачу.

Sub ReplaceValues()

Dim cell As Range

' Проверка каждой ячейки диапазона на возможность замены _

значения в ней (отрицательные значения заменяются на -1, _

положительные – на 1)

For Each cell In Range(«C1:C3»).Cells

If cell.Value < 0 Then

cell.Value = -1

ElseIf cell.Value > 0 Then

cell.Value = 1

End If

Next

End Sub

В данном примере обрабатывается диапазон С1:C3. После применения приведенного выше макроса все положительные значения данного диапазона будут заменены на 1, а отрицательные – на -1.

Очевидно, что с помощью подобных макросов можно заменять значения в любых диапазонах.

При необходимости можно скрыть от несанкционированного просмотра либо редактирования содержимое той или иной ячейки. Рассмотрим применение данной операции на конкретном примере.

Допустим, в ячейке А1 содержится значение 2 5, в ячейке А2 – значение 4 3, а в ячейке В1 рассчитана сумма двух этих ячеек по формуле =СУММ (А1: А2) (рис. 2.3).

Рис. 2.3. Фрагмент рабочего листа

Скроем содержимое ячейки А1. Для этого выделим ее, выполним команду контекстного меню Формат ячеек и в открывшемся окне на вкладке Число в списке Числовые форматы выберем позицию (все форматы), которая расположена в конце данного списка. После этого в открывшемся справа поле Тип введем подряд три точки с запятой —;;; (рис. 2.4).

Рис. 2.4. Окно Формат ячеек

После нажатия в окне кнопки ОК содержимое ячейки А1 будет скрыто (рис. 2.5).

Рис. 2.5. Скрытие содержимого ячейки

На данном рисунке видно, что, несмотря на то что в ячейке А1 ничего не отображается, значение в ней все равно сохраняется и участвует в расчетах (см. строку формул и значение в ячейке В1). Если установить курсор в ячейку А1, то ее значение отобразится в строке формул.

В процессе эксплуатации программы иногда возникает необходимость быстрого заполнения нескольких ячеек одинаковым содержимым. Наиболее распространенный способ – копирование нужного значения в буфер, затем выделение диапазона ячеек, в которые необходимо ввести это значение, и нажатие Enter. Однако подобный эффект достигается и другим способом. Для этого необходимо выполнить следующие действия.

1. Выделить соответствующий диапазон ячеек (допускается выделение несмежных ячеек).

2. Ввести требуемое значение.

3. Нажать сочетание клавиш Ctrl+Enter.