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

Да, есть способ сделать это, но он предполагает использование макросов.

Прежде чем рассматривать решение на основе макросов, вы можете подумать о реструктуризации данных, чтобы каждый из ваших разделов находился на разных листах. (С точки зрения дизайна это было бы самым простым решением.) Если это невозможно, то вам нужно искать макросы.

Один из простых подходов — просто изменить то, что хранится в верхней строке (строка 1) вашего рабочего листа, в зависимости от того, какая строка выбрана. Например, следующий макрос внесет изменения в верхнюю строку в зависимости от того, где находится активная ячейка. Если это перед строкой 40, то в первую строку вставляется один набор заголовков; если после строки 40, то заполняется другой набор заголовков.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim iBottomData As Integer

iBottomData = 40

If ActiveCell.Row < iBottomData Then         Cells(1, 1).Value = "Last Name"

Cells(1, 2).Value = "First Name"

Cells(1, 3).Value = "Address"

Cells(1, 4).Value = "Balance"

Else         Cells(1, 1).Value = "Account"

Cells(1, 2).Value = "Sales Rep"

Cells(1, 3).Value = "Status"

Cells(1, 4).Value = ""

End If End Sub

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

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

Private Sub Worksheet_BeforeRightClick(ByVal _   Target As Range, Cancel As Boolean)

Application.ScreenUpdating = False     ActiveWindow.FreezePanes = False     ActiveWindow.Split = False     Application.EnableEvents = False     Application.Goto Cells(1, 1), scroll:=True     With ActiveWindow         .SplitColumn = 0         .SplitRow = 1     End With     ActiveWindow.FreezePanes = True     Application.Goto Cells(Target.Row - 1, _       Target.Column), scroll:=True     Application.EnableEvents = True     On Error Resume Next        'MUST reenable events     Application.EnableEvents = False     ActiveCell.Offset(-1, 1 - Target.Column).Select     ' so the right click menu doesn't popup     ' only if this is the second header row.

Cancel = True      Application.EnableEvents = True End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False     If Cells(Target.Row, 1).Value <> "title2" Then Exit Sub     ActiveWindow.FreezePanes = False     ActiveWindow.Split = False     Application.Goto Cells(Target.Row, 1), scroll:=True     With ActiveWindow         .SplitColumn = 0         .SplitRow = 1     End With     ActiveWindow.FreezePanes = True     On Error Resume Next        'MUST reenable events     Application.EnableEvents = False     ActiveCell.Offset(1, 1 - Target.Column).Select     Application.EnableEvents = True End Sub

Обработчик событий Worksheet_SelectionChange автоматически перемещает замороженное разбиение ниже второй строки заголовков, когда курсор активной ячейки достигает этой строки. Эта строка обнаруживается в операторе If, который проверяет, содержит ли первая ячейка строки текст «title2» или нет.

(Очевидно, это следует изменить, чтобы отразить то, что действительно будет в этой первой ячейке.)

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

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

Создание более обширного решения выходит за рамки этого совета, потому что оно потребует взаимодействия с реальной операционной системой.

Однако, если вы заинтересованы в том, чтобы пойти по этому маршруту, хорошей отправной точкой может быть эта страница на веб-сайте Чипа Пирсона:

http://www.cpearson.com/excel/DetectScroll.htm

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (11231) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Switching_Headers_in_a_Frozen_Row [Переключение заголовков в замороженной строке].