Как в excel изменить вид формулы: Как изменить стиль ссылок R1C1 на обычные A1 в программе Excel

Как заменить числа на буквы в названиях столбцов Microsoft Excel

Виктор Бухтеев

10 дек в 2021

39K

Обсудить

По умолчанию столбцы в Microsoft Excel имеют буквенное обозначение, которое используется вместе со строками для обозначения ячеек в функциях и при разметке таблицы. Некоторым пользователям такой подход не нравится – они предпочли бы видеть цифры на всей панели координат. Об этом изменении и пойдет речь далее.

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

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

Способ 1: Настройка параметра «Стиль ссылок»

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

1. Откройте любой лист и перейдите на вкладку «Файл».

2. На панели слева щелкните по надписи «Параметры».

3. Перейдите к разделу «Формулы» и активируйте галочку возле пункта «Стиль ссылок R1C1». Соответственно, если вам нужно убрать цифры сверху, галочку эту надо снять.

4. По необходимости наведите курсор на значок с информацией рядом со строкой, чтобы получить более детальное описание от разработчиков.

5. Перед выходом обязательно нажмите «ОК», применив тем самым изменения.

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

1. Теперь при создании функции вам нужно указать R для строки и задать номер клетки по счету.

2. Далее укажем C и номер, обозначив столбец. После введения данных ячейка должна выделиться, что обозначает ее корректный выбор.

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

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

Способ 2: Использование макроса

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

1. Если вкладка «Разработчик» у вас еще не активирована, перейдите в «Параметры».

2. Выберите раздел «Настроить ленту» и отметьте галочкой соответствующий пункт, после чего примените изменения.

3. Перейдите на ту самую вкладку и щелкните ЛКМ по «Visual Basic».

4. Используйте сочетание клавиш Ctrl + G для вызова окна редактора.

5. Вставьте туда строку Application.ReferenceStyle=xlA1, если хотите вернуть буквы вместо цифр на верхней панели.

6. Для замены букв на цифры используйте другую строку: Application.ReferenceStyle=xlr1c1.

7. Обязательно нажмите Enter для применения изменений, после чего вернитесь к таблице и проверьте, как сейчас отображается система координат.

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

Личный опыт

Наши постоянные авторы и читатели делятся лайфхаками, основанными на личном опыте. Полная свобода самовыражения.

Рекомендуем

Ссылки R1C1

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: ссылки в стиле R1C1 полезны в нескольких ситуациях, в частности, при использовании VBA, функции ДВССЫЛ (см., например, последний раздел заметки Ссылка на другой лист с помощью ДВССЫЛ) и условного форматирования. Поэтому краткое знакомство с ними будет полезно.

До появления платформы IBM PC существовало несколько популярных приложений электронных таблиц. Это были и VisiCalc и Quattro Pro и Multiplan. Впервые стиль А1 именования ячеек был представлен в VisiCalc. Но этот продукт быстро проиграл конкурентную гонку. Multiplan выпускался компанией Microsoft до выхода Excel. В этом продукте для адресации ячеек использовался формат R1C1, который с тех пор доступен и в Excel. Но именно Лотус 1-2-3 вырвался на вершину славы сразу же после выхода в 1982 г. и стал доминировать на рынке приложений электронных таблиц для ПК. Не в последнюю очередь благодаря удобной системе именования ячеек А1. [1]

Во время войн электронных таблиц, Microsoft осознала, что большинство пользователей голосуют за стиль ссылок А1, и чтобы конкурировать, ей придется делать вид, что в Excel используются ссылки А1. Конечно, Excel только делает вид, что использует А1. В действительности «за кулисами» работают R1C1. Если вы не верите мне, пройдите по меню ФАЙЛ –> Параметры, перейдите на вкладку Формулы, и в области Работа с формулами поставьте галочку Стиль ссылок R1C1 (рис. 1).

Рис. 1. Вы всего в одном клике от стиля ссылок R1C1

Скачать заметку в формате Word или pdf, примеры в формате Excel

Обратите внимание, что я не призываю вас перейти на стиль ссылок R1C1. Я не такой сумасшедший, как Microsoft, которая при переходе с Excel 2003 на Excel 2007 полностью заменила привычное меню. Вместо этого, я предлагаю вам изучить стиль R1C1, чтобы применять его, когда это уместно.

Решение: стиль ссылок R1C1 содержит буквы R (row – строка) и С (column – столбец). Без каких-либо чисел (т.е. в виде RC) R означает «та же строка, в которой введена формула» и C означает «тот же столбец, в который введена формула». Таким образом, простейшая ссылка в стиле R1C1 – это =RC. Если вы находитесь в ячейке C10 и введете =RC, вы имеете ввиду ячейку С10. Осторожно! Это приведет к возникновению ошибки – циклической ссылки.

Когда в дополнение к буквам R и C используются числа в квадратных скобках, вы ссылаетесь на ячейку, отстоящую от данной на указанное число ячеек. Например, формула =RC[-1] введенная в С10 ссылается на В10, а =R[10]C в С10 – на С20. Вы можете одновременно изменить, и строку, и столбец. Например, формула =R[1]С[1] введенная в С10 ссылается на D11.

Примечание: для строк положительные числа направляют вниз листа, отрицательные – вверх. Для столбцов положительные числа направляют вправо, отрицательные – влево.

Если вы хотите построить формулу для расчета рентабельности (GP) в колонке I, вы должны разделить значение в соответствующей строке столбца H на значение в столбце F (рис. 2). Видно, что формулы в столбце I разные в каждой строке.

Рис. 2. Со ссылками в стиле А1 (верхний рисунок) формулы в каждой ячейке разные, со ссылками в стиле R1C1 (нижний рисунок) формулы одинаковые.

А что если перейти к ссылкам в стиле R1C1? Вам нужно разделить значение в столбце на один левее текущего, на значение в столбце на три левее текущего. Вы можете использовать одну и туже формулу, независимо от того, в какой строке вы находитесь: =RC[-1]/RC[-3].

А что с абсолютными ссылками в стиле R1C1? Формулы в столбце J (рис. 3) гарантируют, что вы всегда используете налоговую ставку из ячейки М1. Для указания абсолютной ссылки в стиле R1C1, опустите квадратные скобки и укажите не смещение от текущей строки, а абсолютные номера строки и столбца. Например, ячейка $М$1 – это 13-й столбец строки 1, поэтому, используя R1C13.

Рис. 3. Абсолютные ссылки в стиле А1 (верхний рисунок) и R1C1 (нижний рисунок)

Резюме: ссылки R1C1 пригодятся вам при записи макросов, программировании VBA, использовании функции ДВССЫЛ и условном форматировании.

[1] Цитируется по изданию Пол Киммел и др. Excel 2003 и VBA. Справочник программиста.

Изменить формат ячейки

Форматирование

• Доступные числовые форматы в Excel
  

  Статья

• Условное форматирование
  

  Статья

• Выравнивание или поворот текста в ячейке
  

  Статья

• Изменить формат ячейки
  

  Статья

• Копировать форматирование ячейки
  

  Статья

• Добавить водяной знак в Excel
  

  Статья

• Показать или скрыть нулевые значения
  

  Статья

• Создание пользовательского числового формата
  

  Статья

Следующий:

Формулы и функции

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

Официальные ячейки

1. Выберите ячейки.

2. Перейдите на ленту, чтобы выбрать изменения как Полужирный , Цвет шрифта или Размер шрифта .

Применить стили Excel

1. Выберите ячейки.

2. Выберите Главная > Стиль ячейки  и выберите стиль.

Изменение стиля Excel

1. Выберите ячейки со стилем Excel.

2. Щелкните правой кнопкой мыши примененный стиль в Главная > Стили ячеек .

3. Выберите Изменить > Формат , чтобы изменить то, что вы хотите.  

Нужна дополнительная помощь?

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

См. также

Форматировать текст в ячейках

Формат чисел

Отформатируйте дату так, как вы хотите

Как изменить формат даты Excel и создать собственное форматирование

Первая часть нашего руководства посвящена форматированию дат в Excel и объясняет, как установить формат даты и времени по умолчанию, как изменить формат даты в Excel, как создать пользовательский форматирование даты и преобразование дат в другую локаль.

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

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

Формат даты Excel

Прежде чем вы сможете воспользоваться мощными функциями работы с датами Excel, вы должны понять, как Microsoft Excel хранит даты и время, так как это является основным источником путаницы. Хотя вы ожидаете, что Excel запомнит день, месяц и год для даты, это не так…

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

Даты в Excel

Все даты хранятся в виде целых чисел , представляющих количество дней с 1 января 1900 года, которое хранится как число 1, до 31 декабря 9999 года, которое хранится как 2958465.

В этой системе:

• 2 2 января 1900
• 3 — 3 января 1900 г.
• 42005 — 1 января 2015 г. (поскольку это 42 005 дней после 1 января 1900 г.)

Время в Excel

Время хранится в Excel в виде десятичных дробей от 0,0 до 0,99999, представляющих долю дня, где 00 – 00:00:00, а 00 – 00:00:00.9999 — это 23:59:59.

Например:

• 0,25 06:00
• 0.5 в 12:00
• 0.541655093 в 12:59:59

Даты и время в Excel

Excel хранит даты и время в виде десятичных чисел, состоящих из целого числа, представляющего дату, и десятичной части, представляющей время.

Например:

• 1.25 — 1 января 1900 г. 6:00 утра
• 42005.5 — 1 января 2015 г., 12:00

.

Как преобразовать дату в число в Excel

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

1. Диалоговое окно «Формат ячеек»

Выберите ячейку с датой в Excel, нажмите Ctrl+1, чтобы открыть окно Формат ячеек и перейдите на вкладку Общие .

Если вы просто хотите узнать серийный номер, стоящий за датой, без фактического преобразования даты в число, запишите число, которое вы видите под цифрой 9.0111 Образец и щелкните Отмена , чтобы закрыть окно. Если вы хотите заменить дату числом в ячейке, нажмите кнопку ОК.

2. Функции Excel ДАТАЗНАЧ и ВРЕМЯЗНАЧ

Используйте функцию ЗНАЧДАТА() для преобразования даты Excel в порядковый номер, например =ЗНАЧДАТА("1/1/2015") .

Используйте функцию ЗНАЧ ВРЕМЕНИ(), чтобы получить десятичное число, представляющее время, например, =ЗНАЧ ВРЕМЕНИ("6:30 AM") .

Чтобы узнать и дату, и время, соедините эти две функции следующим образом:

=ДАТАЗНАЧ("1/1/2015") И ВРЕМЯЗНАЧ("6:00")

Примечание. Поскольку серийные номера Excel начинаются с 1 января 1900 года, а отрицательные числа не распознаются, даты до 1900 года не поддерживаются в Excel.

Если вы введете такую ​​дату в лист, скажем, 31.12.1899, это будет текстовое значение, а не дата, а это означает, что вы не можете выполнять обычную арифметику дат с более ранними датами. Чтобы убедиться, вы можете ввести формулу =ДАТАЗНАЧ("31.12.1899") в какой-то ячейке, и вы получите ожидаемый результат — ошибку #ЗНАЧ!.

Если вы имеете дело со значениями даты и времени и хотите преобразовать время в десятичное число , ознакомьтесь с формулами, описанными в этом руководстве: Как преобразовать время в десятичное число в Excel.

Формат даты по умолчанию в Excel

При работе с датами в Excel краткий и длинный форматы даты извлекаются из региональных настроек Windows. Эти форматы по умолчанию отмечены звездочкой (*) в строке 9.0111 Формат ячейки диалоговое окно:

Форматы даты и времени по умолчанию в поле Формат ячейки изменяются, как только вы изменяете настройки даты и времени в панели управления, что приводит нас прямо к следующему разделу.

Как изменить форматы даты и времени по умолчанию в Excel

Если вы хотите установить другой формат даты и/или времени по умолчанию на вашем компьютере, например изменить формат даты США на стиль Великобритании, перейдите на панель управления и нажмите Регион и язык . Если панель управления открывается в представлении по категориям, нажмите Часы, язык и регион > Регион и язык > Изменить дату, время или числовой формат .

На вкладке Форматы выберите регион под Формат , а затем установите форматирование даты и времени, щелкнув стрелку рядом с форматом, который вы хотите изменить, и выбрав нужный из выпадающего списка:

Совет. Если вы не уверены, что означают разные коды (например, mmm, ddd, yyy), щелкните ссылку « Что означает обозначение » в разделе Форматы даты и времени или проверьте Пользовательские форматы даты Excel в этом руководство.

Если вас не устраивает какой-либо формат времени и даты, доступный на вкладке Форматы , нажмите кнопку Дополнительные настройки в нижней правой части диалогового окна Регион и язык . Это откроет Диалоговое окно «Настроить », где вы переключаетесь на вкладку Дата и вводите пользовательский короткий и/или длинный формат даты в соответствующем поле.

Как быстро применить формат даты и времени по умолчанию в Excel

Microsoft Excel имеет два формата даты и времени по умолчанию — короткий и длинный, как описано в формате даты Excel по умолчанию.

Чтобы быстро изменить формат даты в Excel на формат по умолчанию, выполните следующие действия:

• Выберите даты, которые вы хотите отформатировать.
• На вкладке Домашний в группе Номер щелкните маленькую стрелку рядом с полем Формат номера и выберите нужный формат — краткая дата, длинная дата или время.

Если вам нужны дополнительные параметры форматирования даты, либо выберите Дополнительные числовые форматы из раскрывающегося списка, либо щелкните кнопку запуска диалогового окна рядом с номером . Откроется знакомое диалоговое окно Format Cells , в котором вы сможете изменить формат даты.

Совет. Если вы хотите быстро установить формат даты в Excel на dd-mmm-yy , нажмите Ctrl+Shift+#. Просто имейте в виду, что этот ярлык всегда применяет формат dd-mmm-yy , например 01-Jan-15, независимо от настроек региона Windows.

Как изменить формат даты в Excel

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

1. Выберите даты, формат которых вы хотите изменить, или пустые ячейки, в которые вы хотите вставить даты.
2. Нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек . Кроме того, вы можете щелкнуть правой кнопкой мыши выбранные ячейки и выбрать «Формат ячеек … » в контекстном меню.
3. В окне Формат ячеек перейдите на вкладку Номер и выберите Дата в списке Категория .
4. В поле введите , выберите нужный формат даты. Как только вы это сделаете, в поле Sample отобразится предварительный просмотр формата с первой датой в выбранных вами данных.
5. Если вас устраивает предварительный просмотр, нажмите кнопку OK , чтобы сохранить изменение формата и закрыть окно.

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

Как преобразовать формат даты в другой язык

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

Самый простой способ изменить формат даты в Excel на основе того, как даты отображаются на другом языке, выглядит следующим образом:

• Выберите столбец дат, который вы хотите преобразовать в другую локаль.
• Нажмите Ctrl+1, чтобы открыть формат ячеек
• Выберите нужный язык в разделе «Язык» (местоположение) и нажмите «ОК», чтобы сохранить изменения.

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

Создание пользовательского формата даты в Excel

Если ни один из предопределенных форматов даты Excel вам не подходит, вы можете создать свой собственный.

1. На листе Excel выберите ячейки, которые необходимо отформатировать.
2. Нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек .
3. На вкладке Номер выберите Пользовательский из списка Категория и введите нужный формат даты в поле Введите .
4. Нажмите OK, чтобы сохранить изменения.

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

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

Код	Описание	Пример (1 января 2005 г.)
м	Номер месяца без ведущего нуля	1
мм	Номер месяца с нулем в начале	01
ммм	Название месяца, краткая форма	Январь
мммм	Название месяца, полная форма	января
ммммм	Месяц как первая буква	J (обозначает январь, июнь и июль)
д	Номер дня без ведущего нуля	1
дд	Номер дня с начальным нулем	01
ддд	День недели, короткая форма	Пн
дддд	День недели, полная форма	Понедельник
г. г.	Год (последние 2 цифры)	05
гггг	Год (4 цифры)	2005

При настройке пользовательский формат времени в Excel, вы можете использовать следующие коды.

Чтобы настроить формат даты и времени , включите в код формата как дату, так и время, например м/д/гггг ч:мм AM/PM. Когда вы используете « m » сразу после » hh » или » h » или непосредственно перед «ss» или «s», Excel отобразит минуты , а не месяц.

При создании пользовательского формата даты в Excel можно использовать запятую (,), тире (-), косую черту (/), двоеточие (:) и другие символы.

Например, одна и та же дата и время, скажем 13 января 2015 13:03 , могут отображаться по-разному:

Как создать пользовательский формат даты Excel для другой локали

Если вы хотите отображать даты на другом языке, вам необходимо создать собственный формат и добавить к дате префикс соответствующего кода локали. Код локали должен быть заключен в [квадратные скобки] со знаком доллара ($) и дефисом (-). Вот несколько примеров:

• [$-409] — английский, без названия штаты
• [$-1009] — английский, Канада
• [$-407] — немецкий, Германия
• [$-807] — немецкий, Швейцария
• [$-804] — Бенгальский, Индия
• [$-804] — Китайский, Китай
• [$-404] — Китай, Тайвань

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

Например, так вы настраиваете пользовательский формат даты Excel для китайской локали в формате год-месяц-день (день недели) формат:

На следующем изображении показаны несколько примеров одной и той же даты, отформатированной с разными кодами локали традиционным для соответствующих языков способом:

Формат даты Excel не работает — исправления и решения

Обычно Microsoft Excel очень хорошо понимает даты, и вы вряд ли столкнетесь с какими-либо препятствиями при работе с ними. Если у вас возникла проблема с форматом даты в Excel, ознакомьтесь со следующими советами по устранению неполадок.

Ячейка недостаточно широка, чтобы вместить всю дату

Если вы видите несколько знаков решетки (#####) вместо дат на листе Excel, скорее всего, ваши ячейки недостаточно широки, чтобы вместить все даты.

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

Отрицательные числа форматируются как даты

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

Если вы хотите отображать отрицательные числа как отрицательные даты, вам доступны две опции:

Решение 1. Переключиться на систему дат 1904 года.

Перейти к Файл > Параметры > Расширенный , прокрутите вниз до раздела При расчете этой рабочей книги , установите флажок Использовать систему дат 1904 и нажмите OK .

В этой системе 0 соответствует 1 января 1904 года; 1 — 2 января 1904 г.; а -1 отображается как отрицательная дата -2 января 1904 года.

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

Решение 2. Используйте функцию ТЕКСТ Excel.

Другой возможный способ отображения отрицательных чисел в виде отрицательных дат в Excel — использование функции ТЕКСТ. Например, если вы вычитаете C1 из B1, а значение в C1 больше, чем в B1, вы можете использовать следующую формулу для вывода результата в формате даты:

=ТЕКСТ(ABS(B1-C1),"-d-ммм-гггг")

Возможно, вы захотите изменить выравнивание ячеек по правому краю, и, естественно, вы можете использовать любые другие пользовательские форматы даты в формуле ТЕКСТ.