Форматирование таблиц в excel. как выполнять форматирование таблиц

Как начать работу с Google-таблицами?

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

нажать большой и яркий «+» в левом верхнем углу вашего экрана;

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

Но что делать тем, кто уже начал работать в Microsoft Excel (или Numbers, если речь идет о компьютере родом из Купертино). Все данные уже собраны, формулы проставлены, информация отфильтрована, а ваш руководитель только-только соизволил сообщить, что ему также необходим доступ к этой таблице. Ситуация печальная, но весьма реалистичная и ничуть не смертельная. Выход есть – нужно все ваши наработки из Excel загрузить в Google Sheets и отправить незадачливому боссу ссылку на новенькую онлайн-таблицу.

И вообще, взаимная файловая поддержка Microsoft Excel и Google Sheets делает работу с обоими сервисами очень удобной и комфортной. Респект создателям!

На основе формулы.

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

Для ввода собственной формулы используется самый последний пункт в раскрывающемся списке условий – “Ваша формула”.

Давайте посмотрим, как это работает.

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

Выделяем диапазон дат А2:А22, затем как обычно вызываем меню.

В раскрывающемся меню выбираем пункт “Ваша формула” и в появившемся окошке вписываем формулу, которая поможет нам определить номер дня недели по дате.

=WEEKDAY(A2:A22,2)>5

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

Как видите, выходные дни теперь выделены цветом.

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

=REGEXMATCH(D2:D22;”Черный”)

Эта функция возвратит значение «Истина», если в названии шоколада будет встречаться слово “Черный”.

Посмотрите, что у нас получилось – выделенными оказались заказы “Черный шоколад” и “Супер Черный шоколад”. Нам теперь не нужно долго искать их глазами – особенно если наша таблица содержит десятки и сотни строк.

Выделение дубликатов в одном столбце

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

Шаг # 1: выбор диапазона

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

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

В нашем примере это диапазон A1: A12. Диапазон можно выбрать, щелкнув правой кнопкой мыши «Формат» вверху страницы и выбрав «Условное форматирование». (пример показан ниже).

Щелкните правой кнопкой мыши «Форматировать».

Выберите «Условное форматирование».

Этот запрос должен отображаться при выборе «Условное форматирование». Здесь может быть показан диапазон, обозначенный красным прямоугольником. Если у вас уже есть правило условного форматирования, просто выберите «добавить другое правило», прежде чем продолжить. (показано ниже)

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

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

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

Шаг # 2: установка формата

Установите для параметра «Форматировать ячейки, если» в разделе «Правила форматирования» значение «Пользовательская формула». И введите формулу проверки дубликатов . В поле «(Диапазон, критерии) введите выбранный диапазон в следующем формате: . В нашем примере это диапазон «A1: A12». Это означает столбец A («$ A $ 1») и строки 1–12 в столбце A ($ A: $ 12, A1). Измените переменные в круглых скобках (Диапазон, Критерии) в соответствии с выбранным диапазоном данных. Затем нажмите «Готово». (пример показан ниже)

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

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

Панель инструментов в Google Sheets

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

Давайте быстро пробежимся по основным инструментам, которые предоставляет сервис Google.

Отменить и повторить последнее действие, распечатать документ, скопировать форматирование. Если вам нужно применить одинаковое форматирование сразу к нескольким ячейкам, очень удобно использовать именно этот инструмент. Сначала выберете ячейку, после чего кликайте по инструменту на панели, а потом — по ячейке, которую нужно отформатировать.
Изменить масштаб. С помощью этой функции можно сделать таблицу крупнее или мельче в диапазоне от 50 до 200%.
Изменить формат данных в ячейках. В этом разделе можно выбрать формат исчисления в ячейке. Например, денежный или процентный. Также можно увеличить или уменьшить количество знаков после запятой в числовом формате.
Шрифт. В выпадающем списке можно выбрать любой из предложенных шрифтов.
Размер шрифта.
Форматирование текста. Выбор начертания слов, цвет текста или фона для него.
Форматирование ячеек. Здесь на ячейки можно применить заполнение цветом, выбрать их границы, объединить друг с другом.
Выравнивание текста. Его можно настроить по горизонтали и вертикали, выбрать функции переноса или поворота текста в каждой ячейке или столбце.
Прочие инструменты: добавить ссылку, добавить комментарий, вставить диаграмму, создать фильтр, использовать функции.
Отображение листа и направления ввода.
Способы ввода. Есть варианты включения экранной клавиатуры или поля для рукописного ввода.
Скрыть меню. Кликнув на стрелку можно свернуть главное меню вместе с заголовков таблицы, иконкой аккаунта и настройками доступа. Таким образом, останется только панель инструментов.

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

Изменение расстояния между буквами в Ворде

Чтобы сделать текст более растянутым или сжатым – выделите его и нажмите Ctrl+D. В открывшемся меню «Шрифты» перейдите на вкладку «Дополнительно». Здесь найдем группу команд «Межзнаковый интервал», где можно сделать такие настройки:

Масштаб – увеличить или уменьшить масштаб отображения относительно установленного размера шрифта
Интервал – задайте расстояние в пунктах между символами
Смещение – сдвиг выделенного текста вниз или вверх относительно базовой линии (в пунктах)
Кернинг… — интеллектуальное сжатие текста для экономии места. Не выполняйте его для слишком мелкого шрифта, где буквы могут сливаться друг с другом.

Применение электронных таблиц

С какими данными работают электронные таблицы?

Электронные таблицы позволяют работать с тремя основными типами данных: число, текст и формула.
Числа в электронных таблицах могут быть записаны в обычном числовом или экспоненциальном формате.
Текстом в электронных таблицах является последовательность символов, состоящая из букв, цифр и пробелов.
Формула должна начинаться со знака равенства и может включать в себя числа, имена ячеек, функции (Математические, Статистические, Финансовые, Дата и время и т. д.) и знаки математических операций.

Применение электронных таблиц

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

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

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

Так как основное назначение табличного процессора — автоматизация Расчетов в табличной форме.

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

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

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

В настоящее время, когда клиент все больше обращает внимание на оперативность, наглядность предоставляемой информации

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

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

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

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

На основе числового значения другой ячейки

Часто встречается вопрос: «Как применить условное форматирование, чтобы условие можно было бы легко изменять? Ведь каждый раз корректировать правило – это очень трудоёмко».

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

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

Рядом с нашей таблицей в столбце H укажем эти два условия.

Создадим правила форматирования таблицы заказов.

Диапазон применения установим A2:G22, чтобы не изменять заголовок таблицы.

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

Вот как выглядит формула для заказов, количество в которых больше 100:

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

А если знак $ находится перед номером строки, то абсолютная ссылка установлена на строку

Напомню, что знак $, стоящий перед буквой столбца, означает абсолютную ссылку на этот столбец. А если знак $ находится перед номером строки, то абсолютная ссылка установлена на строку.

В нашем примере $H$3 означает абсолютную ссылку на ячейку, то есть при любых действиях с таблицей она всегда будет ссылаться на эту ячейку.

Важно! Используем абсолютную ссылку на столбец E и абсолютную ссылку на ячейку H3, в которой записано наше ограничение – 100. Если это не сделать, формула работать не будет!. Теперь добавим второе условие

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

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

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

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

Более правильно все вспомогательные данные размещать на отдельном листе.

Перейдём на лист 2 и укажем эти новые условия.

Далее создаём правила ссылаясь на эти ограничения.

И тут возникает проблема. Если мы просто подставим в формулу адрес ячейки из листа 2, то получим ошибку. Прямые ссылки на ячейки в формуле возможны только с текущего листа. Что же делать?

На помощь нам приходим функция ДВССЫЛ (англ. – INDIRECT), которая позволяет возвратить ссылку на ячейку, адрес которой написан как текст.

Вот так будет выглядеть ссылка на ячейку:

И вторая формула –

В результате получаем тот же результат, что и ранее, но наш лист не захламлён дополнительными данными.

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

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

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

Форматирование с пустыми / непустыми ячейками

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

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

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

Рекламные объявления

Текстовая статистика с
Текст заканчивается на
Текст точно
Текст содержит
Текст не содержит

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

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

Таким же образом вы можете установить любое текстовое условие и получить полученную информацию из электронной таблицы.

Выделение всей строки таблицы

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

Рекламные объявления

Условием форматирования будет «Custom Formula is», за которым следует указанное значение.

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

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

Вы должны упомянуть такую формулу.

= $ A2 = «BMW»

Позвольте мне объяснить это по порядку.

= означает, что формула инициирована
$ A означает, что формула будет выглядеть в столбце A
A2 — это образец данных в столбце A
BMW — это значение данных, которое означает, что строки, содержащие слово BMW, будут выделены.

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

Вы должны указать условие на основе

больше чем
Меньше, чем
Больше чем равно
Меньше чем равно
Равно
Не равно
Между
Не между

Вот шаги,

выберите диапазон данных
Формат > Условное форматирование
Нажмите Добавить новое правило
Под Формат ячеек, если указать предложение на основе числа
Затем установите числовое значение
Нажмите Сделанный

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

Использование цветов для условного форматирования в Google Таблицах

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

Условное форматирование в Google Таблицах с использованием дат

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

Существует три типа форматирования на основе даты

Дата
дата раньше
Дата после

Теперь о шагах по настройке условий форматирования.

открыть электронную таблицу
Выберите диапазон
Перейти к Формат > Условное форматирование
Под Форматировать ячейки, если, установите любое из трех предложений на основе даты, которые я упомянул выше
Как и любое другое форматирование, установите значение параметра Date, например, сегодня, вчера, после определенной даты или месяца и даже года.

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

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

Сортировка в Google таблицах

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

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

Инструкция:

Выделяем весь столбец, нажав на ячейку с буквой, в нашем случае «A».
Жмем правой кнопкой мышки по выделенному столбцу и выбираем «Условное форматирование.

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

В менюшке:

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

Теперь установим дополнение для нашей таблицы. Для этого в верхнем меню находим одноименную кнопку «Дополнения» ↣ «Установить дополнение». Вбиваем в поисковую строку «Sort by color» и устанавливаем расширение, как на рисунке:

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

Нажимаем «Дополнения» ↣ «Sort by color» и выбираем любое из списка. Вуаля. Можно удалять.

Рабочая область в Google Таблицы

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

При наведении курсора на каждый элемент система подскажет его функцию

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

Изменить название документа можно также во вкладке «Настройки доступа»

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

Такой же выпадающий список появится, если вы нажмёте на одну из пронумерованных ячеек в левой части экрана

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

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

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

Если вы захотите удалить гугл-таблицу, то зайдите во вкладку «Файл» и выберите пункт «Удалить». Восстановить удалённые данные можно будет в «шапке», нажав на кнопку «последнее изменение».