Как изменить формат формулы в Excel
Обновлено: 14.05.2024
Содержимое ячейки, содержащей формулу, можно преобразовать так, чтобы вычисляемое значение заменяло формулу. Если вы хотите заморозить только часть формулы, вы можете заменить только ту часть, которую не хотите пересчитывать. Замена формулы ее результатом может быть полезна, если в книге много или сложные формулы и вы хотите повысить производительность за счет создания статических данных.
Вы можете преобразовывать формулы в их значения как для каждой ячейки, так и для всего диапазона сразу.
Важно! Обязательно изучите последствия замены формулы ее результатами, особенно если формулы ссылаются на другие ячейки, содержащие формулы. Рекомендуется сделать копию рабочей книги, прежде чем заменять формулу ее результатами.
В этой статье не рассматриваются параметры и методы расчета. Чтобы узнать, как включить или отключить автоматический пересчет для рабочего листа, см. статью Изменение пересчета формулы, итерации или точности.
Заменить формулы их расчетными значениями
При замене формул их значениями Excel навсегда удаляет формулы. Если вы случайно заменили формулу значением и хотите восстановить формулу, нажмите «Отменить» сразу после ввода или вставки значения.
Выберите ячейку или диапазон ячеек, содержащих формулы.
Если формула представляет собой формулу массива, выберите диапазон, содержащий формулу массива.
Как выбрать диапазон, содержащий формулу массива
Нажмите ячейку в формуле массива.
На вкладке "Главная" в группе "Редактирование" нажмите "Найти и выбрать", а затем нажмите "Перейти".
Нажмите "Специальные".
Нажмите Текущий массив.
Нажмите "Копировать" .
Нажмите "Вставить" .
Нажмите стрелку рядом с параметром "Параметры вставки" и выберите "Только значения".
В следующем примере показана формула в ячейке D2, которая умножает ячейки A2, B2 и скидку, полученную из C2, для расчета суммы счета-фактуры для продажи. Чтобы скопировать фактическое значение вместо формулы из ячейки на другой лист или книгу, вы можете преобразовать формулу в своей ячейке в ее значение, выполнив следующие действия:
Нажмите F2, чтобы изменить ячейку.
Нажмите F9, а затем нажмите клавишу ВВОД.
После преобразования ячейки из формулы в значение значение отображается как 1932,322 в строке формул. Обратите внимание, что 1932,322 — это фактическое расчетное значение, а 1932,32 — это значение, отображаемое в ячейке в денежном формате.
Совет. Когда вы редактируете ячейку, содержащую формулу, вы можете нажать F9, чтобы навсегда заменить формулу ее вычисленным значением.
Заменить часть формулы ее вычисленным значением
Могут быть случаи, когда вы хотите заменить только часть формулы ее вычисленным значением. Например, вы хотите зафиксировать значение, которое используется в качестве первоначального взноса для автокредита. Этот первоначальный взнос был рассчитан на основе процента от годового дохода заемщика. В настоящее время эта сумма дохода не изменится, поэтому вы хотите зафиксировать авансовый платеж в формуле, которая вычисляет платеж на основе различных сумм кредита.
При замене части формулы ее значением эта часть формулы не может быть восстановлена.
Нажмите ячейку, содержащую формулу.
В строке формул выберите часть формулы, которую вы хотите заменить вычисленным значением. Когда вы выбираете часть формулы, которую хотите заменить, убедитесь, что вы включили весь операнд. Например, если вы выбираете функцию, вы должны выбрать полное имя функции, открывающую скобку, аргументы и закрывающую скобку.
Чтобы рассчитать выбранную часть, нажмите F9.
Чтобы заменить выбранную часть формулы ее вычисленным значением, нажмите клавишу ВВОД.
В Excel для Интернета результаты уже отображаются в ячейке книги, а формула отображается только в строке формул.
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
Условное форматирование быстро выделяет важную информацию в электронной таблице. Но иногда встроенных правил форматирования недостаточно. Добавление собственной формулы к правилу условного форматирования делает его более мощным, помогая делать то, что не могут сделать встроенные правила.
Например, предположим, что вы отслеживаете дни рождения своих стоматологических пациентов, чтобы узнать, чей из них приближается, а затем отметить их как получивших от вас поздравление с днем рождения.
На этом листе мы видим нужную нам информацию с помощью условного форматирования, основанного на двух правилах, каждое из которых содержит формулу. Первое правило в столбце A форматирует будущие дни рождения, а правило в столбце C форматирует ячейки сразу после ввода "Y", указывая на то, что поздравление с днем рождения отправлено.
Чтобы создать первое правило:
Выберите ячейки с A2 по A7. Для этого перетащите от A2 до A7.
На вкладке "Главная" нажмите "Условное форматирование" > "Новое правило".
В поле "Стиль" выберите "Классический".
В разделе "Классический" выберите "Форматировать только верхние или нижние значения" и измените его на "Использовать формулу для определения форматируемых ячеек".
В следующем поле введите формулу: =A2>СЕГОДНЯ()
В формуле используется функция СЕГОДНЯ, чтобы определить, больше ли даты в столбце А, чем сегодня (в будущем). Если это так, ячейки форматируются.
В поле "Форматировать с помощью" нажмите "Пользовательский формат".
В диалоговом окне "Формат ячеек" перейдите на вкладку "Шрифт".
В поле "Цвет" выберите "Красный". В поле Стиль шрифта выберите Жирный.
Нажимайте OK, пока диалоговые окна не закроются.
Форматирование применяется к столбцу A.
Чтобы создать второе правило:
Выберите ячейки с C2 по C7.
На вкладке "Главная" нажмите "Условное форматирование" > "Новое правило".
В поле "Стиль" выберите "Классический".
В разделе "Классический" выберите "Форматировать только верхние или нижние значения" и измените его на "Использовать формулу для определения форматируемых ячеек".
В следующем поле введите формулу: =C2="Y"
Формула проверяет, содержат ли ячейки в столбце C букву "Y" (кавычки вокруг буквы Y указывают Excel, что это текст). Если это так, ячейки форматируются.
В поле "Форматировать с помощью" нажмите "Пользовательский формат".
Вверху нажмите вкладку "Шрифт".
В поле "Цвет" выберите "Белый". В поле «Стиль шрифта» выберите «Полужирный».
Вверху нажмите вкладку "Заливка", а затем для параметра "Цвет фона" выберите "Зеленый".
Нажимайте OK, пока диалоговые окна не закроются.
Форматирование применяется к столбцу C.
Попробуйте
В приведенных выше примерах мы использовали очень простые формулы условного форматирования. Поэкспериментируйте сами и используйте другие формулы, с которыми вы знакомы.
Вот еще один пример, если вы хотите перейти на следующий уровень. Введите следующую таблицу данных в свою рабочую книгу. Начните с ячейки A1. Затем выберите ячейки D2:D11 и создайте новое правило условного форматирования, использующее следующую формулу:
При создании правила убедитесь, что оно применяется к ячейкам D2:D11. Установите формат цвета, который будет применяться к ячейкам, соответствующим критериям (то есть в столбце D есть более одного экземпляра города — Сиэтл и Спокан).
< бр />
Лори Кауфман
Лори Кауфман
Писатель
Лори Кауфман – специалист по технологиям с 25-летним опытом работы. Она была старшим техническим писателем, работала программистом и даже управляла собственным бизнесом в нескольких местах. Подробнее.
Обычно в Excel используется стиль ссылок на ячейки A1, в котором буквы используются для заголовков столбцов, а цифры — для заголовков строк. Однако, если вы заметили числа как в заголовках строк, так и в заголовках столбцов, это стиль ссылок на ячейки R1C1. Не волнуйтесь. Его легко вернуть обратно.
Чтобы изменить стиль ссылки на ячейку R1C1 обратно на стиль A1, мы изменим параметр в параметрах. Перейдите на вкладку «Файл».
На экране Backstage нажмите «Параметры» в списке элементов слева.
В диалоговом окне «Параметры Excel» нажмите «Формулы» в списке элементов слева.
В разделе "Работа с формулами" установите флажок "Стиль ссылок R1C1", чтобы в нем не было галочки.
Нажмите "ОК", чтобы принять изменение и закрыть диалоговое окно "Параметры Excel".
Теперь в заголовках столбцов должны быть буквы.
Когда вы меняете стиль ссылки на ячейку, он изменяется для всех рабочих листов в текущей книге. Если вы сохраните книгу с этим изменением, любые другие книги, которые вы открываете, или новые книги, которые вы создаете, будут использовать тот же стиль ссылок на ячейки. Если вы закроете книгу после внесения этого изменения и не внесли никаких других изменений, вам НЕ будет предложено сохранить изменения.
Обратите внимание, что раскрывающийся список слева от строки формул показывает, какой стиль ссылки на ячейку используется. Может показаться, что вы можете изменить стиль ссылки на ячейку с помощью этого раскрывающегося списка, но мы обнаружили, что по какой-то причине он не работает.
- › Как использовать окно наблюдения Microsoft Excel для формул
- › Как пронумеровать строки в Microsoft Excel
- › Как добавлять числа в Microsoft Excel
- › Почему не было Windows 9?
- › Почему прозрачные чехлы для телефонов желтеют?
- › Как восстановить метки панели задач в Windows 11
- ›5 шрифтов, которые следует прекратить использовать (и лучшие альтернативы)
- › Что означает XD и как вы его используете?
В этом примере я покажу вам, как можно отформатировать ячейку так, чтобы ее числовой формат переключался между денежным и безденежным форматом.
Мы также можем изменить форматирование ячейки на жирный и обычный в зависимости от условия.
Все, что вам нужно, это немного условного форматирования.
В этом видео задается вопрос: "Может ли одна ячейка иметь несколько настраиваемых форматов в Excel?"
Ответ: Да! На самом деле у него может быть два, три или столько пользовательских форматов, сколько вы пожелаете.
Как изменить числовой формат на основе условия?
Чтобы продемонстрировать эту возможность, мы создадим простой тестовый отчет, который позволит пользователю выбирать между общим объемом продаж и количеством приложений в списке приложений.
Обратите внимание на разницу в формате ответов.
Когда пользователь выбирает "Общий объем продаж", к ответу применяется стиль валюты.
Когда пользователь выбирает количество приложений, к ответу применяется цифровой стиль.
К одной и той же ячейке применяются разные инструкции по форматированию в зависимости от отображаемого ответа.
Посмотрите на образец набора данных.
На вкладке "Данные" у нас есть список приложений и связанных с ними продаж. Мы построим наш мини-отчет на вкладке «Отчет».
Первый шаг — создать раскрывающийся список "Проверка данных", чтобы пользователь мог выбрать один из двух расчетов (Продажи и количество приложений).
На листе отчета выберите ячейку (например, C4) и создайте раскрывающийся список, выбрав «Данные» (вкладка) — «Инструменты данных» (группа) — «Проверка данных». Установите проверку данных, чтобы разрешить список. В поле Источник введите «Общий объем продаж, количество приложений».
Проверьте правильность работы раскрывающегося списка и задайте ячейке светло-зеленую заливку, чтобы выделить ее визуально.
Теперь мы создадим динамический числовой формат в ячейке непосредственно под раскрывающимся списком проверки данных (например, C5).
Мы не хотим задавать числовой формат непосредственно в ячейке, потому что форматирование будет применяться к ячейке независимо от отображаемого ответа. В обычных условиях на форматирование ячейки C5 не может повлиять выбор, сделанный в ячейке C4.
Условное форматирование в помощь
Условное форматирование будет предпочтительным инструментом для управления стилем чисел, применяемым к ячейке C5, на основе параметра, выбранного в ячейке C4.
Построение логики
Прежде чем мы сможем создать формат динамических чисел, нам нужно создать формулу, которая будет выбирать расчет на основе параметра, выбранного в раскрывающемся списке.
В ячейке C5 введите следующую формулу:
=ЕСЛИ(C4="Общий объем продаж", СУММ(Данные!B3:B29),СЧЁТ(Данные!A3:A29))
Логика приведенной выше функции ЕСЛИ заключается в том, чтобы определить, отображается ли в ячейке C4 текст «Общий объем продаж». Если это так, мы суммируем все продажи в столбце B листа данных.Если в ячейке C4 НЕ отображается текст «Общий объем продаж», мы можем сделать вывод, что в ячейке отображается текст «Количество приложений». Мы могли бы подсчитать все продажи, но если у приложения нет продаж, оно будет исключено из операции подсчета. Вместо этого мы будем считать приложения в столбце A листа данных. Мы хотим подсчитать все ячейки независимо от их содержимого (т. е. чисел или слов); для этой цели лучше всего использовать функцию COUNTA.
Проверьте ответы, выбрав различные варианты в ячейке C4.
Если в раскрывающемся списке есть более двух вариантов, вам потребуется создать вложенную формулу ЕСЛИ, используя несколько функций ЕСЛИ.
Если вам нужно освежить знания о создании вложенных функций ЕСЛИ, посмотрите видео под названием Формула ЕСЛИ в Excel: от простого к сложному (множество критериев, вложенные функции ЕСЛИ, И, ИЛИ).
Вернуться к условному форматированию
На этом этапе мы будем использовать условное форматирование для создания и управления различными стилями чисел для разных ответов.
Выберите ячейку C5, а затем выберите Главная (вкладка) – Стили (группа) – Условное форматирование – Новое правило.
В диалоговом окне "Новое правило форматирования" выберите "Использовать формулу для определения форматируемых ячеек". Это позволит нам проверить выбранное значение в ячейке C4, чтобы изменить форматирование числа в ячейке C5. Мы напишем формулу…
Если эта формула приводит к истинному условию, мы отформатируем ячейку C5, выбрав кнопку «Формат…» и установив следующий параметр отображения.
Выберите «Формат» — «Число» — «Валюта» — «Ноль десятичных разрядов» (нажмите «ОК», когда закончите, чтобы закрыть диалоговое окно)
Протестируйте функцию, выбрав различные параметры в раскрывающемся списке в ячейке C4. Когда мы выбираем Total Sales, ответ будет в валютном стиле. Когда мы выбираем количество приложений, число отображается в исходном десятичном формате.
Мы хотели бы, чтобы ответ "Количество приложений" был выделен жирным шрифтом.
Выберите ячейку B5 и установите для нее полужирный шрифт.
Проблема в том, что ячейка выделена жирным шрифтом независимо от отображаемого числа.
Чтобы не использовать полужирный шрифт при отображении общего объема продаж, выберите Главная (вкладка) — Стили (группа) — Условное форматирование — Управление правилами.
В диспетчере правил условного форматирования выберите «Изменить правило».
Выберите «Формат» в диалоговом окне «Редактировать правило форматирования».
В диалоговом окне «Формат ячеек» выберите вкладку «Шрифт» и выберите «Обычный» в списке «Стиль шрифта». Нажмите OK, когда закончите.
Обратите внимание, что если в ячейке C4 указано "Общий объем продаж", ответ в ячейке C5 выделен полужирным шрифтом.
Условное форматирование переопределяет этот параметр и возвращает ячейку обратно в обычный формат.
Создание динамического списка источников данных
Мы хотели бы иметь возможность добавлять новые приложения в список приложений на листе данных, и нам не нужно было бы постоянно обновлять формулы на листе отчета, чтобы они соответствовали. Нам просто нужно «обновить» обычную таблицу в таблице данных до официальной таблицы Excel.
Выберите любое место в данных на листе данных и нажмите CTRL-T (или выберите Главная (вкладка) — Стили (группа) — Форматировать как таблицу). В диалоговом окне "Создать таблицу" нажмите "ОК".
Если мы добавим новую запись в конец списка приложений, мы сразу же увидим, что диапазон таблицы расширится, чтобы поглотить новую запись.
Если мы вернемся к листу отчета, мы увидим, что формула обновилась и теперь включает данные новой строки.
=ЕСЛИ(C4="Общий объем продаж", СУММ(Данные!B3:B30),СЧЁТ(Данные!A3:A30))
То, что раньше заканчивалось в строке 29, теперь заканчивается в строке 30.
Если мы переключимся на количество приложений, мы увидим обновленное число (с 27 на 28).
Читайте также: