Стиль маркировки ячеек excel

Перейти к содержимому

«У меня в Excel, в заголовках столбцов листа появились цифры (1,2,3…) вместо обычных букв (A,B,C…)! Все формулы превратились в непонятную кашу с буквами R и С! Что делать??? Помогите!»

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

Что это

Классическая и всем известная система адресации к ячейкам листа в Excel представляет собой сочетание буквы столбца и номера строки — морской бой или шахматы используют ту же идею для обозначения клеток доски. Третья сверху во втором столбце ячейка, например, будет иметь адрес B3. Иногда такой стиль ссылок еще называют «стилем А1». В формулах адреса могут использоваться с разным типом ссылок: относительными (просто B3), абсолютными ($B$3) и смешанного закрепления ($B3 или B$3). Если с долларами в формулах не очень понятно, то очень советую почитать тут про разные типы ссылок, прежде чем продолжать.

Однако же, существует еще и альтернативная малоизвестная система адресации, называемая «стилем R1C1». В этой системе и строки и столбцы обозначаются цифрами. Адрес ячейки B3 в такой системе будет выглядеть как R3C2 (R=row=строка, C=column=столбец). Относительные, абсолютные и смешанные ссылки в такой системе можно реализовать при помощи конструкций типа:

• RC — относительная ссылка на текущую ячейку
• R2C2 — то же самое, что $B$2 (абсолютная ссылка)
• RC5 — ссылка на ячейку из пятого столбца в текущей строке
• RC[-1] — ссылка на ячейку из предыдущего столбца в текущей строке
• RC[2] — ссылка на ячейку, отстоящую на два столбца правее в той же строке
• R[2]C[-3] — ссылка на ячейку, отстоящую на две строки ниже и на три столбца левее от текущей ячейки
• R5C[-2] — ссылка на ячейку из пятой строки, отстоящую на два столбца левее текущей ячейки
• и т.д.

Ничего суперсложного, просто слегка необычно.

Как это включить/отключить

Мало кто использует этот режим осознанно. Обычно он случайно включается сам, например, при открытии кривых выгрузок из 1С в Excel и в некоторых других ситуациях. Отключить его совсем несложно. Самый простой путь:

В Excel 2007/2010: кнопка Офис (Файл) — Параметры Excel — Формулы — Стиль ссылок R1C1 (File — Excel Options — Formulas — R1C1-style)

В Excel 2003 и старше: Сервис — Параметры — Общие — Стиль ссылок R1C1 (Tools — Options — General — R1C1-style)

Если вам приходится делать это часто, то имеет смысл создать простой макрос, переключающий эти два режима туда-обратно:

Sub ChangeRefStyle()
    If Application.ReferenceStyle = xlA1 Then
        Application.ReferenceStyle = xlR1C1
    Else
        Application.ReferenceStyle = xlA1
    End If
End Sub

Можно сохранить его в личную книгу макросов и повесить на кнопку на панели инструментов или на сочетание клавиш (как это сделать описано тут).

Где это может быть полезно

А вот это правильный вопрос. Если звезды зажигают, то это кому-нибудь нужно. Есть несколько ситуаций, когда режим ссылок R1C1 удобнее, чем классический режим А1:

• При проверке формул и поиске ошибок в таблицах иногда гораздо удобнее использовать режим ссылок R1C1, потому что в нем однотипные формулы выглядят не просто похоже, а абсолютно одинаково. Сравните, например, одну и ту же таблицу в режиме отладки формул (CTRL+~) в двух вариантах адресации:

Найти ошибку в режиме R1C1 намного проще, правда?

• Если большая таблица с данными на вашем листе начинает занимать уже по нескольку сотен строк по ширине и высоте, то толку от адреса ячейки типа BT235 в формуле немного. Видеть номер столбца в такой ситуации может быть гораздо полезнее, чем его же буквы.
• Некоторые функции Excel, например ДВССЫЛ (INDIRECT) могут работать в двух режимах — A1 или R1C1. И иногда оказывается удобнее использовать второй.
• В коде макросов на VBA часто гораздо проще использовать стиль R1C1 для ввода формул в ячейки, чем классический A1. Так, например, если нам надо сложить два столбца чисел по десять ячеек в каждом (A1:A10 и B1:B10,) то мы могли бы использовать в макросе простой код:

    Range("C1:C10").FormulaR1C1="=RC[-2]*RC[-1]"

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

For Each cell In Range("C1:C10")
    cell.Formula = "=" & cell.Offset(0, -2).Address & "*" & cell.Offset(0, -1).Address
Next cell

Ссылки по теме

• Что такое макросы, как их создавать, куда вставлять текст макроса на VBA?
• Зачем $ в адресах ячеек? Различные типы ссылок в формулах Excel.

Стили в Excel – это инструмент, который позволяет существенно упростить и ускорить процесс форматирования документа. Стилям форматирования можно дать определение как сохраненные под определенным названием готовых настроек форматов. Их можно легко присвоить одной или множеству ячеек.

Присвоение стилей форматирования ячейкам

В каждом стиле определены следующие настройки:

1. Шрифт (тип, размер, цвет и т.п.).
2. Формат отображения чисел.
3. Границы ячеек.
4. Заливка и узоры.
5. Защита ячеек.

Благодаря стилям все листы легко и быстро отформатировать. А если мы вносим изменение в стиль, то эти изменения автоматически присваиваются всем листам, которые им отформатированы.

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

Чтобы воспользоваться библиотекой встроенных стилей необходимо:

1. Виделите в указанной на рисунке таблице не отформатированную область ячеек, но без заголовка.



2. Выберите инструмент: «Главная»-«Стили»-«Стили ячейки»



3. Из выпадающего списка миниатюр предварительного просмотра стиля, выберите понравившейся Вам.



Создание пользовательского стиля по образцу

Теперь создадим свой пользовательский стиль, но по образцу уже готового:

1. Выделите первую строку таблицы, чтобы отформатировать ее заголовок.
2. Отобразите выпадающий список встроенных стилей и щелкните правой кнопкой мышки по «Заголовок 2». А из контекстного меню выберите опцию «Дублировать».



3. В диалоговом окне, укажите имя стиля «Новый заголовок» и, не изменяя настроек жмите на кнопку «Формат».



4. В появившемся окне «Формат ячеек» внесите свои изменения. На вкладке шрифт укажите темно-зеленый цвет. А шрифт измените на «курсив». Далее ОК и снова ОК.
5. Присвойте Ваш стиль на главной панели: «Стили ячейки»-«Пользовательские»-«Новый заголовок».

Таким образом, на основе встроенного стиля мы создали дубликат, который изменили под свои потребности.

Можно ли изменять стиль в Excel? В принципе можно было и не создавать дубликат, а в контекстном меню сразу выбрать «Изменить». Все изменения любого встроенного стиля по умолчанию, сохранились бы только в текущей книге. На настройки программы они не влияют. И при создании новой книги библиотека стилей отображается стандартно без изменений.

Можно ли удалить стиль в Excel? Конечно можно, но только в рамках одной книги. Например, как удалить пользовательские стили в Excel?

Выбираем желаемый стиль на главной панели: «Стили ячейки» и в разделе «Пользовательские» щелкаем правой кнопкой мышки. Из появившегося контекстного меню выберем опцию «Удалить». В результате ячейки очистятся от форматов заданных соответствующим стилем.

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

Создать стиль по формату ячейки

Но что если нужно создать стиль на основе пользовательского формата ячеек, который задан обычным способом.

1. Задайте пустой ячейке формат с изменением заливки и цвета шрифта.
2. Теперь выделите эту исходную ячейку и выберите инструмент на главной панели: «Стили ячейки»-«Создать стиль ячейки».
3. В окне «Стиль» укажите имя стиля «Как в ячейке1» и нажмите ОК.

Теперь у Вас в разделе стилей «Пользовательские» отображается имя «Как в ячейке1». Принцип понятен.

Все выше описанные стилевые форматы сохраняются в рамках файла текущей книги Excel. Поэтому для дальнейших действий сохраните эту книгу под названием «Урок о стилях.xlsx».

Копирование стиля в другие книги

Иногда возникает необходимость использовать текущие стили и в других книгах. Для этого можно просто скопировать их:

1. Создайте новую книгу, в которой будем использовать пользовательский стиль «Как в ячейке1».
2. Откройте книгу «Урок о стилях.xlsx» из сохраненным исходным требуемым нам стилем.
3. Выберите инструмент на главной закладке: «Стили ячейки»-«Объединить». И в окне «Объединение стилей» укажите на нужную нам книгу и ОК.

Теперь все пользовательские или измененные форматы из исходной книги скопированы в текущую.

Если Вам нужно будет часто использовать один стиль в разных книгах, тогда есть смысл создать специальную книгу со своими стилями и сохранить ее как шаблон. Это будет значительно удобнее чем каждый раз копировать… А все преимущества шаблонов подробнее рассмотрим на следующих уроках.

Форматирование в Excel – это очень утомительное, но важное задание. Благодаря стилям мы можем существенно ускорить и упростить данный рабочий процесс. Сохраняя при этом точную копию форматов ячеек в разных листах и книгах.

Многие пользователи программы Эксель привыкли к тому, что в качестве названий столбцов таблицы служат латинские буквы. Однако иногда может случиться такое, что вместо букв отображаются цифры подобно нумерации строк.

Такое возможно по ряду причин:

• программные сбои;
• пользователь сам изменил соответствующую настройку, но не заметил, как это сделал или забыл.
• возможно, другой пользователь, работающий с таблицей внес изменения в настройки.

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

Смена обозначений столбцов

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

Метод 1: настройка параметров программы

Данный метод предполагает внесение изменений в параметры программы. Вот, что нужно сделать:

1. Кликаем по меню “Файл”.
2. В открывшемся окне в перечне слева в самом низу щелкаем по пункту “Параметры”.
3. На экране отобразится окно с параметрами программы:
   • переключаемся в раздел “Формулы”;
   • в правой стороне окна находим блок настроек “Работа с формулами” и убираем флажок напротив опции “Стиль ссылок R1C1”.
   • нажимаем кнопку OK, чтобы подтвердить изменения.
4. Все готово. Благодаря этим достаточно простым и быстрореализуемым действиям мы вернули привычные обозначения в таблицу.

Примечание: стиль ссылок R1C1 – это и есть параметр, включение которого меняет латинские буквы на цифры на горизонтальной координатной панели.

Метод 2: настройки в Режиме разработчика

Прежде, чем приступить к выполнению действий, описанных в данном методе, необходимо активировать режим Разработчика, так как по умолчанию он выключен.

• заходим в параметры программы (порядок действий описан выше);
• переходим в раздел “Настроить ленту”, в правой части окна находим пункт “Разработчик”, ставим напротив него галочку и щелкаем OK.

Теперь можно перейти к основному алгоритму действий:

1. Переходим во вкладку “Разработчик”, в левой части которой щелкаем по кнопке “Visual Basic”. Также, вместо этого можно воспользоваться комбинацией клавиш Alt+F11.
2. В открывшемся редакторе нажимаем комбинацию Ctrl+G, что позволит переместить курсор в область “Immediate”. Пишем в ней команду Application.ReferenceStyle=xlA1 и нажимаем Enter. Примечание: в процессе набора команды программа будет нам помогать с вариантами, как при ручном наборе формул в ячейке.
3. Можно закрывать окно редактора Visual Basic. В таблицу должны были вернуться буквенные обозначения столбцов.

Заключение

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