Где находится автоформат в excel 2019 – 27.Форматы ячеек и создание таблиц в Excel. Автоформат и условное форматирование. Основные приемы работы в ms excel.

27.Форматы ячеек и создание таблиц в Excel. Автоформат и условное форматирование. Основные приемы работы в ms excel.

Форматы ячеек

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

Примеры форматов:

Содержимое ячейки	Формат	Результат
1234,567	Числовой	1 234,567
1234,567	Денежный	1 234,567р.
1234,567	Процентный	123456,7%
1234,567	Экспоненциальный	1,235Е+03
123456	Время	12:34:56

Присвоить формат ячейке или блоку ячеек, предварительно выделив их, можно с помощью команды Ячейки из меню Формат или нажав правую кнопку мыши и выбрав команду Формат ячеек….

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

Создание таблиц

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

Вставка таблицы

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

2. На вкладке Вставка в группе Таблицы выберите команду Таблица.

3. Клавиши быстрого доступа. Также можно нажать клавиши CTRL+L или CTRL+T.

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

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

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

После создания таблицы становится доступна вкладка Работа с таблицами, содержащая вкладку Конструктор. Инструменты на вкладке

Конструктор можно использовать для изменения или редактирования таблицы.

Автоформат

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

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

1. Введите нужные данные в таблицу.

2. Выделите диапазон ячеек, которые необходимо форматировать.

3. В меню «Формат» выберите команду «Автоформат». При этом откроется окно диалога.

4. В окне диалога «Автоформат» нажмите кнопку «Параметры», чтобы отобразить область «Изменить».

5. Выберите подходящий автоформат и нажмите кнопку «Ок».

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

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

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

Процедура задания условного форматирования следующая:

1)Выделяем ячейку или диапозон ячеек;

2) Входим в меню «Формат – Условное форматирование…»

3) Задаем условие. Например, тип условия «больше», значение «100»

4) Задаем формат (шрифт, границы, заливка)

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

Используйте условное форматирование для «подсветки» нетипичных, ошибочных значений, незаполненных ячеек, визуальной группировки данных и т.п.

Основные приемы работы в MS EXCEL

1. Перемещение между ячейками означает сделать ячейку активной щелчком мыши в ней. Другой вариант – воспользоваться клавишами управления курсора и выбрать нужную ячейку, а затем нажатием клавиши Enter сделать её активной.

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

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

4. Ввод чисел осуществляется также как и текста. При вводе Excel пытается представить число таким образом, чтобы оно полностью помещалось в ячейке, если же это не удается, то вместо числа выводятся символы диеза («#»).

5. Ввод формул. Кроме текста и числа ячейка может содержать формулу, т.е. её содержимое будет вычисляться исходя из значений других ячеек. При вводе формулы строку надо начать с символа равно («=»).

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

7. Удаление содержимого ячейки – для этого надо сделать интересующую ячейку активной и нажить клавишу DEL. Другой вариант – воспользоваться пунктом [Очистить] опции горизонтального меню [Правка].

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

9. С помощью Excel на рабочих листах можно создавать всевозможные графики и диаграммы, основанные на исходных и производных числовых данных. Перед созданием диаграммы необходимо выделить область рабочего листа, содержащую необходимые данные. Для вызова мастера диаграмм нужно воспользоваться опцией [Диаграмма] или щелкнуть на кнопке [Мастер диаграмм] панели инструментов Стандартная.

studfiles.net

Автовосстановление в Excel — Microsoft Excel для начинающих

Excel периодически сохраняет копию файла. Из статьи вы узнаете, как восстановить несохранённый файл, и документ, сохранившийся хотя бы один раз. При первом открытии после сбоя Excel отображает панель Document Recovery (Восстановление документа). Это быстрый способ реанимировать последний автоматически сохранённый файл.

Файл, который не сохраняли ни разу

Если вы никогда не сохраняли файл, и при этом случайно нажали кнопку Don’t save (Не сохранять), закрывая Excel (или если программа дала сбой), следуйте инструкции ниже, чтобы спасти последний автоматически сохранённый документ.

Примечание: На рисунке выше Excel сообщает, что последняя копия останется доступной.

1. На вкладке File (Файл) кликните Info (Информация).
2. Нажмите Manage Versions (Управление версиями), кликните по Recover Unsaved Workbooks (Восстановить несохраненные книги).
3. Выберите последний автоматически сохранённый документ.

Документ, сохраненный хотя бы один раз

Если вы сохранили файл по крайней мере единожды, но случайно нажали Don’t save (Не сохранять), закрывая Excel (или если программа дала сбой), следуйте инструкции ниже, чтобы восстановить последний автоматически сохранённый документ.

Примечание: Excel сообщает, что последняя копия останется доступной.

1. Откройте документ.
2. На вкладке File (Файл) выберите Info (Информация).
3. Кликните по Manage Versions (Управление версиями) и нажмите на последний автоматически сохранённый документ.

Версии

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

1. Вы можете в любое время вернуться к предыдущей версии файла Excel.

Примечание: Excel удаляет все предыдущие автосохранения при закрытии.

Настройки

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

1. На вкладке File (Файл) выберите Options (Параметры) > Save (Сохранение).

Примечание: Вы можете задать автосохранение каждые n минут, сменить папку, где находятся автоматически сохранённые документы, и т.д.

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

орматы ячеек и создание таблиц в Excel. Автоформат и условное форматирование. Основные приемы работы в MS EXCEL — Информатика

Форматы ячеек

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

Примеры форматов:

Содержимое ячейки

Формат

Результат

1234,567

Числовой

1 234,567

1234,567

Денежный

1 234,567р.

1234,567

Процентный

123456,7%

1234,567

Экспоненциальный

1,235Е+03

123456

Время

12:34:56

Присвоить формат ячейке или блоку ячеек, предварительно выделив их, можно с помощью команды Ячейки из меню Формат или нажав правую кнопку мыши и выбрав команду Формат ячеек….

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

Создание таблиц

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

Вставка таблицы

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

2. На вкладке Вставка в группе Таблицы выберите команду Таблица.

3. Клавиши быстрого доступа. Также можно нажать клавиши CTRL+L или CTRL+T.

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

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

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

После создания таблицы становится доступна вкладка Работа с таблицами, содержащая вкладку Конструктор. Инструменты на вкладке Конструктор можно использовать для изменения или редактирования таблицы.

Автоформат

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

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

1. Введите нужные данные в таблицу.

2. Выделите диапазон ячеек, которые необходимо форматировать.

3. В меню «Формат» выберите команду «Автоформат». При этом откроется окно диалога.

4. В окне диалога «Автоформат» нажмите кнопку «Параметры», чтобы отобразить область «Изменить».

5. Выберите подходящий автоформат и нажмите кнопку «Ок».

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

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

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

Процедура задания условного форматирования следующая:

1)Выделяем ячейку или диапозон ячеек;

2) Входим в меню «Формат – Условное форматирование…»

3) Задаем условие. Например, тип условия «больше», значение «100»

4) Задаем формат (шрифт, границы, заливка)

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

Используйте условное форматирование для «подсветки» нетипичных, ошибочных значений, незаполненных ячеек, визуальной группировки данных и т.п.

Основные приемы работы в MS EXCEL

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

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

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

Ввод чисел осуществляется также как и текста. При вводе Excel пытается представить число таким образом, чтобы оно полностью помещалось в ячейке, если же это не удается, то вместо числа выводятся символы диеза («#»).

Ввод формул. Кроме текста и числа ячейка может содержать формулу, т.е. её содержимое будет вычисляться исходя из значений других ячеек. При вводе формулы строку надо начать с символа равно («=»).

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

Удаление содержимого ячейки – для этого надо сделать интересующую ячейку активной и нажить клавишу DEL. Другой вариант – воспользоваться пунктом [Очистить] опции горизонтального меню [Правка].

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

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

Ключевые слова страницы: как, скачать, бесплатно, без, регистрации, смс, реферат, диплом, курсовая, сочинение, ЕГЭ, ГИА, ГДЗ

referatzone.com

Автоформат таблиц

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

Выберите Таблица — Автоформат (когда курсор находится в таблице)

Применение Автоформата к таблице

1. Щелкните в ячейке таблицы или выберите ячейки, которые нужно отформатировать.

2. Выберите Таблица — Автоформат, затем щелкните на формате, который нужно применить.

3. Нажмите ОК.

Окно Автоформат

Формат

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

Добавить

Позволяет добавить новый стиль таблицы в список.

1. Позволяет отформатировать таблицу в документе.

2. Выберите таблицу и затем щелкните на Таблица — Автоформат.

3. Нажмите кнопку Добавить.

4. В окне Добавить автоформат введите имя и нажмите OK.

Delete

Удаляет выбранный стиль таблицы.

Дополнительно

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

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

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

Формат чисел

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

Шрифт

Позволяет включить формат шрифта в выбранный стиль таблицы.

Выравнивание

Позволяет включить параметры выравнивания в выбранный стиль таблицы.

Границы

Позволяет включить стиль границ в выбранный стиль таблицы.

Шаблон

Позволяет включить стиль фона в выбранный стиль таблицы.

Переименовать

Позволяет изменить имя выбранного стиля таблицы. Стиль «Стандартный» переименовать нельзя.

infolib.lotus.com

Как в Excel изменять цвет строки в зависимости от значения в ячейке

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

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

Как изменить цвет строки на основании числового значения одной из ячеек

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

Мы хотим раскрасить различными цветами строки в зависимости от заказанного количества товара (значение в столбце Qty.), чтобы выделить самые важные заказы. Справиться с этой задачей нам поможет инструмент Excel – «Условное форматирование».

1. Первым делом, выделим все ячейки, цвет заливки которых мы хотим изменить.
2. Чтобы создать новое правило форматирования, нажимаем Главная > Условное форматирование > Создать правило (Home > Conditional Formatting > New rule).
3. В появившемся диалоговом окне Создание правила форматирования (New Formatting Rule) выбираем вариант Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format), и ниже, в поле Форматировать значения, для которых следующая формула является истинной (Format values where this formula is true), вводим такое выражение:

   =$C2>4

   

   Вместо C2 Вы можете ввести ссылку на другую ячейку Вашей таблицы, значение которой нужно использовать для проверки условия, а вместо 4 можете указать любое нужное число. Разумеется, в зависимости от поставленной задачи, Вы можете использовать операторы сравнения меньше (<) или равно (=), то есть записать формулы в таком виде:

   =$C2<4
=$C2=4

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

4. Нажимаем кнопку Формат (Format) и переходим на вкладку Заливка (Fill), чтобы выбрать цвет фона ячеек. Если стандартных цветов недостаточно, нажмите кнопку Другие цвета (More Colors), выберите подходящий и дважды нажмите ОК.Таким же образом на остальных вкладках диалогового окна Формат ячеек (Format Cells) настраиваются другие параметры форматирования, такие как цвет шрифта или границы ячеек.
5. В поле Образец (Preview) показан результат выполнения созданного правила условного форматирования:
6. Если всё получилось так, как было задумано, и выбранный цвет устраивает, то жмём ОК, чтобы увидеть созданное правило в действии.Теперь, если значение в столбце Qty. больше 4, то соответствующая строка таблицы целиком станет голубой.

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

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

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

=$C2>9

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

1. На вкладке Главная (Home) в разделе Стили (Styles) нажмите Условное форматирование (Conditional Formatting) > Управление правилами (Manage Rules)
2. В выпадающем списке Показать правила форматирования для (Show formatting rules for) выберите Этот лист (This worksheet). Если нужно изменить параметры только для правил на выделенном фрагменте, выберите вариант Текущий фрагмент (Current Selection).
3. Выберите правило форматирования, которое должно быть применено первым, и при помощи стрелок переместите его вверх списка. Должно получиться вот так:Нажмите ОК, и строки в указанном фрагменте тут же изменят цвет, в соответствии с формулами в обоих правилах.

   

Как изменить цвет строки на основании текстового значения одной из ячеек

Чтобы упростить контроль выполнения заказа, мы можем выделить в нашей таблице различными цветами строки заказов с разным статусом доставки, информация о котором содержится в столбце Delivery:

• Если срок доставки заказа находится в будущем (значение Due in X Days), то заливка таких ячеек должна быть оранжевой;
• Если заказ доставлен (значение Delivered), то заливка таких ячеек должна быть зелёной;
• Если срок доставки заказа находится в прошлом (значение Past Due), то заливка таких ячеек должна быть красной.

И, конечно же, цвет заливки ячеек должен изменяться, если изменяется статус заказа.

С формулой для значений Delivered и Past Due всё понятно, она будет аналогичной формуле из нашего первого примера:

=$E2="Delivered"
=$E2="Past Due"

Сложнее звучит задача для заказов, которые должны быть доставлены через Х дней (значение Due in X Days). Мы видим, что срок доставки для различных заказов составляет 1, 3, 5 или более дней, а это значит, что приведённая выше формула здесь не применима, так как она нацелена на точное значение.

В данном случае удобно использовать функцию ПОИСК (SEARCH) и для нахождения частичного совпадения записать вот такую формулу:

=ПОИСК("Due in";$E2)>0
=SEARCH("Due in",$E2)>0

В данной формуле E2 – это адрес ячейки, на основании значения которой мы применим правило условного форматирования; знак доллара $ нужен для того, чтобы применить формулу к целой строке; условие «>0» означает, что правило форматирования будет применено, если заданный текст (в нашем случае это «Due in») будет найден.

Подсказка: Если в формуле используется условие «>0«, то строка будет выделена цветом в каждом случае, когда в ключевой ячейке будет найден заданный текст, вне зависимости от того, где именно в ячейке он находится. В примере таблицы на рисунке ниже столбец Delivery (столбец F) может содержать текст «Urgent, Due in 6 Hours» (что в переводе означает – Срочно, доставить в течение 6 часов), и эта строка также будет окрашена.

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

=ПОИСК("Due in";$E2)=1
=SEARCH("Due in",$E2)=1

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

Итак, выполнив те же шаги, что и в первом примере, мы создали три правила форматирования, и наша таблица стала выглядеть вот так:

Как изменить цвет ячейки на основании значения другой ячейки

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

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

Как задать несколько условий для изменения цвета строки

Если нужно выделить строки одним и тем же цветом при появлении одного из нескольких различных значений, то вместо создания нескольких правил форматирования можно использовать функции И (AND), ИЛИ (OR) и объединить таким образом нескольких условий в одном правиле.

Например, мы можем отметить заказы, ожидаемые в течение 1 и 3 дней, розовым цветом, а те, которые будут выполнены в течение 5 и 7 дней, жёлтым цветом. Формулы будут выглядеть так:

=ИЛИ($F2="Due in 1 Days";$F2="Due in 3 Days")
=OR($F2="Due in 1 Days",$F2="Due in 3 Days")

=ИЛИ($F2="Due in 5 Days";$F2="Due in 7 Days")
=OR($F2="Due in 5 Days",$F2="Due in 7 Days")

Для того, чтобы выделить заказы с количеством товара не менее 5, но не более 10 (значение в столбце Qty.), запишем формулу с функцией И (AND):

=И($D2>=5;$D2<=10)
=AND($D2>=5,$D2<=10)

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

=ИЛИ($F2="Due in 1 Days";$F2="Due in 3 Days";$F2="Due in 5 Days")
=OR($F2="Due in 1 Days",$F2="Due in 3 Days",$F2="Due in 5 Days")

Подсказка: Теперь, когда Вы научились раскрашивать ячейки в разные цвета, в зависимости от содержащихся в них значений, возможно, Вы захотите узнать, сколько ячеек выделено определённым цветом, и посчитать сумму значений в этих ячейках. Хочу порадовать Вас, это действие тоже можно сделать автоматически, и решение этой задачи мы покажем в статье, посвящённой вопросу Как в Excel посчитать количество, сумму и настроить фильтр для ячеек определённого цвета.

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

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

Что такое числовой формат в Excel?

Каждый раз, работая в Excel, старайтесь использовать подходящие числовые форматы для Ваших данных. Числовые форматы сообщают программе, какой тип данных Вы используете, будь то проценты (%), валюты ($), время, дата и так далее. Посмотрите, для примера, на следующую таблицу, в которой использованы различные числовые форматы:

Для чего нужны числовые форматы?

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

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

Установка числовых форматов

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

• В Microsoft Excel 2007-2013 откройте вкладку Home (Главная), в разделе Number (Число) кликните выпадающее меню Number Format (Числовые форматы) и выберите нужный формат. Либо под выпадающим меню Вы можете кликнуть одну из иконок быстрого доступа.
• В Excel 2003 и более ранних версиях откройте меню Format (Формат) > Cells (Ячейки).
• В Google Sheets нажмите кнопку More Formats (Другие форматы) в левой части панели инструментов – эта кнопка выглядит, как цифры 123. Слева от этой команды есть иконки Currency (Денежный формат) или Percent (Процентный формат) для быстрого доступа к этим форматам.

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

В этом примере мы применим числовой формат Currency (Денежный), который добавляет символ валюты ($) и отображает два десятичных знака для любого числового значения.

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

Используем числовые форматы правильно

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

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

Процентный формат

Один из наиболее полезных форматов – это процентный формат (%). Он отображает значения как проценты, например, 20% или 55%, что особенно полезно при расчете величин скидок или чаевых. Если вслед за числом ввести знак процента (%), к нему будет автоматически применен процентный формат.

Возможно, Вы еще помните из школьного курса математики, что проценты могут быть записаны в виде десятичных дробей, т.е. 15% – это то же самое, что 0,15, а 7,5% – это 0,075.

Процентный формат будет полезен не однократно. На изображениях ниже Вы видите пример, что получается в каждой таблице при разном форматировании размера скидки (5, 5% или 0,05):

Как видите, в таблице слева вычисления выполнены не верно. Без применения процентного формата, Excel думает, что мы хотим умножить $22,50 на 5, а не на 5%. Таблица справа хоть и справляется с этой задачей, но прочесть проще всего средний вариант.

Формат даты

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

Электронные таблицы понимают информацию не так, как это делает человек. Например, если Вы введете в ячейку слово «October», Excel не поймет, что Вы имеете в виду дату, и сочтёт это обычным текстом. Вместо этого, при вводе дат, используйте определённый формат, который понятен Excel, например, месяц/день/год или день/месяц/год – в зависимости от того, в какой стране Вы находитесь и какой локализацией программы пользуетесь. В примере ниже мы введём «10/12/2014», чтобы получить дату October 12, 2014. В этом случае Excel автоматически установит для ячейки формат даты.

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

Если формат даты не был применен автоматически, значит приложение Excel не распознало дату, которую Вы ввели. В следующем примере мы ввели «March 15th». Excel не распознал в этой записи дату, поэтому формат ячейки остался General (Общий).

С другой стороны, если мы введем «March 15» (без th), Excel распознает дату. Поскольку год в этой дате не указан, автоматически добавится текущий год, и дата будет содержать всю необходимую информацию. Мы также можем записать дату несколькими другими способами, например, 3/15, 3/15/2014 или March 15 2014, и таблица по-прежнему поймёт, что это дата.

В русской локализации 15/3, 15/3/2014 или 15 март 2014.

Другие варианты форматов даты

В некоторых приложениях присутствует гораздо больший выбор форматов даты. Доступ к ним немного отличается, в зависимости от приложения, которое Вы используете. В Excel 2007-2013 откройте выпадающее меню Number Format (Числовые форматы) и выберите в нем пункт More Number Formats (Другие числовые форматы).

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

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

Хитрости при форматировании чисел

Вот несколько хитростей, которые помогут Вам лучше справиться с числовыми форматами:

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

• • Дважды проверьте значения в ячейках после применения к ним числового формата: Если Вы применяете числовой формат к существующим данным, результат может быть неожиданным. Например, применяя процентный формат (%) к ячейке со значением 5, Вы получите 500%, а не 5%. В этом случае придется заново ввести корректные значения в каждую ячейку.

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

• Если Вы хотите, чтобы данные в ячейке отображались в точности так, как они были введены, нужно использовать текстовый формат. Этот формат особенно хорош для чисел, над которыми Вы не хотите производить какие-либо вычисления, например, номера телефонов, почтовые индексы или числа, начинающиеся с нуля (02415). Примените текстовый формат к таким ячейкам, прежде чем вводить данные.

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

Условное форматирование в Excel | Exceltip

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

Условное форматирование – это простой способ определить ячейки с ошибочными записями или значениями определённого типа. Вы можете использовать формат (например, красная заливка), чтобы легко идентифицировать определенные ячейки.

Виды условного форматирования

Когда вы нажимаете на кнопку Условное форматирование, которая находится в группе Стили вкладки Главная, вы увидите выпадающее меню со следующими опциями:

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

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

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

Цветовые шкалы позволяет задавать двух- и трехцветовые шкалы для цвета фона ячейки на основе ее значения относительно других ячеек в диапазоне

Наборы значков отображает значок в ячейке. Какой именно значок отображается, зависит от значения ячейки относительно других ячеек. Excel 2013 предоставляет 20 наборов значков на выбор (при этом вы можете смешивать и сочетать значки из разных наборов). Количество значков в наборах колеблется от трех до пяти.

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

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

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

Графическое условное форматирование

Вероятно, самое крутое (и конечно, простое) условное форматирование, которое можно применить к диапазону ячеек – это форматирование с применением графических элементов – Гистограммы, Цветовые шкалы и Наборы значков.

На рисунке изображено применение двух различных правил для форматирования для диапазона от 6 до 1 и наоборот. В первом случае применялись Цветовые шкалы, где мы видим, как изменяется формат при изменении значения от 6 до 1, во втором – 3 цветные стрелки.

Определение конкретных значений в диапазоне ячеек

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

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

Примеры условного форматирования

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

Выделяем диапазон ячеек, к которому мы хотим применить условное форматирование. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Правила выделения ячеек -> Текст содержит.

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

Щелкаем ОК, чтобы наше правило вступило в силу.

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

Скажем, вы хотите применить три различных условных форматирования к одному и тому же диапазону ячеек: первый тип формата, когда ячейка содержит целевое значение, второй – когда больше цели и третий – когда меньше. Ниже описаны шаги по заданию формата Желая заливка с темно-желтым текстом для ячеек содержащих значение 95, Зеленая заливка с темно-зеленым текстом для ячеек со значениями больше 95 и Светло-красная заливка и темно-красным текстом для ячеек меньше 95.

Выделяем диапазон ячеек, к которому мы хотим применить три различных правила условного форматирования. Начнем с создания правила для ячеек, содержащих значение равное 95. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Правила выделения ячеек -> Равно. Excel откроет диалоговое окно Равно, где в левом текстовом поле необходимо указать условие 95, а в правом выпадающем списке выбрать формат для этого условия Желая заливка с темно-желтым текстом.

Далее задаем условное форматирование для значений больше 95. Из меню Условное форматирование -> Правила выделения ячеек выбираем Больше, в появившемся диалоговом окне Больше указываем значение, выше которого ячейка будет закрашиваться в зеленый цвет, и сам формат.

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

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

Формулы в условном форматировании

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

Выделяем таблицу с данными, к которой мы хотим применить условное форматирование. Переходим по вкладке Главная в группу Стили, щелкаем кнопку Условное форматирование -> Создать правило.  В появившемся диалоговом окне Создание правила форматирования в поле Выберите тип правила выбираем  Использовать формулу для определения форматируемых ячеек.

В поле Измените описание правила задаем условия и формат для нашего правила. В нашем случае, условием будет формула =ИЛИ(ДЕНЬНЕД($A2;2)=6;ДЕНЬНЕД($A2;2)=7). В качестве формата я выбрал темно красную заливку.

Послесловие

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

Вам также могут быть интересны следующие статьи

exceltip.ru