Как выделить таблицу в excel: Как сделать таблицу в excel

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

Как сделать таблицу в Excel, основные правила Блог Ивана Кунпана

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

Что такое Microsoft Excel, его назначение и применение

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

MS Excel – это одно из приложений пакета MS Office. Практически всегда эта программа установлена на компьютере, но достаточно редко используется пользователями. Очень непривычно, что рабочая область разбита на ячейки, которые и являются основными минимальными объектами этого табличного процессора.

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

Работа в этом приложении основана на умении создавать различные таблицы и их обрабатывать.

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

Как сделать таблицу в Excel новичку

Как сделать таблицу в Excel, если вы новичок и только научились делать какие – то элементарные вещи на компьютере, а вам нужно создать таблицу в Экселе.

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

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

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

Затем, прописываем заголовки столбцов, устанавливаем нужную ширину. Ширину легко откорректировать. Нужно выделить столбец, нажать правую кнопку мыши, таким образом вызвав контекстное меню столбца и выбрать ширина столбца. Установить нужную.

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

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

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

Таким образом, шаблон таблицы готов. Можно вводить данные.

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

Я дал алгоритм создания стандартной таблицы в MS Excel.

Заключение

Я думаю вам стало понятно, как сделать таблицу в Excel. Нет ничего сложного и невыполнимого. Начинайте и делайте. И всё получится. Очень удобно, что, изучив Эксель, вы свободно сможете работать в Google документах в таблицах. На эту тему есть статья на блоге «Гугл документы и таблицы онлайн». Удачи!

С уважением, Иван Кунпан.

Просмотров: 2151

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

Самое простое, что можно сделать в Microsoft Excel — создать таблицу. Рассмотрим этот процесс на примере Microsoft Office 2007.

Первое, с чего нужно начать — рассчитать необходимое количество столбцов и строк. Допустим, нам необходимо 5 столбцов и 6 строк. Соответственно, мы выделяем столбцы А-Е и строки 1-6.

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

Выделяем, кликаем по выделенному участку правой клавишей мыши и в контекстном меню выбираем Формат ячеек.

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

Для этого выбираем Тип линии жирный (самую жирную полосу), в разделе Все выбираем Внешние. Так мы создали внешние рамки таблицы, внутренние разделители можно сделать сразу же, для этого в Тип линии выбираем обычную полосу, в разделе Все выбираем Внутренние. Цвет можно оставить Авто — по умолчанию это черный. Теперь можно нажать Ок. Результат будет таким:

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

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

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

Границы настраивать по своему усмотрению можно и через верхнюю панель инструментов:

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

Если нужно залить столбец или строку цветом, то выделяем участок, нажимаем правой клавишей мыши и выбираем Формат ячеек. В открывшемся окне выбираем вкладку Заливка и выбираем нужный цвет. Результат будет примерно таким:

Готово! Получившуюся таблицу теперь можно заполнить, сохранить или распечатать.

Как сделать таблицу в Excel. Пошаговая инструкция

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

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

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

Содержание

Как работать в Excel с таблицами. Пошаговая инструкция

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

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

1. Выделите область ячеек для создания таблицы

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

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

На вкладке “Вставка” нажмите кнопку “Таблица”.

3. Выберите диапазон ячеек

Во всплывающем вы можете скорректировать расположение данных, а также настроить отображение заголовков. Когда все готово, нажмите “ОК”.

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

Форматирование таблицы в Excel

Для настройки формата таблицы в Экселе доступны предварительно настроенные стили. Все они находятся на вкладке “Конструктор” в разделе “Стили таблиц”:

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

Помимо цветовой гаммы, в меню “Конструктора” таблиц можно настроить:

• Отображение строки заголовков – включает и отключает заголовки в таблице;
• Строку итогов – включает и отключает строку с суммой значений в колонках;
• Чередующиеся строки – подсвечивает цветом чередующиеся строки;
• Первый столбец – выделяет “жирным” текст в первом столбце с данными;
• Последний столбец – выделяет “жирным” текст в последнем столбце;
• Чередующиеся столбцы – подсвечивает цветом чередующиеся столбцы;
• Кнопка фильтра – добавляет и убирает кнопки фильтра в заголовках столбцов.

Как добавить строку или столбец в таблице Excel

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

• Если вы хотите удалить строку или столбец в таблице, то спуститесь по списку в сплывающем окне до пункта “Удалить” и выберите “Столбцы таблицы”, если хотите удалить столбец или “Строки таблицы”, если хотите удалить строку.

Как отсортировать таблицу в Excel

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

В окне выберите по какому принципу отсортировать данные: “по возрастанию”, “по убыванию”, “по цвету”, “числовым фильтрам”.

Как отфильтровать данные в таблице Excel

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

• “Текстовый фильтр” отображается когда среди данных колонки есть текстовые значения;
• “Фильтр по цвету” так же как и текстовый, доступен когда в таблице есть ячейки, окрашенные в отличающийся от стандартного оформления цвета;
• “Числовой фильтр” позволяет отобрать данные по параметрам: “Равно…”, “Не равно…”, “Больше…”, “Больше или равно…”, “Меньше…”, “Меньше или равно…”, “Между…”, “Первые 10…”, “Выше среднего”, “Ниже среднего”, а также настроить собственный фильтр.
• Во всплывающем окне, под “Поиском” отображаются все данные, по которым можно произвести фильтрацию, а также одним нажатием выделить все значения или выбрать только пустые ячейки.

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

Как посчитать сумму в таблице Excel

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

В списке окна выберите пункт “Таблица” => “Строка итогов”:

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

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

Как в Excel закрепить шапку таблицы

Таблицы, с которыми приходится работать, зачастую крупные и содержат в себе десятки строк. Прокручивая таблицу “вниз” сложно ориентироваться в данных, если не видно заголовков столбцов. В Эксель есть возможность закрепить шапку в таблице таким образом, что при прокрутке данных вам будут видны заголовки колонок.

Для того чтобы закрепить заголовки сделайте следующее:

• Перейдите на вкладку “Вид” в панели инструментов и выберите пункт “Закрепить области”:

• Выберите пункт “Закрепить верхнюю строку”:

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

Как перевернуть таблицу в Excel

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

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

• Выделить таблицу целиком (зажав левую клавишу мыши выделить все ячейки таблицы) и скопировать данные (CTRL+C):

• Переместить курсор мыши на свободную ячейку и нажать правую клавишу мыши. В открывшемся меню выбрать “Специальная вставка” и нажать на этом пункте левой клавишей мыши:

• В открывшемся окне в разделе “Вставить” выбрать “значения” и поставить галочку в пункте “транспонировать”:

• Готово! Месяцы теперь размещены по строкам, а фамилии продавцов по колонкам. Все что остается сделать – это преобразовать полученные данные в таблицу.

В этой статье вы ознакомились с принципами работы в Excel с таблицами, а также основными подходами в их создании. Пишите свои вопросы в комментарии!

Автор: · 16.06.2019

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

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

План обучения будет прост:

• сначала рассматриваем различные методы создания таблиц;
• затем занимаемся оформлением, чтобы информация была максимально наглядной и понятной.

Ручной режим

Данный метод самый простой. Делается это следующим образом.

1. Открыв пустой лист, вы увидите большое количество одинаковых ячеек.

1. Выделите любое количество строк и столбцов.

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

1. Сразу после этого у вас появится обычная элементарная табличка.

Теперь можете приступать к заполнению данных.

Существует и другой способ ручного рисования таблицы.

1. Снова нажмите на иконку «Границы». Но на этот раз выберите пункт «Нарисовать сетку».

1. Сразу после этого у вас изменится внешний вид курсора.

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

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

Автоматический режим

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

1. Перейдите на вкладку «Вставка». Нажмите на кнопку «Таблицы» и выберите последний пункт.

Обратите внимание на то, что нам подсказывают о горячих клавишах. В дальнейшем для автоматического создания можно использовать сочетание кнопок Ctrl+T.

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

1. Для этого достаточно просто выделить любую область – координаты подставятся автоматом.

1. Как только вы отпустите курсор, окно примет исходный вид. Нажмите на кнопку «OK».

1. В результате этого будет создана красивая таблица с чередующимися линиями.

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

Дальше можете делать, что душе угодно.

Сводная таблица

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

1. Сначала делаем таблицу и заполняем её какими-нибудь данными. Как это сделать, описано выше.

1. Теперь заходим в главное меню «Вставка». Далее выбираем нужный нам вариант.

1. Сразу после этого у вас появится новое окно.

1. Кликните на первую строчку (поле ввода нужно сделать активным). Только после этого выделяем все ячейки.

Читайте также:  Как закрепить область в Эксель

1. Затем нажимаем на кнопку «OK».

1. В результате этого у вас появится новая боковая панель, где нужно настроить будущую таблицу.

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

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

Только после этого (иконка курсора изменит внешний вид) палец можно отпустить.

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

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

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

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

Иногда не получается правильно подобрать поля для столбцов и строк. И в итоге ничего путного не выходит. Для таких случаев разработчики Microsoft подготовили свои варианты анализа данных.

Работает это очень просто.

1. Первым делом выделяем нужную нам информацию.

1. После этого выбираем соответствующий пункт меню.

1. В результате программа сама проанализирует содержимое ячеек и предложит несколько вариантов.

1. Кликнув на любой из предложенных вариантов и нажав на кнопку «OK», всё будет создано автоматически.

1. В случае примера, мы получили сумму общих затрат, без учета месяцев.

Готовые шаблоны в Excel 2016

Для особо ленивых данная программа позволяет создавать по-настоящему «крутые» таблицы всего одним кликом.

При запуске Экселя вам на выбор предлагаются следующие варианты:

• открыть последние файлы, с которыми вы работали ранее;
• создать новую пустую книгу;
• посмотреть учебник с подробной информацией о возможностях данного ПО;
• выбрать какой-нибудь готовый шаблон по умолчанию;
• продолжить поиск в интернете, если ни один из предложенных дизайнов вам не понравился;
• войти под своей учетной записью Microsoft.

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

Кликаем на какой-нибудь понравившийся вариант.

Нажимаем на кнопку «Создать».

В результате этого вы получаете готовый вариант очень большой и сложной таблицы.

Оформление

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

Рассмотрим вкратце основные манипуляции с ячейками.

Создание заголовка

В качестве примера будем использовать простую таблицу.

1. Сначала переходим на вкладку «Главная» и нажимаем на пункт меню «Вставить строки на лист».

1. Выделяем появившуюся строчку и нажимаем на пункт меню «Объединить ячейки».

1. Далее пишем любой заголовок.

Изменение высоты элементов

Наш заголовок по размеру одинаковый с шапкой. А это не очень красиво. Кроме того, он смотрится невзрачно. Для того чтобы это исправить, нужно перенести курсор на границу 1 и 2 строки. После того, как его внешний вид изменится, сделайте левый клик мыши и потяните вниз.

В результате этого высота строки будет больше.

Выравнивание текста

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

Читайте также:  Округление в Эксель

Кликаем на кнопку «По середине» и получаем желанный результат.

Теперь заголовок смотрится куда лучше.

Изменение стиля

Также рекомендуется изменить шрифт и увеличить кегль (размер по вертикали). Сделать это можно вручную при помощи панели инструментов.

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

Эффект будет очень красивым.

Как вставить новую строку или столбец

Для того чтобы изменить количество элементов в таблице, можно воспользоваться кнопкой «Вставить».

Вы можете добавить:

• ячейки;
• строки;
• столбцы;
• целый лист.

Удаление элементов

Уничтожить ячейку или что-нибудь еще можно точно так же. Для этого есть своя кнопка.

Заливка ячеек

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

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

Формат элементов

При желании можно сделать с таблицей что угодно. Для этого достаточно нажать на кнопку «Формат».

В результате этого вы сумеете:

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

Формат содержимого

Если нажать на последний из вышеописанных пунктов, то появится следующее:

Благодаря этому инструменту можно:

• изменить формат отображаемых данных;
• указать выравнивание;

• выбрать любой шрифт;

• изменить границы таблицы;

• «поиграть» с заливкой;

• установить защиту.

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

Именно благодаря возможности использовать функции автоподсчёта (умножение, сложение и так далее), Microsoft Excel и стал мощным инструментом.

Полную информацию о формулах в Экзеле лучше всего посмотреть на официальной странице справки.

Кроме этого, рекомендуется ознакомиться с описанием всех функций.

Рассмотрим самую простую операцию – умножение ячеек.

1. Для начала подготовим поле для экспериментов.

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

1. Введите там следующую команду.

=C3*D3

1. Теперь нажмите на клавишу Enter. После этого наведите курсор на правый нижний угол этой ячейки до тех пор, пока не изменится его внешний вид. Затем зажмите пальцем левый клик мыши и потяните вниз до последней строки.

1. В результате автоподстановки формула попадёт во все ячейки.

Значения в колонке «Общая стоимость» будут зависеть от полей «Количество» и «Стоимость 1 кг». Это и есть прелесть динамики.

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

1. Сначала выделяем значения. Затем нажимаем на кнопку «Автосуммы», которая расположена на вкладке «Главная».

1. В результате этого ниже появится общая сумма всех чисел.

Использование графики

Иногда в ячейках вместо текста используют фотографии. Сделать это очень легко.

Выбираем пустой элемент. Переходим на вкладку «Вставка». Выбираем раздел «Иллюстрации». Кликаем на пункт «Рисунки».

1. Указываем файл и кликаем на кнопку «Вставить».

1. Результат вас не разочарует. Смотрится очень красиво (в зависимости от подобранного рисунка).

Экспорт в Word

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

1. Выделите область данных.

1. Нажмите на горячие клавиши Ctrl+C.
2. Откройте документ
3. Теперь используем кнопки Ctrl+V.
4. Итог будет следующим.

Читайте также:  Как из Excel перенести таблицу в Word

Онлайн-сервисы

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

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

Способы печати

Распечатка документов Word, как правило, задача несложная. Но с таблицами в Excel всё иначе. Самая большая проблема заключается в том, что «на глаз» сложно определить границы печати. И очень часто в принтере появляются практически пустые листы, на которых находится всего 1-2 строки таблицы.

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

1. Открываем документ. Он выглядит вполне обычно.

1. Далее нажмите на горячие клавиши Ctrl+P. В появившемся окне мы видим, что информация не помещается на один лист. У нас исчезла графа «Общая стоимость». Кроме того, внизу нам подсказывают, что при печати будет использовано 2 страницы.

В версии 2007 года, для этого нужно было нажать на кнопку «Просмотр».

1. Для отмены нажимаем горячую клавишу Esc. В результате появится вертикальная пунктирная линия, которая показывает границы печати.

Увеличить пространство при печати можно следующим образом.

1. Первым делом уменьшаем поля. Для этого переходим на вкладку «Разметка страницы». Кликаем на кнопку «Поля» и выбираем самый «Узкий» вариант.

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

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

1. Снова нажимаем на Ctrl+P. Теперь мы видим, что информация помещается на один лист.

Отличие версий продукции Майкрософт

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

Пример рабочей области Excel 2003.

В современных 2007, 2010, 2013, а тем более 2016 версиях всё намного «круче».

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

Сейчас же для них отведена целая вкладка.

Ограничения и возможности разных версий

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

Пример самых основных параметров.

Этот список довольно длинный. Поэтому стоит перейти по ссылке и ознакомиться с остальными.

Обратите внимание, что версию 2003 года даже не рассматривают, так как её поддержка прекращена.

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

Заключение

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

Видеоинструкция

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

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

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

Как создать таблицу в Excel для чайников

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

Посмотрите внимательно на рабочий лист табличного процессора:

Это множество ячеек в столбцах и строках. По сути – таблица. Столбцы обозначены латинскими буквами. Строки – цифрами. Если вывести этот лист на печать, получим чистую страницу. Без всяких границ.

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

Как выделить столбец и строку

Чтобы выделить весь столбец, щелкаем по его названию (латинской букве) левой кнопкой мыши.

Для выделения строки – по названию строки (по цифре).

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

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

Как изменить границы ячеек

Если информация при заполнении таблицы не помещается нужно изменить границы ячеек:

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

Для изменения ширины столбцов и высоты строк сразу в определенном диапазоне выделяем область, увеличиваем 1 столбец /строку (передвигаем вручную) – автоматически изменится размер всех выделенных столбцов и строк.

Примечание. Чтобы вернуть прежний размер, можно нажать кнопку «Отмена» или комбинацию горячих клавиш CTRL+Z. Но она срабатывает тогда, когда делаешь сразу. Позже – не поможет.

Чтобы вернуть строки в исходные границы, открываем меню инструмента: «Главная»-«Формат» и выбираем «Автоподбор высоты строки»

Для столбцов такой метод не актуален. Нажимаем «Формат» — «Ширина по умолчанию». Запоминаем эту цифру. Выделяем любую ячейку в столбце, границы которого необходимо «вернуть». Снова «Формат» — «Ширина столбца» — вводим заданный программой показатель (как правило это 8,43 — количество символов шрифта Calibri с размером в 11 пунктов). ОК.

Как вставить столбец или строку

Выделяем столбец /строку правее /ниже того места, где нужно вставить новый диапазон. То есть столбец появится слева от выделенной ячейки. А строка – выше.

Нажимаем правой кнопкой мыши – выбираем в выпадающем меню «Вставить» (или жмем комбинацию горячих клавиш CTRL+SHIFT+»=»).

Отмечаем «столбец» и жмем ОК.

Совет. Для быстрой вставки столбца нужно выделить столбец в желаемом месте и нажать CTRL+SHIFT+»=».

Все эти навыки пригодятся при составлении таблицы в программе Excel. Нам придется расширять границы, добавлять строки /столбцы в процессе работы.

Пошаговое создание таблицы с формулами

1. Заполняем вручную шапку – названия столбцов. Вносим данные – заполняем строки. Сразу применяем на практике полученные знания – расширяем границы столбцов, «подбираем» высоту для строк.
2. Чтобы заполнить графу «Стоимость», ставим курсор в первую ячейку. Пишем «=». Таким образом, мы сигнализируем программе Excel: здесь будет формула. Выделяем ячейку В2 (с первой ценой). Вводим знак умножения (*). Выделяем ячейку С2 (с количеством). Жмем ВВОД.
3. Когда мы подведем курсор к ячейке с формулой, в правом нижнем углу сформируется крестик. Он указываем на маркер автозаполнения. Цепляем его левой кнопкой мыши и ведем до конца столбца. Формула скопируется во все ячейки.
4. Обозначим границы нашей таблицы. Выделяем диапазон с данными. Нажимаем кнопку: «Главная»-«Границы» (на главной странице в меню «Шрифт»). И выбираем «Все границы».

Теперь при печати границы столбцов и строк будут видны.

С помощью меню «Шрифт» можно форматировать данные таблицы Excel, как в программе Word.

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

Как создать таблицу в Excel: пошаговая инструкция

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

Сделаем «умную» (динамическую) таблицу:

1. Переходим на вкладку «Вставка» — инструмент «Таблица» (или нажмите комбинацию горячих клавиш CTRL+T).
2. В открывшемся диалоговом окне указываем диапазон для данных. Отмечаем, что таблица с подзаголовками. Жмем ОК. Ничего страшного, если сразу не угадаете диапазон. «Умная таблица» подвижная, динамическая.

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

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

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

Как работать с таблицей в Excel

С выходом новых версий программы работа в Эксель с таблицами стала интересней и динамичней. Когда на листе сформирована умная таблица, становится доступным инструмент «Работа с таблицами» — «Конструктор».

Здесь мы можем дать имя таблице, изменить размер.

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

Возможности динамических электронных таблиц MS Excel огромны. Начнем с элементарных навыков ввода данных и автозаполнения:

1. Выделяем ячейку, щелкнув по ней левой кнопкой мыши. Вводим текстовое /числовое значение. Жмем ВВОД. Если необходимо изменить значение, снова ставим курсор в эту же ячейку и вводим новые данные.
2. При введении повторяющихся значений Excel будет распознавать их. Достаточно набрать на клавиатуре несколько символов и нажать Enter.
3. Чтобы применить в умной таблице формулу для всего столбца, достаточно ввести ее в одну первую ячейку этого столбца. Программа скопирует в остальные ячейки автоматически.
4. Для подсчета итогов выделяем столбец со значениями плюс пустая ячейка для будущего итога и нажимаем кнопку «Сумма» (группа инструментов «Редактирование» на закладке «Главная» или нажмите комбинацию горячих клавиш ALT+»=»).

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

Иногда пользователю приходится работать с огромными таблицами. Чтобы посмотреть итоги, нужно пролистать не одну тысячу строк. Удалить строки – не вариант (данные впосл

Выделить ячейки — Excel

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

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

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

Создать стиль ячеек для выделения ячеек

1. Щелкните Home > Новые стили ячеек .

2. В поле Имя стиля введите соответствующее имя для нового стиля ячейки.

   Совет: Например, введите Выделите .

3. Щелкните Формат .

4. В диалоговом окне Формат ячеек на вкладке Заливка выберите цвет, который вы хотите использовать для выделения, а затем нажмите ОК .

5. Щелкните OK , чтобы закрыть диалоговое окно Style .

   Новый стиль будет добавлен в Custom в поле стилей ячеек.

6. На листе выберите ячейки или диапазоны ячеек, которые нужно выделить. Как выделить ячейки?

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

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

Используйте Format Painter для выделения других ячеек

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

2. На вкладке Home в группе Clipboard дважды щелкните Format Painter , а затем перетащите указатель мыши на любое количество ячеек или диапазонов ячеек, которые вы хотите выделить.

3. Когда вы закончите, щелкните Format Painter еще раз или нажмите ESC, чтобы выключить его.

Отображение определенных данных другим цветом или форматом шрифта

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

   Как выбрать данные в ячейке

   Выбрать содержимое ячейки	Сделай это
   В ячейке	Дважды щелкните ячейку и перетащите ее по содержимому ячейки, которую вы хотите выделить.
   В строке формул	Щелкните ячейку и перетащите ее по содержимому ячейки, которую вы хотите выделить в строке формул.
   С помощью клавиатуры	Нажмите F2, чтобы изменить ячейку, используйте клавиши со стрелками, чтобы установить точку вставки, а затем нажмите клавиши SHIFT + СТРЕЛКА, чтобы выбрать содержимое.

2. На вкладке Home в группе Font выполните одно из следующих действий:

   • Чтобы изменить цвет текста, щелкните стрелку рядом с полем Цвет шрифта а затем в разделе Цвета темы или Стандартные цвета щелкните цвет, который хотите использовать.

   • Чтобы применить последний выбранный цвет текста, щелкните Цвет шрифта .

   • Чтобы применить цвет, отличный от доступных цветов темы и стандартных цветов, щелкните Дополнительные цвета , а затем определите цвет, который вы хотите использовать, на вкладке Standard или Custom диалогового окна Colors .

   • Чтобы изменить формат, щелкните Полужирный , Курсив , или Подчеркнутый .

     Сочетание клавиш. Вы также можете нажать CTRL + B, CTRL + I или CTRL + U.

Применение затенения к чередующимся строкам или столбцам на листе

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

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

= МОД (СТРОКА (), 2) = 0

Примечание: Если вы хотите применить затенение к альтернативным столбцам вместо альтернативных строк, введите вместо этого = MOD (COLUMN (), 2) = 0 .

1. На рабочем листе выполните одно из следующих действий:

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

   • Чтобы применить затенение ко всему рабочему листу, нажмите кнопку Выбрать все .

2. На вкладке Домашняя страница в группе Стили щелкните стрелку рядом с Условное форматирование , а затем щелкните Новое правило.

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

4. В поле «Значения формата , где эта формула верна» введите = MOD (ROW (), 2) = 0 , как показано на следующем рисунке.

5. Щелкните Формат .

6. В диалоговом окне Формат ячеек щелкните вкладку Заполнить .

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

   На этом этапе только что выбранный цвет должен появиться в окне Preview в диалоговом окне New Formatting Rule .

8. Чтобы применить форматирование к ячейкам на листе, нажмите ОК

Примечание: Чтобы просмотреть или отредактировать правило условного форматирования, на вкладке Home в группе Styles щелкните стрелку рядом с полем Conditional Formatting , а затем щелкните Manage Rules .

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

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

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

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

2. На вкладке Home в группе Styles щелкните Формат в виде таблицы .

3. Под Light , Medium или Dark щелкните стиль таблицы, который хотите использовать.

   Совет. Пользовательские стили таблиц доступны в Пользовательские после создания одного или нескольких из них. Дополнительные сведения о том, как создать собственный стиль таблицы, см. В разделе Форматирование таблицы Excel.

4. В диалоговом окне Форматировать как таблицу нажмите ОК .

   Обратите внимание, что флажок Banded Rows установлен по умолчанию в группе Параметры стиля таблицы .

   Если вы хотите применить затенение к альтернативным столбцам вместо альтернативных строк, вы можете снять этот флажок и вместо этого выбрать Banded Columns .

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

6. На вкладке Design в группе Инструменты щелкните Преобразовать в диапазон .

   Совет: Вы также можете щелкнуть правой кнопкой мыши таблицу, выбрать Таблица , а затем Преобразовать в диапазон .

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

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

1. Выберите любую ячейку в таблице.

2. Щелкните вкладку Table Design и в разделе Style Options установите флажок Banded Columns .

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

Выделение строк на основе значения ячейки в Excel (условное форматирование)

Посмотреть видео — выделение строк на основе значений ячеек в Excel

Если вы предпочитаете читать письменные инструкции, ниже приводится руководство.

Условное форматирование позволяет форматировать ячейку (или диапазон ячеек) на основе значения в ней.

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

Чтобы дать вам пример, ниже у меня есть набор данных, в котором я выделил все строки, в которых имя торгового представителя — Боб.

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

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

Выделить строки на основе критериев текста

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

Вот шаги, чтобы сделать это:

1. Выберите весь набор данных (A2: F17 в этом примере).
2. Щелкните вкладку «Главная».
3. В группе «Стили» щелкните «Условное форматирование».
4. Щелкните «Новые правила».
5. В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
6. В поле формулы введите следующую формулу: = $ C2 = «Боб»
7. Нажмите кнопку «Формат».
8. В открывшемся диалоговом окне установите цвет, которым должна выделяться строка.
9. Нажмите ОК.

Это выделит все строки, где имя торгового представителя — «Боб».

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

Как это работает?

Условное форматирование проверяет каждую ячейку на соответствие указанному нами условию, а именно: = $ C2 = «Боб»

Таким образом, когда он анализирует каждую ячейку в строке A2, он проверяет, имеет ли ячейка C2 имя Боб или не.Если это так, эта ячейка выделяется, иначе — нет.

Обратите внимание, что здесь хитрость заключается в использовании знака доллара ($) перед алфавитом столбца ($ C1). Сделав это, мы заблокировали столбец, чтобы он всегда был C. Таким образом, даже когда ячейка A2 проверяется на наличие формулы, она проверяет C2, а когда A3 проверяется на условие, она проверяет C3.

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

Связанные : абсолютные, относительные и смешанные ссылки в Excel.

Выделение строк на основе критериев числа

В приведенном выше примере мы увидели, как проверить имя и выделить всю строку.

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

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

Вот шаги, чтобы сделать это:

1. Выберите весь набор данных ( A2: F17 в этом примере).
2. Щелкните вкладку «Главная».
3. В группе «Стили» щелкните «Условное форматирование».
4. Щелкните «Новые правила».
5. В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
6. В поле формулы введите следующую формулу: = $ D2> = 15
7. Нажмите кнопку «Форматировать». В открывшемся диалоговом окне установите цвет, которым должна быть выделена строка.
8. Нажмите ОК.

Это выделит все строки, в которых количество больше или равно 15.

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

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

 = $ A2> ДАТА (2018,7,10) 

Выделить строки на основе a Несколько критериев (И / ИЛИ)

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

Например, если вы хотите выделить все строки, в которых имя торгового представителя — «Боб», а количество больше 10, вы можете сделать это, выполнив следующие действия:

1. Выберите весь набор данных (A2: F17 в этом примере).
2. Щелкните вкладку «Главная».
3. В группе «Стили» щелкните «Условное форматирование».
4. Щелкните «Новые правила».
5. В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
6. В поле формулы введите следующую формулу: = И ($ C2 = «Bob», $ D2> 10)
7. Нажмите кнопку «Форматировать». В открывшемся диалоговом окне установите цвет, которым должна быть выделена строка.
8. Нажмите ОК.

В этом примере выделяются только те строки, где выполняются оба условия (это делается с помощью формулы И).

Аналогичным образом можно использовать условие ИЛИ. Например, если вы хотите выделить строки, в которых либо торговым представителем является Боб, либо количество больше 15, вы можете использовать следующую формулу:

  = OR ($ C2 = "Bob", $ D2> 15)  

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

Выделение строк разным цветом в зависимости от нескольких условий

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

Например, вы можете выделить все строки, где количество больше 20, зеленым, а где количество больше 15 (но меньше 20) оранжевым.

Для этого нужно создать два правила условного форматирования и задать приоритет.

Вот шаги, чтобы сделать это:

1. Выберите весь набор данных (A2: F17 в этом примере).
2. Щелкните вкладку «Главная».
3. В группе «Стили» щелкните «Условное форматирование».
4. Щелкните «Новые правила».
5. В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
6. В поле формулы введите следующую формулу: = $ D2> 15
7. Нажмите кнопку «Формат». В открывшемся диалоговом окне установите оранжевый цвет.
8. Нажмите ОК.
9. В диалоговом окне «Диспетчер правил условного форматирования» нажмите «Новое правило».
10. В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
11. В поле формулы введите следующую формулу: = $ D2> 20
12. Нажмите кнопку «Форматировать». В открывшемся диалоговом окне установите цвет на зеленый.
13. Нажмите ОК.
14. Нажмите Применить (или ОК).

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

Понимание порядка правил:

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

В приведенном выше примере условие зеленого цвета выше условия оранжевого цвета.

Если все наоборот, все строки будут окрашены только в оранжевый цвет.

Почему?

Потому что строка, в которой количество больше 20 (скажем, 23), удовлетворяет обоим нашим условиям (= $ D2> 15 и = $ D2> 20). А поскольку оранжевое состояние находится наверху, ему отдается предпочтение.

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

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

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

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

Вот шаги, чтобы сделать это:

1. Выберите весь набор данных (A2: F17 в этом примере).
2. Щелкните вкладку «Главная».
3. В группе «Стили» щелкните «Условное форматирование».
4. Щелкните «Новые правила».
5. В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
6. В поле формулы введите следующую формулу: = COUNTIF ($ A2: $ F2, ””)> 0
7. Нажмите кнопку «Формат». В открывшемся диалоговом окне установите оранжевый цвет.
8. Нажмите ОК.

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

Если какая-либо из ячеек пуста, выделяется вся строка.

Связано: Прочтите это руководство, если вы хотите выделить только пустые ячейки.

Выделить строки на основе раскрывающегося списка

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

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

Ниже приведен пример, в котором я выбираю имя из раскрывающегося списка, и все строки с этим именем выделяются:

Вот шаги для создания этого:

1. Создайте раскрывающийся список в ячейка A2. Здесь я использовал имена торговых представителей для создания раскрывающегося списка. Вот подробное руководство о том, как создать раскрывающийся список в Excel.
2. Выберите весь набор данных (в данном примере C2: h27).
3. Щелкните вкладку «Главная».
4. В группе «Стили» щелкните «Условное форматирование».
5. Щелкните «Новые правила».
6. В диалоговом окне «Новое правило форматирования» нажмите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
7. В поле формулы введите следующую формулу: = $ E2 = $ A $ 2
8. Нажмите кнопку «Форматировать». В открывшемся диалоговом окне установите оранжевый цвет.
9. Нажмите ОК.

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

Хотите узнать больше о том, как искать и выделять в Excel? Посмотрите видео ниже.

Вам также могут понравиться следующие руководства по Excel:

Выделение пустых ячеек в Excel (менее чем за 10 секунд)

Посмотреть видео — Как выделить пустые ячейки в Excel

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

Одна из таких проверок — найти и выделить пустые ячейки в Excel.

Пустые ячейки в наборе данных могут быть пустыми по многим причинам:

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

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

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

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

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

Я собираюсь продемонстрировать эти примеры с небольшим набором данных. Однако вы можете использовать те же методы и с большими наборами данных.

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

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

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

Это выделит все пустые ячейки в наборе данных.

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

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

Выбор и выделение пустых ячеек в Excel

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

Вот шаги, чтобы выбрать и выделить пустые ячейки в Excel:

• Выберите данные.
• Нажмите клавишу F5. Откроется диалоговое окно «Перейти к».
• В диалоговом окне «Перейти» нажмите кнопку «Специальные».
• В диалоговом окне «Перейти к специальному» выберите «Пробелы».
• Нажмите ОК.Это выберет все пустые ячейки в наборе данных.
• Выделив все пустые ячейки, выделите их, присвоив им цвет.

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

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

Использование VBA для выделения пустых ячеек в Excel

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

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

Вот код VBA, который выделяет пустые ячейки в выбранном наборе данных:

 'Код от Sumit Bansal (https: // trumpexcel.com)
Sub HighlightBlankCells ()
Dim Dataset As Range
Установить набор данных = Выбор
Набор данных.SpecialCells (xlCellTypeBlanks) .Interior.Color = vbRed
End Sub 

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

• Перейдите на вкладку «Разработчик» и щелкните Visual Basic (или нажмите ALT + F11).
• В редакторе Vb в Project Explorer щелкните правой кнопкой мыши любое имя листа (если вы не видите Project Explorer, нажмите CONTROL + R).
• Перейдите во вкладку «Вставить» и щелкните «Модуль».
• В окне кода модуля скопируйте и вставьте код VBA.
• Закройте редактор VB.

Как запустить код VBA (макрос)?

После того, как вы скопировали и вставили этот макрос, вы можете использовать его несколькими способами.

Использование диалогового окна «Макрос»

Вот шаги для запуска этого макроса в диалоговом окне «Макрос»:

1. Выберите данные.
2. Перейдите на вкладку «Разработчик» и нажмите «Макросы».
3. В диалоговом окне «Макрос» выберите макрос «HighlightBlankCells» и нажмите «Выполнить».

Использование редактора VB

Вот шаги для запуска этого макроса с помощью редактора VB:

1. Выберите данные.
2. Перейдите на вкладку Разработчик и щелкните Visual Basic.
3. В редакторе VB щелкните в любом месте кода.
4. Нажмите кнопку с зеленым треугольником на панели инструментов (или нажмите клавишу F5).

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

Вам также могут понравиться следующие учебные пособия по Excel:

Выделение точек данных в Excel одним нажатием кнопки

Посмотреть видео — выделение точек данных в Excel одним нажатием кнопки

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

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

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

Follow Along .. Загрузите диаграмму

Выделите точки данных в линейной диаграмме Excel

Вот как вы можете создать этот тип диаграмм:

1. Получите данные на месте. Для этого графика у меня есть цифры роста выручки за каждый квартал 2012-2015 годов.
   
2. Выделите все данные, перейдите в Вставка -> Диаграммы -> Линия с маркерами. Это позволит вставить линейную диаграмму с тремя разными линиями для каждого года.
   
3. Перейдите в меню Вставка -> Иллюстрации -> Фигуры -> Прямоугольник со скругленными углами.Щелкните в любом месте рабочего листа, и он вставит прямоугольник со скругленными углами на рабочий лист.
   
4. Вставьте еще 2 прямоугольника со скругленными углами и поместите их на диаграмму. Введите название серии (годы) в формах, как показано ниже:
   
5. Выберите прямоугольник для 2013 года, перейдите в поле «Имя» и введите 2013. Аналогичным образом сделайте то же самое для полей 2014 и 2015 годов (поле имени находится в поле «Имя»). слева от строки формул).
   
6. В ячейке F2 введите 2013 (вы можете ввести любой год из данных).
7. В ячейке F3 введите следующую комбинацию функций ИНДЕКС, СТРОКИ и ПОИСКПОЗ (и перетащите ее для ячеек F3: F6)

    = ИНДЕКС ($ B $ 3: $ D $ 6, ROWS ($ E $ 3: E3), ПОИСКПОЗ ( $ F $ 2, $ B $ 2: $ D $ 2,0)) 

8. Выделите ячейки F3: F6 и скопируйте их (нажмите Control + C), выберите диаграмму и вставьте (Ctrl + v). Это создаст две линии для одного и того же года (при копировании обратите внимание, что цвет линии выбранного года изменится).
9. Выберите строку года (для которого вы скопировали данные), щелкните правой кнопкой мыши и выберите «Форматировать данные ряда».В серии данных формата:
   • Изменить цвет линии на «Нет линии»
     
   • В параметрах маркера внесите следующие изменения
     • Встроенный тип: круглая форма
     • Встроенный размер: 15
   • Изменить заливку маркера без заливки
     
   • Измените цвет границы маркера (я использовал красный цвет), ширину и тип черточки.
     
   • Щелкните правой кнопкой мыши любой из круглых маркеров и выберите «Добавить метки данных». Отформатируйте его так, чтобы отобразить проценты

Если вы выполнили все вышеперечисленные шаги, у вас будет что-то, как показано ниже:

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

Код VBA

Мы будем использовать код VBA для выполнения двух задач:

• Измените значение года в ячейке F3 при щелчке по фигуре и
• Измените цвет выбранной фигуры

Просто скопируйте следующее код в редакторе VB.

 Sub Select Год 2013 ()
Диапазон («F2»). Значение = 2013.
ActiveSheet.Shapes ("2013"). Fill.ForeColor.RGB = RGB (176, 196, 222)
ActiveSheet.Shapes ("2014"). Fill.ForeColor.RGB = RGB (255, 255, 255)
ActiveSheet.Фигуры ("2015"). Fill.ForeColor.RGB = RGB (255, 255, 255)
Конец Sub 

 Sub Select Год 2014 ()
Диапазон («F2»). Значение = 2014.
ActiveSheet.Shapes ("2013"). Fill.ForeColor.RGB = RGB (255, 255, 255)
ActiveSheet.Shapes («2014»). Fill.ForeColor.RGB = RGB (176, 196, 222).
ActiveSheet.Shapes ("2015"). Fill.ForeColor.RGB = RGB (255, 255, 255)
Конец подписки

Sub Select Год 2015 ()
Диапазон («F2»). Значение = 2015.
ActiveSheet.Shapes ("2013"). Fill.ForeColor.RGB = RGB (255, 255, 255)
ActiveSheet.Фигуры ("2014"). Fill.ForeColor.RGB = RGB (255, 255, 255)
ActiveSheet.Shapes ("2015"). Fill.ForeColor.RGB = RGB (176, 196, 222)
End Sub 

Чтобы скопировать этот код:

• Нажмите Alt + F11. Откроется редактор VBE.
• Перейдите во вкладку «Вставить» и щелкните «Модуль». Это вставит модуль.
• Дважды щелкните значок модуля и вставьте код в область кода справа.

Назначить макросы кнопкам

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

• Щелкните фигуру правой кнопкой мыши и выберите «Назначить макрос».
• В диалоговом окне «Назначить макрос» выберите макрос и нажмите «ОК».
  

Примечание. Поскольку эта книга содержит макрос, сохраните его как файл формата .xlsm или .xls.

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

Скачать файл

Подробнее о динамических уроках по построению диаграмм Excel:

Как выделить дубликаты в Excel 2016

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

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

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

Далее в этом руководстве вы найдете несколько способов выделить повторяющиеся записи в зависимости от вашей конкретной задачи. Эти методы работают во всех версиях Excel 2016, Excel 2013, Excel 2010 и ниже.

Как выделить дубликаты в Excel с помощью встроенного правила (с 1 ^st вхождений)

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

1. Выберите данные, которые нужно проверить на дублирование. Это может быть столбец, строка или диапазон ячеек.
2. На вкладке Home в группе Стили щелкните Условное форматирование > Правила выделения ячеек > Дублирующиеся значения…
   
3. Откроется диалоговое окно «Дубликаты значений » , в котором по умолчанию выбран формат «Светло-красная заливка» и «Темно-красный текст».Чтобы применить формат по умолчанию, просто нажмите OK .

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

Наконечник. Чтобы выделить уникальные значения, выберите Уникальный в левом поле.

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

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

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

Как выделить дубликаты без 1 ^st вхождений

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

1. На вкладке Home в группе Стили щелкните Условное форматирование > Новое правило> Используйте формулу, чтобы определить, какие ячейки нужно форматировать .
2. В значениях формата , где эта формула верна, поле введите формулу, подобную этой:
   = СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)> 1

   Где A2 — самая верхняя ячейка выбранного диапазона.

3. Нажмите кнопку Формат… и выберите желаемый цвет заливки и / или шрифта.
4. Наконец, нажмите ОК , чтобы сохранить и применить правило.

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

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

Как показать 3 ^rd , 4 ^th и все последующие повторяющиеся записи

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

Чтобы выделить 3 ^rd и все последующие повторяющихся экземпляров, создайте правило условного форматирования на основе этой формулы:

= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)> = 3

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

= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)> = 4

Чтобы выделить только определенные вхождения, используйте оператор , равный (=).Например, чтобы выделить только 2 ^и экземпляров , вы должны использовать следующую формулу:

= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2) = 2

Как выделить дубликаты в диапазоне (несколько столбцов)

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

Выделить дубликаты в нескольких столбцах, включая 1 ^st вхождений

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

Или создайте правило условного форматирования с помощью этой формулы:

СЧЁТЕСЛИ ( диапазон , верхняя_ячейка )> 1

Например, чтобы выделить дубликаты в диапазоне A2: C8, формула выглядит следующим образом:

= СЧЁТЕСЛИ ($ A $ 2: $ C $ 8; A2)> 1

Обратите внимание на использование абсолютных ссылок на ячейки для диапазона ($ A $ 2: $ C $ 8) и относительных ссылок для верхней ячейки (A2).

Выделить дубликаты в нескольких столбцах без 1 ^st вхождений

Решение для этого сценария намного сложнее, неудивительно, что в Excel нет встроенного правила для этого 🙂

Чтобы выделить повторяющиеся записи в нескольких столбцах, игнорируя вхождения 1 ^st , вам необходимо создать 2 правила со следующими формулами:

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

Здесь вы используете ту же формулу, что и мы, чтобы выделить дубликаты без 1 ^st вхождений в одном столбце (подробные инструкции можно найти здесь).

В этом примере мы создаем правило для A2: A8 с этой формулой:

= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)> 1

В результате повторяющиеся элементы без 1 ^st вхождений выделяются в крайнем левом столбце диапазона (в нашем случае такой элемент только один):

Правило 2.Применяется ко всем последующим столбцам

Чтобы выделить дубликаты в оставшихся столбцах (B2: C8), используйте эту формулу:

= СЧЁТЕСЛИ (A $ 2: $ A $ 8; B2) + СЧЁТЕСЛИ (B $ 2: B2, B2)> 1

В приведенной выше формуле первая функция СЧЁТЕСЛИ подсчитывает количество вхождений данного элемента в первом столбце, а вторая СЧЁТЕСЛИ делает то же самое для всех последующих столбцов. Затем вы складываете эти числа и проверяете, больше ли сумма 1.

В результате все повторяющиеся элементы, за исключением их 1 ^st вхождений, найдены и выделены:

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

Другое возможное решение — добавить пустой столбец слева от набора данных и объединить приведенные выше формулы в одну формулу, например:

= ЕСЛИ (СТОЛБЦЫ ($ B2: B2)> 1, СЧЁТЕСЛИ (A $ 2: $ B $ 8, B2), 0) + СЧЁТЕСЛИ (B $ 2: B2, B2)> 1

Где B2 — верхняя ячейка с данными в столбце 2 ^и целевого диапазона.

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

• Для первого столбца (B) условие ЕСЛИ никогда не выполняется, поэтому применяется только вторая функция СЧЁТЕСЛИ, которая является обычной формулой, которую мы уже использовали для поиска дубликатов без 1 ^st вхождений в одном столбце.
• Для всех последующих столбцов (C2: D8) ключевым моментом является разумное использование абсолютных и относительных ссылок в двух функциях СЧЁТЕСЛИ. Чтобы упростить понимание, я скопировал его в столбец G, чтобы вы могли видеть, как формула изменяется при применении к другим ячейкам:
  

Поскольку условие ЕСЛИ всегда ИСТИНА для всех столбцов, кроме первого (количество столбцов больше 1), формула действует следующим образом:

• Подсчитывает количество появлений данного элемента (D5 на скриншоте выше) во всех столбцах слева от данного столбца: COUNTIF (B $ 2: $ C $ 8, D5)
• Подсчитывает количество вхождений данного элемента в столбце элемента до ячейки элемента: COUNTIF (D $ 2: D5, D5)
• Наконец, формула складывает результаты обеих функций СЧЁТЕСЛИ.Если общее количество больше 1, то есть если существует более одного экземпляра элемента, применяется правило и элемент выделяется.

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

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

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

• Чтобы выделить повторяющиеся строки за исключением 1 ^st вхождений :
  = COUNTIF ($ A $ 2: $ A2, $ A2)> 1
• Чтобы выделить повторяющиеся строки , включая 1 ^st вхождений :
  = COUNTIF ($ A $ 2: $ A $ 15, $ A2)> 1

Где A2 — это первая ячейка, а A15 — последняя использованная ячейка в столбце, который вы хотите проверить на наличие дубликатов.Как видите, главное отличие — это умное использование абсолютных и относительных ссылок на ячейки.
На следующем снимке экрана показаны оба правила в действии:

Как выделить повторяющиеся строки в Excel

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

Для этого используйте функцию СЧЁТЕСЛИМН, которая позволяет сравнивать ячейки по нескольким критериям.Например, чтобы выделить повторяющиеся строки с одинаковыми значениями в столбцах A и B, используйте одну из следующих формул:

• Чтобы выделить повторяющиеся строки , кроме 1 ^st вхождений :
  = COUNTIFS ($ A $ 2: $ A2, $ A2, $ B $ 2: $ B2, $ B2)> 1
• Чтобы выделить повторяющиеся строки с 1 ^st вхождений :
  = COUNTIFS ($ A $ 2: $ A $ 15, $ A2, $ B $ 2: $ B $ 15, $ B2)> 1

Следующий снимок экрана демонстрирует результат:

Как вы понимаете, приведенный выше пример предназначен только для демонстрационных целей.При выделении повторяющихся строк на реальных листах вы, естественно, не ограничены сравнением значений только в 2 столбцах, функция СЧЁТЕСЛИМН может обрабатывать до 127 пар диапазон / критерий.

Выделение последовательных повторяющихся ячеек в Excel

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

• Чтобы выделить последовательные дубликаты без 1 ^st вхождений :
  = $ A1 = $ A2
• Для выделения последовательных дубликатов с 1 ^st вхождений :
  = ИЛИ ($ A1 = $ A2, $ A2 = $ A3)

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

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

• Чтобы выделить последовательные повторяющиеся ячейки без 1 ^st вхождений и игнорировать пустые ячейки :
  = AND ($ A2 <> "", $ A1 = $ A2)
• Для выделения последовательных повторяющихся ячеек с 1 ^st вхождений и игнорируют пустые ячейки :
  = AND ($ A2 <> "", OR ($ A1 = $ A2, $ A2 = $ A3))

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

Как выделить дубликаты в Excel с помощью Duplicate Remover

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

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

Средство для удаления дубликатов добавляет 3 новые функции к вашей ленте Excel:

• Dedupe Table — чтобы сразу найти и выделить дубликаты в одной таблице.
• Duplicate Remover — пошаговый мастер с расширенными параметрами для выявления и выделения дубликатов или уникальных значений в 1 таблице.
• Сравнить 2 таблицы — найти и выделить дубликаты путем сравнения двух столбцов или двух отдельных таблиц.

После установки Ultimate Suite for Excel вы найдете эти инструменты на вкладке Ablebits Data в группе Dedupe :

Выделение дубликатов в Excel за пару кликов

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

Вы не поверите, но желаемый результат можно получить всего за 2 клика мышки 🙂

1. Выделив любую ячейку в таблице, нажмите кнопку Dedupe Table , и умная надстройка выберет всю таблицу.
2. Откроется диалоговое окно «Таблица выведения » со всеми столбцами, выбранными автоматически, а параметр «Цвет » дублирует параметр , выбранный по умолчанию.Итак, все, что вам нужно сделать, это нажать ОК 🙂 Готово!

Результат будет примерно таким:

Как вы видите на изображении выше, инструмент Dupe Table выделил повторяющиеся строки без первых экземпляров .

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

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

По сравнению с быстрым инструментом Dedupe Table , мастеру Duplicate Remover требуется несколько дополнительных щелчков мышью, но он компенсирует это рядом дополнительных опций. Позвольте показать вам это в действии:

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

   Убедитесь, что таблица выбрана правильно, и нажмите Далее .

2. Выберите один из следующих типов данных, которые вы хотите найти:
   • Дубликаты, кроме первых случаев
   • Дубликаты с 1-м вхождением
   • Уникальные значения
   • Уникальные значения и первые повторяющиеся вхождения

   Для этого примера давайте найдем дубликатов + 1-е вхождение :
   

3. Теперь выберите столбцы для проверки дубликатов.Поскольку мы хотим выделить полные повторяющиеся строки, я выбрал все 3 столбца.
   

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

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

Поскольку сегодня мы изучаем различные способы выделения дубликатов в Excel, наш выбор очевиден 🙂 Итак, выберите Заливка цветом и выберите один из стандартных цветов темы, или нажмите Дополнительные цвета… и выберите любой настраиваемый цвет RGB или HSL.

Нажимаем кнопку Finish и наслаждаемся результатом 🙂

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

Если вы довольны производительностью Duplicate Remover, мы рады предоставить вам эту особую возможность:

Вас также может заинтересовать