Выравнивание содержимого ячеек

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

Команды, отвечающие за выравнивание содержимого ячеек

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

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

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

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

Выравнивание в зависимости от содержимого

Отступы в ячейках и направление текста

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

Добавление отступов в ячейки

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

Резмещение содержимого по вертикали

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

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

Выбор шрифтов и размера шрифтов

От выбора шрифта зависит общее восприятие текста. В Excel 2013/2016 по умолчанию выбран шрифт Calibri с размером 11 пунктов (1 пункт равняется 1/72 дюйма, подробнее: //msoffice-prowork.com/courses/word/wordpro/wordpro-lesson3/#t1). Можно изменить шрифт для выбранной ячейки, столбца, строки и определенной буквы в ячейке.

За изменение шрифта, его размера, начертания, цвета и цвета заливки ячейки отвечает группа «Шрифт» вкладки «Главная».

Настройка параметров шрифта в Excel

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

Установка шрифта по умолчанию в параметрах Excel

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

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

Мини панель инструментов для ячейки и данных внутри ячейки

Изменение цвета шрифта и цвета заливки

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

Пользователь может выбрать цвет из предложенных вариантов цветового набора темы, стандартный цвет или произвольный (команда «Другие цвета…»).

Настройка цветовой палитры

Цвета темы – это цветовой набор, который зависит от темы документа, другими словами, ячейка с зеленой заливкой может изменить свой цвет на синий, фиолетовый и т.д. в зависимости от того какая тема или цветовой набор используется. Изменить тему документа, а также цветовой набор можно на вкладке «Разметка страницы», группа «Темы».

Изменение темы книги Excel

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

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

Изменение заливки с помощью диалогового окна «Формат ячеек»

Изменение начертания текста

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

Для быстрого изменения начертания текста существуют общеизвестные сочетания клавиш:

• Ctrl + B – полужирный;
• Ctrl + I – курсив;
• Ctrl + U – подчеркивание (линию подчеркивания можно выбрать одинарную или двойную). В Word выбор линий подчеркивания значительно шире.

Изменение начертания текста

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

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

Разнообразие числовых форматов в Excel

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

В нижнем ряде группы «Число» есть быстрые наборы для часто применяемых форматов (процент, изменение разрядности, отделение групп разрядности).

Различные варианты оформления числового форматирования

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

Перенос текста и объединение ячеек

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

Установка переноса текста в ячейке и объединение нескольких ячеек

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

Пример использования переноса текста в ячеках и объединения нескольких ячеек

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

В диалоговом окне «Формат ячеек» рядом с опциями переноса текста и объединения ячеек есть опция автопобора ширины. Редко используемая команда впишет текст в ячейку так, чтобы он вмещался в ячейку, но сделает это за счет уменьшения размера текста. Данная команда не совместима с командой переноса по словам, поэтому на рисунке выше она не активна для выделения.

Применение функции автоподбора ширины

Добавление границ для ячеек

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

Установка границ для диапазонов данных

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

Тонкая настройка границ для диапазона с помощью диалогового окна «Формат ячеек»

Диалоговое окно «Формат ячеек» на вкладке «Граница» вызывается по команде «Другие границы…». Здесь можно выбрать тип линии, ее цвет, а также тонко настроить линии в самой таблице (внешние, внутренние).

Формат по образцу

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

Команда формат по образцу

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

1. Выделить содержимое с форматированием.
2. Нажать кнопку «Формат по образцу» группы «Буфер обмена» вкладки «Главная».
3. Выделить другой участок, который должен быть отформатирован как исходный либо кликнуть в левую верхнюю ячейку участка.

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

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

Пример использования инструмента «Формат по образцу»

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

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

Применение предустановленного набора стилей

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

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

Найти предустановленный набор стилей можно в одноименной группе на вкладке «Главная».

Заготовленные стили ячеек в Excel

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

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

Диапазон данных на листе оформлен стилями

Изменение существующих и создание своих стилей

Пользователю доступно не просто применение существующих стилей, можно создавать свои или изменять имеющиеся.

Диалоговове окно настройки стиля ячейки

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

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

Для примера, создадим два стиля (выбираем команду «Создать стиль ячейки» в выпадающем меню команды «Стили Ячеек»):

1. Первый форматирует выравнивание, шрифт, рамку и заливку. Выравнивание по горизонтали: по центру, шрифт Times New Roman 14 пт, рамка одинарная по всем краям черного цвета, заливка светло-зеленая.
2. Второй форматирует только рамку и заливку. Рамка: только по нижнему краю темно-зеленая широкая, заливка: без заливки.



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

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

Результат оформления

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

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

Действия над созданными стилями

С помощью команда «Объединить стили» можно скопировать существующие стили из другой открытой книги.

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

Диапазон данных без форматирования

В таблице без форматирования быстро выделить лучших по месяцам довольно проблематично, т.к. все данные отформатированы единообразно. Однако, достаточно воспользоваться заготовленным правилом из команды «Условное форматирование», группа «Стили», вкладка «Главная». Несмотря на то, что правило называется «Первые 10 элементов» пользователю доступна настройка правила.

Настройка правила условного форматирования

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

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

Все правила условного форматирования можно посмотреть в диспетчере условного форматирования (Условное форматирование/ управление правилами…).

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

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

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

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

Различные сценарии настройки собственных правил

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

Создание своего правила для условного форматирования

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

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

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

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

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

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

Гораздо интереснее создание самой формулы. И для создания формулы есть свое правило:

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

Ввод формулы для форматирования ячеек диапазона

План можно изменять и раскрашивание ячеек будет изменяться динамически.

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

Условное форматирование с индивидуальным планом на каждый месяц

Удаление форматирования ячейки

Удаление условного форматирования можно выполнить двумя путями, воспользоваться командой: «Очистить/Очистить форматы» из группы «Редактирование» вкладки «Главная», но лучше использовать команду: Условное форматирование/ удалить правила. Обе эти команды удалят условное форматирование, однако, если к ячейке, кроме условного форматирования был еще применен стиль ячейки, то команда «Очистить форматы» удалит и его, а удаление правил не затронет удаление стилей ячейки.

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

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

Пример 1. Определение выходных дней (субботы и воскресенья)

По умолчанию в условном форматировании не предусмотрено инструментов для работы с датами, однако, есть функции для работы с датами. С помощью функции ДЕНЬНЕД (WEEKDAY) идет проверка является ли определенная дата субботой или воскресеньем и если утверждение истинно, ячейка закрашивается в зеленый цвет.

Пример определения выходных дней с помощью условного форматирования

Пример 2. Создание «шахматной доски» с помощью условного форматирования

Для создания шахматной доски используются несколько функций. Функции СТРОКА (ROW) и СТОЛБЕЦ (COLUMN) возвращают номера строк/столбцов в которых они находятся, а функция ОСТАТ (MOD) вычисляет делится ли число на 2, другими словами, является ли оно четным.

Пример создания шахматной доски с помощью условного форматирования

Кстати, если немножко урезать используемую формулу: «=ОСТАТ(СТРОКА();2)=0», то получим выделение четных строк, а «=ОСТАТ(СТОЛБЕЦ();2)=0», соответственно, даст чередование столбцов в таблице.

Форматирование всего столбца или строки

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

1. Кликом левой клавиши мыши по заголовку строки либо столбца он выделяется. С помощью зажатой клавиши можно растянуть выделение на диапазон строк/столбцов.
2. Применяется необходимое форматирование (шрифт, размер, выравнивание и т.д.) либо стиль ячейки

Здесь надо быть осторожным, если добавляется заливка, границы, либо какой-нибудь стиль ячейки, то это оформление будет до конца листа. В связке Windows 10 и Office 2016 диапазон отправлялся на печать корректно, т.е. примененной заливке всех строк на принтер отправлялся только диапазон с данными, однако, в более ранних версиях Office, на печать отправлялись пустые листы только по причине того, что на них имеется форматирование (заливка, либо установлены границы).

Выделение нескольких столбцов

Кстати, быстро выделить одну строку можно введя в окошко «Имя» букву «R» или «r», а для столбца это буквы «C» или «c». Выделится тот столбец/ та строка на которой установлен курсор (каретка выделения ячейки).

Сокрытие и отображение строк и столбцов

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

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

Сокрытие столбцов с помощью команд ленты интерфейса

Для отображение ранее скрытых строк либо столбцов существует больше команд. Двойной клик по разделительной двойной полосе отобразит скрытые строки/ столбцы.

По двойной черте либо заголовкам столбцов можно определить скрыте

Отобразить скрытые строки или столбцы можно с помощью контекстного меню и команд ленты интерфейса. Для этого нужно выделить соседние строки/ столбцы, потом, либо вызвать контекстное меню и выбрать команду «Отобразить», либо группу «Ячейки», команда «Формат»/ «Скрыть или отобразить» и выбрать что следует отобразить строки или столбцы.

Транспонирование

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

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

Выполним операцию пошагово:

1. Выделяем исходный диапазон.
2. Копируем его (горячее сочетание Ctrl+C).
3. Устанавливаем курсор в ячейку, которая будет левой верхней нового диапазона.
4. Вкладка «Главная»/ «Вставить»/ Специальная вставка и выбираем, что нужно скопировать (все, значения, форматы и т.д.), главное не забываем указывать, что диапазон необходимо транспонировать.



Варианты вставки со специальной вставкой

5. После подтверждения операции получаем транспонированный диапазон (первой ячейкой нового диапазона была выбрана ячейка C15).



Вертикальный диапазон был траспонирован в горизонтальный

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

Добавление гиперссылки

Наиболее используемым способом добавления гиперссылки – это использование контекстного меню, в то же время можно вставить гиперссылку используя команды ленты интерфейса: группа «Ссылки» вкладки «Вставка», или используя горячее сочетание клавиш «Ctrl+K».

Команда «Гиперссылка» находится на вкладке «Вставка»

Гиперссылки глобально можно разделить на анкорные и безанкорные. Различие заключается в том, что в безанкорных ссылках видимый текст ссылки и сама ссылка одинаковые, тогда как в анкорных текст от самой ссылки отличается. Текст ссылки изменяется в диалоговом окне «Изменение гиперссылки»

Удаление/ изменение гиперссылки

Удалить или изменить гиперссылку можно:

1. Вызвав диалоговое окно изменения гиперссылки: с ленты интерфейса, горячим сочетанием «Ctrl+K», через контекстное меню.
2. Команда удаление доступна пользователю не только в диалоговом окне изменения гиперссылки, но и сразу из контекстного меню.

Поскольку клик по гиперссылке в ячейке приведет к переходу по адресу, то для выделения ячейки с гиперссылкой нужно выполнить «длинный» клик, т.е. кликнуть и подержать 1-2 секунды клавишу мыши, в этом случае переход по ссылке не будет осуществлен, а курсор выделения перейдет на ячейку с гиперссылкой.