Как залить таблицу в эксель

Обновлено: 04.07.2024

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

Что мы рассмотрим в данной статье?

1. Какие таблицы обычно создает пользователь в Excel?

2. Научимся, как не надо создавать таблицы?

3. Что значит "плохие" таблицы в Excel?

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

5. Подготовим таблицы для дальнейшего анализа и создания отчетов.

Ошибки при создании таблиц в Excel

1. Отсутствует структура данных в таблицах

Первая и самая распространенная ошибка пользователей при создании таблиц в Excel - это отсутствие структуры в таблицах Excel.

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

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

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

Именно создание нормализованных таблиц по модели реляционной базы и есть главный критерий построения правильных таблиц в Excel.

Запомните! Это фундамент для построения будущих таблиц.

Зафиксируем сразу два важных момента:

1. Определяем какие данные мы хотим хранить в таблице. Проектируем структуру данных в таблице Excel.

2. Создаем таблицы без лишней и перегруженной (не нужной) информации.

Полосатая заливка "зеброй" - весьма популярный и удобный для просмотра вид оформления таблиц:

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

Способ 1. Форматировать как таблицу

Это способ подойдет только тем, у кого последние версии Excel - 2007 или новее. Выделите ваш диапазон с данными и нажмите кнопку Форматировать как таблицу (Format as Table) на вкладке Главная (Home) :

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

Способ 2. Универсальный. Условное форматирование.

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

Выделите ячейки таблицы (кроме "шапки"), затем нажмите кнопку Условное форматирование (Conditional Formatting) на вкладке Главная (Home) и выберите Создать правило (New Rule) , затем в открывшемся окне задайте последний тип правила Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format)

В появившееся поле впишите следующую формулу:

или в английской версии =MOD(ROW(A2);2)=0

Эта формула берет номер текущей строки (функция СТРОКА, а A2 - первая ячейка выделения), делит его на 2 и проверяет остаток от деления (функция ОСТАТ). Если он равен нулю, т.е. номер строки четный, то происходит форматирование ячейки (не забудьте нажать кнопку Формат и задать цвет шрифта или заливку). Если необходимо залить не каждую 2-ю, а, скажем, каждую 5-ю строку, то просто измените в этой формуле число 2 на число 5.

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

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

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

Выбор стиля таблицы

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

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

На вкладке Главная выберите Форматировать как таблицу.

Выберите нужный стиль.

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

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

Создание и удаление пользовательского стиля таблицы

После создания пользовательских стилей таблиц они будут доступны в коллекции "Стили таблиц" (раздел "Пользовательские").

Созданные пользовательские стили доступны только в текущей книге.

Создание пользовательского стиля таблицы

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

На вкладке Главная выберите Форматировать как таблицу или разверните коллекцию Стили таблиц на вкладке Работа с таблицами > Конструктор (на компьютерах Mac — на вкладке Таблицы).

Выберите Создать стиль таблицы. Откроется диалоговое окно Создание стиля таблицы.

В поле Имя введите название нового стиля.

В поле Элемент таблицы выполните одно из следующих действий:

Чтобы отформатировать элемент, щелкните его и нажмите кнопку Формат, а затем выберите необходимые параметры форматирования на вкладках Шрифт, Граница и Заливка.

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

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

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

Удаление пользовательского стиля таблицы

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

В группе Дополнительно щелкните правой кнопкой стиль, который вы хотите удалить, а затем в контекстном меню выберите команду Удалить.

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

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

Выберите параметр Очистить.

Таблица отобразится в формате, заданном по умолчанию.

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

Выбор параметров для форматирования элементов таблицы

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

Выделите ячейки таблицы.

Выберите Работа с таблицами > Конструктор (на компьютерах Mac следует открыть вкладку Таблицы), а затем в группе Параметры стилей таблиц установите или снимите указанные ниже флажки.

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

Строка итогов — быстрое добавление строки с результатами функции промежуточного итога, (например СУММ, СРЗНАЧ, СЧЁТ, МИН или МАКС), которую можно выбрать в раскрывающемся списке. Скрытые строки можно как включать в вычисления, так и нет.

Первый столбец — применение или отмена форматирования первого столбца таблицы.

Последний столбец — применение или отмена форматирования последнего столбца таблицы.

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

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

Кнопка фильтра — включение и отключение автофильтра.

В Excel в Интернете можно применять параметры стиля таблицы для форматирование элементов таблицы.

Выбор параметров для форматирования элементов таблицы

Выделите ячейки таблицы.

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

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

Чередующиеся строки. Отображает нечетные и четные строки с чередующимся затенением для удобства чтения.

Первый столбец — применение или отмена форматирования первого столбца таблицы.

Последний столбец — применение или отмена форматирования последнего столбца таблицы.

Чередующиеся столбцы. Отображает нечетные и четные столбцы с чередующимся затенением для удобства чтения.

Кнопка фильтра — включение и отключение автофильтра.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

Сразу переходим к практике. Выделяем всю таблицу A1:D4.

На закладке «Главная» щелкаем по инструменту «Границы», который расположен в разделе «Шрифт». Из выпадающего списка выберите опцию «Все границы».

Теперь снова в этом же списке выберите опцию «Толстая внешняя граница». Далее выделяем диапазон A1:D1. В этом же разделе инструментов щелкните по «Цвет заливки» и укажите на «Темно-синий». Рядом в инструменте «Цвет текста» укажите «Белый».

Пример изменения цвета таблиц

На рисунках отображается процесс изменения границ в практике:

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

Выделите снова всю таблицу A1:D4. Нажмите комбинацию горячих клавиш CTRL+1 или CTRL+SHIFT+P. Появится многофункциональное диалоговое окно «Формат ячеек»

Сначала перейдите на закладку «Границы». В разделе инструментов «Все» данной закладки, сделайте щелчок по кнопке «внутренние». Теперь в разделе тип линии выберите самую толстую линию (вторая снизу в правой колонке). При необходимости ниже задайте цвет для границ таблицы. Дальше возвращаемся в раздел «Все» и кликаем по кнопке «внешние». Для подтверждения настроек форматирования границ нажмите ОК.

Так же можем сделать цветной текст. Выделите заголовки исходной таблицы A1:D1. Откройте окно «Формат ячеек». Теперь перейдите на вкладку «Шрифт». В поле цвет текста укажите на «Желтый». Чтобы изменить цвет выделенной ячейки в Excel, перейдите на вкладку «Заливка» и выберите «Черный». Для подтверждения изменения формата ячеек жмем ОК. Заливка ячеек в Excel позволяет выделить цветом строку или столбец.

Как видите в окне «Формат ячеек» находится множество инструментов, которые расширяют возможности форматирования данных.

Полезный совет! Форматируйте данные в последнюю очередь. Так вы сэкономите рабочее время.

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

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

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

Как скопировать таблицу с шириной столбцов и высотой строк

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

При копировании на другой лист привычным методом Ctrl+C – Ctrl+V. Получаем нежелательный результат:

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

Способ1:Используем специальную вставку

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

Она получила исходные параметры и выглядит идеально точно.

Способ 2: Выделяем столбцы перед копированием

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

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

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

Вставка значений формул сохраняя формат таблицы

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

Чтобы решить такую задачу следует выполнить 2 операции, используя специальную вставку в Excel.

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

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

Полезный совет! Чтобы не выполнять вторую операцию можно воспользоваться инструментом «формат по образцу».

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

Читайте также: