Автоматическое заполнение ячеек в excel по условию

Автоматическое заполнение ячеек листа данными

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

Другие видеоуроки от Майка Гирвина в YouTube на канале excelisfun

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

Например, если требуется задать последовательность 1, 2, 3, 4, 5..., введите в первые две ячейки значения 1 и 2. Если необходима последовательность 2, 4, 6, 8..., введите 2 и 4.

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

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

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

Умное автозаполнение вниз и вправо

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

Представьте: огромный как футбольное поле опенспейс-офис российского представительства международной FMCG-компании. Шикарный дизайн, дорогая оргтехника, дресс-код, экспаты курлыкают по углам - вот это вот все :) В одной из переговорок начинаю двухдневный тренинг продвинутого уровня по текущей тогда версии Excel 2003 для 15 ключевых сотрудников экономического департамента вместе с их руководителем. Знакомимся, расспрашиваю их о бизнес-задачах, проблемах, прошу показать несколько типовых рабочих файлов. Показывают километровой длины выгрузки из SAP, простыни отчетов, которые они по этому всему делают и т.д. Ну, дело знакомое - мысленно прикидываю темы и тайминг, подстраиваюсь под аудиторию. Краем глаза замечаю, как один из участников, демонстрируя кусочек своего отчета, терпеливо тянет ячейку с формулой вниз за черный крестик в правом нижнем углу на несколько тысяч строк, потом проскакивает с лету конец таблицы, тянет обратно и т.д. Не выдержав, прерываю его кёрлинг мышью по экрану и показываю двойной щелчок по черному крестику, объясняя про автозаполнение вниз до упора.

Вдруг понимаю, что в аудитории подозрительно тихо и все как-то странно на меня смотрят. Незаметно окидываю себя взглядом где могу - все ОК, руки-ноги на месте, ширинка застегнута. Мысленно отматываю назад свои последние слова в поисках какой-нибудь жуткой оговорки - не было ничего криминального, вроде бы. После этого главный в группе молча встает, жмет мне руку и с каменным лицом говорит: "Спасибо, Николай. На этом тренинг можно закончить."

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

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

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

Копирование не всегда происходит до конца таблицы. Если таблица не монолитная, т.е. в соседних столбцах есть пустые ячейки, то не факт, что автозаполнение сработает до конца таблицы. Скорее всего процесс остановится на ближайшей пустой ячейке, не дойдя до конца. Если ниже по столбцу есть занятые чем-то ячейки, то автозаполнение остановится на них совершенно точно.
При копировании портится дизайн ячеек, т.к. по-умолчанию копируется не только формула, но еще и формат. Для исправления надо щелкать по кнопке параметров копирования и выбирать Только значения (Fill without format).
Не существует быстрого способа также удобно протянуть формулу не вниз, а вправо, кроме как тянуть вручную. Двойной щелчок по черному крестику - это только вниз.

Давайте попробуем исправить эти недостатки с помощью простого макроса.

Нажмите сочетание клавиш левый Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer). Вставьте новый пустой модуль через меню Insert - Module и скопируйте туда текст этих макросов:

Sub SmartFillDown() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(0, -1).CurrentRegion If rng.Cells.Count > 1 Then n = rng.Cells(1).Row + rng.Rows.Count - ActiveCell.Row ActiveCell.AutoFill Destination:=ActiveCell.Resize(n, 1), Type:=xlFillValues End If End Sub  Sub SmartFillRight() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(-1, 0).CurrentRegion If rng.Cells.Count > 1 Then n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Type:=xlFillValues End If End Sub

Такие макросы:

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

Для пущего удобства можно назначить этим макросам сочетания клавиш, используя кнопку Макросы - Параметры (Macros - Options) там же на вкладке Разработчик (Developer). Теперь достаточно будет ввести нужную формулу или значение в первую ячейку столбца и нажать заданное сочетание клавиш, чтобы макрос автоматически заполнил весь столбец (или строку):

Красота.

P.S. Частично проблема с копированием формул до конца таблицы была решена в Excel 2007 с появлением "умных таблиц". Правда, не всегда и не везде они уместны. И вправо Excel самостоятельно копировать так и не научился.

Ссылки по теме

Как в Excel автоматически заполнить диапазон набором значений — Трюки и приемы в Microsoft Excel

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

Например, на рис. 35.1 показан ряд последовательных чисел в столбце А. Ячейка А1 содержит значение 1, а ячейка А2 содержит формулу, которая была скопирована вниз по столбцу: =А1+1

Рис. 35.1. Excel предлагает простой способ сгенерировать ряд значений

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

Введите 1 в ячейку А1.
Введите 2 в ячейку А2.
Выберите А1:А2.
Переместите указатель мыши в правый нижний угол ячейки А2 (так называемый маркер заполнения ячейки) и, когда указатель мыши превратится в черный знак «плюс», перетащите его вниз по столбцу, чтобы заполнить ячейки.

Вы можете включать и отключать это поведение. Если у ячеек нет маркера заполнения, выберите Файл ► Параметры, перейдите в раздел Дополнительно диалогового окна Параметры Excel. Здесь в области Параметры правки установите флажок Разрешить маркеры заполнения и перетаскивание ячеек.

Данные, введенные в шагах 1 и 2, обеспечивают Excel необходимой информацией для определения типа серии, которую надо использовать. Если бы вы ввели 3 в ячейку А2, то серия бы состояла из нечетных чисел: 1,3, 5, 7 и т. д.

Вот еще один трюк автозаполнения: если данные, с которых вы начинаете, являются беспорядочными, Excel завершает автозаполнение, выполняя линейную регрессию и заполняя диапазон спрогнозированными значениями. На рис. 35.2 приведен лист с ежемесячными значениями продаж за январь-июль. При использовании автозаполнения после выбора С2:С8 Excel продлевает наиболее вероятную линейную тенденцию продаж и заполняет недостающие значения. На рис. 35.3 показаны спрогнозированные значения, а также график.

Рис. 35.2. Значения продаж за январь-июль, которые будут использованы для автозаполнения дальнейших цифр

Рис. 35.3. Цифры продаж последних пяти месяцев после использования автозаполнения для прогнозирования

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

Таблица 35.1. Типы данных с возможностью автозаполнения

Первое значение	Автоматически сгенерированные значения
Воскресенье	Понедельник, вторник, среда и т. д.
Квартал-1	Квартал-2, Квартал-3, Квартал-4, Квартал-1 и т. д.
Янв	Фев, Map, Апр и т. д.
Январь	Февраль, Март, Апрель и т. д.
Месяц 1	Месяц 2, Месяц 3, Месяц 4 и т. д.

Вы также можете создавать собственные списки элементов для автоматического заполнения. Для этого откройте диалоговое окно Параметры Excel и перейдите в раздел Дополнительно. Затем прокрутите окно вниз и нажмите кнопку Изменить списки для отображения диалогового окна Списки. Введите ваши элементы в поле Элементы списка (каждый на новой строке). Затем нажмите кнопку Добавить, чтобы создать список. На рис. 35.4 показан пользовательский список названий регионов, которые используют римские цифры.

Рис. 35.4. Эти имена регионов работают с функцией автоматического заполнения Excel

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

Рис. 35.5. Контекстное меню для автозаполнения

Как Связать Ваши Данные из Разных Книг в Excel

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

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

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

Как Можно Быстро Организовать Ссылку Между Данными в Книгах Excel (Смотри и Учись)

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

Давайте посмотрим иллюстрированное руководство по связыванию Листов и Книг в Excel.

Основы: Как Добавить Ссылку между Листами в Excel

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

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

Microsoft Excel

Урок за 60 Секунд: Как Вставлять, Удалять и Скрывать Листы в Excel

Andrew Childress

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

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

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

1. Создайте в Excel новую Формулу

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

Microsoft Excel

Как Работать с Математическими Формулами в Excel (Основы)

Andrew Childress

Я открываю запись знаком =, и затем щелкаю по первой ячейке на текущем листе, чтобы создать первую часть формулы. Затем, я напечатаю знак +, чтобы добавить значение из второй ячейки в этой формуле.

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

Пока не завершайте ввод формулы и не жмите Enter! Вам нужно оставить формулу открытой перед тем как вы переключитесь на другой Лист.

2. Переключитесь между Листами в Excel

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

Перейдите на другой Лист Excel

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

Заметьте на картинке ниже, что для ссылки на ячейку на другом Листе, Excel добавил надпись "Sheet2!B3", которая ссылается на ячейку В3 на листе с названием Sheet2. Вы можете написать это вручную, но когда в щелкаете по ячейке, Excel делает эту надпись автоматически за вас.

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

3. Закройте Excel формулу

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

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

Повышаем Уровень Сложности: Как Создать Связь Между Несколькими Книгами Excel

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

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

1. Откройте Две Рабочие Книги

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

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

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

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

2. Начните Создавать Формулу в Excel

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

Давайте рассмотрим число баррелей нефти которую я покупаю каждый месяц и цену за баррель. Сначала в ячейке из колонки Cost (ячейка C3), я начинаю создавать формулу, щелкнув в ней и нажав знак равно (=), а затем щелкаю по ячейке В3, чтобы взять значение для количества. Теперь, я жму знак *, чтобы уможножить количество на курс.

Итак, ваша формула, пока должна выглядеть так:

=B3*

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

3. Переключитесь на Другой Рабочий Документ Excel

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

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

Excel автоматически добавит ссылку на другой рабочий документа, как часть формулы:

=B3*[Prices.xlsx]Sheet1!$B$2

После того как вы нажмете Enter, Excel автоматически рассчитает окончательную стоимость умножив количество в первой книге на цену из второй книги.

Потренируйте свои навыки работы в Excel, умножая количество товара или объем на соответствующую ему цену из документа "Prices".

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

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

Как Обновлять Данные При Работе с Несколькими Книгами

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

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

Ответ такой, "зависит", и в основном зависит от того, открыты ли два документа одновременно.

Пример 1: Открыты Обе Книги Excel

Давайте проверим пример используя те же книги, что мы использовали раньше. Обе книги открыты. Давайте посмотрим, что произойдет, если мы изменим цену нефти за баррель с 45$ на 75$:

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

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

Пример 2: Если Один Рабочий Документ Закрыт

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

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

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

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

Подводим итоги и продолжаем обучаться работе в Excell

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

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

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

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

Как ни странно, очень многие люди, использующие в работе электронные таблицы Calc, не знают о такой вещи, как условное форматирование. Что такое условное форматирование? Это автоматическое форматирование ячейки в зависимости от данных в ней и условия, заданного пользователем. Например, в зависимости от данных в ячейке может меняться её цвет или, например, такие свойства шрифта, как размер, гарнитура, эффекты или цвет, а также это всё вместе в различных вариациях. Или в ячейке могут быть показаны дополнительные значки или гистограммы (но об этом ниже).

Для чего это нужно? Это нужно для визуального выделения нужных данных среди всех остальных, либо для облегчения визуального восприятия информации. Причем пользователю не нужно всматриваться, пытаться анализировать данные, а затем вручную что-то выделять или помечать. Программа всё сделает за Вас.

Настройки условного форматирования расположены в подменю Формат-Условное форматирование.

Доступны следующие варианты: Условие, Цветовая шкала, Гистограмма, Набор пиктограмм, Дата. Рассмотрим каждый из них подробнее:

Условие.

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

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

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

На иллюстрации ниже привёден пример результата работы условного форматирования с тремя условиями для каждой ячейки: диапазон от 1 до 10 - красный, от 10 до 50 - жёлтый, свыше 50 - зелёный. Пример абсолютно абстрактный конечно же.

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

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

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

Цветовая шкала.

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

Доступно два типа цветовой шкалы: 3 значения и 2 значения. На иллюстрации ниже показаны примеры для трёх (красный, жёлтый, зелёный) и для двух (синий, красный) цветов.

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

Гистограмма.

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

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

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

На иллюстрации показаны два варианта гистограмм для одних и тех же числовых значений. Слева установлено автоматическое определение минимума и максимума, типа заливки градиент, длина полоски 100%. Справа - минимум и макисмум заданы вручную (но соответствуют значениям в ячейках), тип заливки - сплошной цвет, максимальная длина полоски ограничена 50%, активна опция "Показать только полоску" - числовые значения в ячейках не показываются, однако никуда не исчезли, на них можно ссылаться и использовать в формулах.

Набор пиктограмм (значков).

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

Такой тип условного форматирования работает также для некоего диапазона ячеек. В LibreOffice Calc есть достаточно большое разнообразие типов из 3, 4 и 5 значков:

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

ИМХО, 3 значка - это когда нужно показать что "всё плохо", "средне", "хорошо"; 4 значка - это когда "всё пропало и погибло", "всё плохо, но не смертельно", "не плохо", "всё хорошо"; 5 значков - "результат ноль", "есть что-то", "уже половину написал", "ещё чуть чуть осталось", "выполнили план!".

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

Дата.

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

Автоматически заполнять ячейки Excel

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

Рис.1. Заполните от 1 до 100.

Excel анализирует заполненные ячейки, распознает формулу и вставляет значения по этой формуле, другой пример четные числа, в ячейку A1 вводим цифру 2 а в ячейку A2 цифру 4 , см. рис. 2.

Рис. 2. Заполняйте только четные числа.

Excel хорошо обрабатывает названия дней недели, месяцев, дат, см. рисунок 3.

Рис. 3. Заполните дни недели.

Имена, которые Excel распознает и использует формулу для заполнения следующих ячеек, примеры:

Пн (Вт, Ср, Чт и т.д.)
1 января (1 февраля, 1 марта, 1 апреля и т. д.)
2010 (2011, 2012 и др.)
8:00 (9:00, 10:00 и т. д.)
Продукт 1 (Продукт 2, Продукт 3 и т. д.)

Кроме того, Excel позволяет создавать новые списки автозаполнения с помощью диалогового окна «Файл»> «Параметры»> «Дополнительно»> «Общие»> «Редактировать пользовательские списки» .Смотрите рисунок 4, я создал примерный список с цветами, список, конечно же, расширяемый, теперь просто введите любой цвет из списка в ячейку и используйте дескриптор ячейки, чтобы заполнить оставшиеся ячейки значениями.

Рис. 4. Новый список автозаполнения.

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

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

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

Вы можете создавать, редактировать, удалять и просматривать все правила условного форматирования в книге с помощью диалогового окна Диспетчер правил условного форматирования . (На вкладке Главная щелкните Условное форматирование , а затем щелкните Управление правилами ).

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

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

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

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

Первое правило (которое, если установлено значение «Истина», устанавливает красный цвет фона ячейки) сравнивает значение даты в столбце B с текущей датой (полученной с помощью функции СЕГОДНЯ в формуле). Формула должна быть назначена первому значению данных в столбце B, то есть ячейке B2. Формула этого правила: = B2 <СЕГОДНЯ () .Он используется для проверки ячеек в столбце B (ячейки B2: B15). Если формула верна для любой из ячеек в столбце B, соответствующая ячейка в столбце A (например, A5 — это B5, а A11 — это B11) форматируется с красным цветом фона. После того, как все ячейки, указанные в Применимо к , проверены с помощью первого правила, проверяется второе правило. Эта формула проверяет, что значения в столбце B меньше 60 дней от текущей даты (например, предположим, что текущая дата — 11.08.2010).Значение в ячейке B4, 04.10.2010, меньше 60 дней с сегодняшней даты, поэтому оценка оценивается как True, а ячейка форматируется с желтым цветом фона. Формула этого правила: = B2<СЕГОДНЯ() + 60 . Любые ячейки, сначала отформатированные с помощью самого верхнего правила в списке (красного цвета), остаются без изменений.

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

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

Иногда для нескольких правил условного форматирования установлено значение «Истина».Вот как применяются правила, когда они не конфликтуют и когда они конфликтуют с одним:

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

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

Как вставка, заливка и Format Painter влияют на правила условного форматирования

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

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

Что делать, если условное и ручное форматирование вызывают конфликт?

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

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

Управляйте проверкой правила с помощью флажка Остановить, когда условие истинно

Для совместимости с версиями Excel, предшествующими Excel 2007, можно установить флажок Остановить, когда условие истинно в диалоговом окне Управление правилами .Это позволяет увидеть, как может выглядеть условное форматирование в более ранних версиях Excel, которые не поддерживали более трех правил условного форматирования или несколько правил, применяемых к одному диапазону.

Если для диапазона ячеек указано более трех правил условного форматирования, версии Excel до Excel 2007:

90 112

проверит только первые три правила,

будет использовать первое правило, дающее значение «Истина»,

игнорирует правила со следующим приоритетом, если они имеют значение «Истина».

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

90 130 90 130

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

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

90 112

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

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

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

Использование структурированных ссылок в таблицах Excel

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

Вместо использования явных ссылок на ячейки	Excel использует имена таблиц и столбцов
= СУММ (С2:С7)	= СУММ (CDM [сумма продаж])

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

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

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

Дилер	Регион	Сумма продаж	% комиссия
Кристофер	Север	260	10%
Томас	Юг	660	15%
Каролина	Восток	940	15%
Эрик	Запад	410	12%
Данута	Н	800	15%
Аня	Юг	900	15%

Скопируйте пример данных из приведенной выше таблицы (включая заголовки столбцов) и вставьте их в ячейку A1 нового листа Excel.
Чтобы создать таблицу, выберите любую ячейку в диапазоне данных и нажмите Ctrl + T.
Убедитесь, что в моей таблице есть заголовки, выбран , а затем нажмите OK.
В ячейке E2 введите знак равенства ( = ) и щелкните ячейку C2.

В строке формул после знака равенства появляется структурированная ссылка [@ [Сумма продаж]] .
Сразу после закрытия введите звездочку (*) и щелкните ячейку D2.

В строке формул после звездочки появляется структурированная ссылка [@ [% комиссии]] .
Нажмите клавишу Введите .

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

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

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

В образце рабочего листа щелкните E2
В строке формул введите формула = C2 * D2 и нажмите Enter.

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

Как переименовать таблицу?

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

Выберите любую ячейку в таблице, чтобы отобразить вкладку > table на вкладке Design на ленте.
Введите имя в поле Имя таблицы и нажмите клавишу Enter .

В нашем образце данных мы использовали имя SprzWDz .

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

Используйте допустимые символы Имя всегда должно начинаться с буквы, символа подчеркивания (_) или обратной косой черты (\). Другие символы, используемые в имени, могут быть буквами, цифрами, точками или символами подчеркивания. Буквы «K», «k», «W» или «w» нельзя использовать в качестве имени, поскольку они используются в качестве ярлыков для выбора столбца или строки активной ячейки при вводе в поле имени или . , Перейти к .

Не использовать ссылки на ячейки Имена не могут совпадать со ссылками на ячейки, например Z $ 100 или R1C1.
Не разделять слова пробелами В имени нельзя использовать пробелы. В качестве разделителей слов можно использовать символ подчеркивания (_) и точку (. ). Например, CFD, Sales_Tax или First.Quarter.
До 255 символов Имя таблицы может содержать до 255 символов.
Используйте уникальные имена таблиц. Повторяющиеся имена не допускаются. Excel не различает прописные и строчные буквы, поэтому, если вы введете «Продажи» и «ПРОДАЖИ» уже существуют в одной и той же книге, вам будет предложено ввести уникальное имя.
Использование идентификатора объекта Если вы планируете иметь смешанные таблицы, сводные таблицы и диаграммы, вы можете добавить префиксы к именам с типом объекта.Например: tbl_Sales для таблицы продаж, pt_Sales для сводной таблицы продаж, chrt_Sales для диаграммы продаж или ptchrt_Sales для таблицы сводной диаграммы. Благодаря этому все ваши имена будут организованы в упорядоченный список в диспетчере имен.

Правила синтаксиса структурированных ссылок

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

= СУММА (Купон [[# Сумма], [Сумма продажи]], Купон [[# Данные], [Сумма комиссии]])

Эта формула содержит следующие структурированные ссылочные компоненты:

Имя таблицы: DeptSales — это пользовательское имя таблицы. Он относится к данным в таблице без каких-либо заголовков или итоговых строк.Вы можете использовать имя таблицы по умолчанию, например Table1, или изменить его, чтобы использовать собственное имя.
Спецификатор столбца: [Объем продаж] i [Сумма Комиссия] — это спецификаторы столбцов, которые используют имена столбцов, которые они представляют. Они относятся к данным столбца, без заголовков или итоговых строк. Спецификаторы всегда должны быть заключены в квадратные скобки, как показано на рисунке.
Спецификатор элемента: [#Totals] и [#Data] — это специальные спецификаторы элементов, которые относятся к определенным частям таблицы, например к итоговой строке.
Спецификатор таблицы: [[#Сумы];[Сумма продаж]] и [[#Данные];[Сумма комиссии]] — это спецификаторы таблицы, представляющие собой внешние части структурированной ссылки.Внешние части ссылок следуют за именем таблицы и должны быть заключены в квадратные скобки.
Структурированная ссылка : (SprzWD [[# Всего]; [Сумма продажи]] и SprzWD [[# Дата]; [Сумма комиссии]] — это структурированные ссылки, представленные строкой, которая начинается с имени таблицы и заканчивается спецификатором столбца.

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

Заключите спецификаторы в квадратные скобки Все спецификаторы для таблиц, столбцов и специальных элементов должны быть заключены в квадратные скобки ([]).Спецификатор, который содержит другие спецификаторы, требует внешних скобок, заключающих внутренние скобки других спецификаторов. Например: = Отдел продаж [[Продавец]: [Регион]]
Все заголовки столбцов являются текстовыми строками Однако вам не нужно включать кавычки, когда они используются в структурированной ссылке. Числа или даты, такие как 2014 или 01.01.2014, также являются текстовыми строками.Вы не можете использовать выражения в заголовках столбцов. Например, выражение SprzWDzPodsYoung [[2014]: [2012]] не подойдет.

Поместите заголовки столбцов, содержащие специальные символы, в квадратные скобки Если есть специальные символы, весь заголовок столбца должен быть заключен в круглые скобки, что означает, что спецификатор столбца требует двойных скобок. Например: = Промежуточный счет [[Общая сумма в долларах США]]

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

Табулятор
Новая строка
Возврат каретки
Запятая (,)
Двоеточие (:)
Период (.)
Левая квадратная скобка ([)
Правая квадратная скобка (])
Знак номера (#)
Одинарная кавычка (')
Двойная кавычка ("")
Левая фигурная скобка ({)
Правая распорка (})
Знак доллара ($)
Каре (^)
Коммерческий "и" (&)
Звездочка (*)
Знак плюс (+)
Знак равенства (=)
Знак минус (-)
Символ большинства (>)
Символ меньшинства (<)
Знак деления (/)

Вот список специальных символов, для которых требуется экранирующий символ (') в формуле:

Левая квадратная скобка ([)
Правая квадратная скобка (])
Знак номера (#)
Одинарная кавычка (')

Используйте пробел, чтобы упростить чтение структурированной ссылки Вы можете использовать пробелы, чтобы упростить чтение структурированной ссылки.Например: = Cmd [[Продавец]: [Регион]] или = Cmdm [[# Заголовки]; [#Данные]; [% комиссии]]

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

после первой левой квадратной скобки ([),
перед последней правой квадратной скобкой (]),
после точки с запятой.

Справочные операторы

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

Структурированный номер:	Относится к:	Использование:	Соответствует диапазону ячеек:
= Купон [[Продавец]: [Регион]]	Все ячейки в двух или более соседних столбцах	: (двоеточие) оператор диапазона	А2: В7
= SprzWDz [Сумма продажи] SprzWDz [Сумма комиссии]	Сборка двух или более колонн	; Составной оператор (точка с запятой)	С2: С7; Е2: Е7
= SprzWDz [[Продавец]: [Объем продаж]] SprzWDz [[Регион]: [% комиссии]]	Общая часть двух или более столбцов	(пробел) оператор пересечения	В2: С7

Спецификаторы специальных позиций

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

Спецификатор специального предмета:	Относится к:
#Все	Вся таблица, включая заголовки столбцов, данные и итоги (если есть).
# Данные	Только строки данных.
# Заголовки	Только строка заголовка.
#Сумы	Только общая строка.Если итоговая строка не существует, возвращается null.
# Эта строка или @ или @ [Имя столбца]	Только ячейки в той же строке, что и формула. Эти спецификаторы нельзя комбинировать ни с какими другими специальными спецификаторами элементов.Используйте этот спецификатор, чтобы принудительно использовать промежуточное пересечение в эталонном поведении или переопределить поведение промежуточного пересечения и ссылаться на отдельные значения в столбце. Excel автоматически заменяет спецификатор строки #This на более короткий спецификатор @ в таблицах с более чем одной строкой данных. Однако если в таблице есть только одна строка, Excel не переопределяет спецификатор строки #This, что может привести к неожиданным результатам вычислений, если вы добавите больше строк.Чтобы избежать проблем с расчетами, введите в таблицу несколько строк, прежде чем вводить какие-либо структурированные справочные формулы.

Квалифицировать структурированные ссылки на вычисляемые столбцы

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

Тип структурированной ссылки	Пример	Комментарий
Не подходит	= [Сумма продажи] * [% комиссии]	Умножить соответствующие значения из текущей строки.
Полностью квалифицированный	= SprzWDz [Сумма продаж] * SprzWDz [% комиссии]	Умножить соответствующие значения для каждой строки и обоих столбцов.

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

Примеры использования структурированных ссылок

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

Структурированный номер:	Относится к:	Соответствует диапазону ячеек:
= Купон [[# Все];[Сумма продажи]]	Все ячейки в столбце Сумма продаж.	С1: С8
= Неверный [[# Заголовок]; [% комиссии]]	% Заголовок столбца комиссии.	Д1
= Invdt [[# Сумма]; [Регион]]	Сумма столбца "Регион".Если строки суммы нет, возвращается null.	В8
= SprzWDz [[# Все];[Сумма продажи]: [% комиссии]]	Все ячейки в столбцах «Сумма продаж» и «% комиссии».	С1: D8
= SprzWDz [[# Данные];[% комиссии]: [Сумма комиссии]]	Только данные в колонках %Комиссия и Сумма комиссии.	Д2: Е7
= Инвентарь [[# Заголовков];[Регион]: [Сумма комиссии]]	Только заголовки столбцов между «Регион» и «Сумма комиссии».	В1: Е1
= SprzWDz [[# Сумы];[Сумма продажи]: [Сумма комиссии]]	Итоги для столбцов «Сумма продаж» — «Сумма комиссии».Если строки суммы нет, возвращается null.	С8: Е8
= Неверный [[# заголовков]; [# данных]; [% комиссии]]	Только заголовок и данные для столбца %Комиссия.	Д1: Д7
= SprzWDz [[#Эта строка];[Сумма комиссии]] или = SprzWDz [@ Сумма комиссии]	Ячейка на пересечении текущей строки и столбца Сумма комиссии.При использовании в той же строке, что и заголовок или итоговая строка, будет возвращена ошибка #ЗНАЧ!. Если вы введете эту структурированную ссылку в более длинной форме (#Эта строка) в таблице с несколькими строками данных, Excel автоматически заменит ее более короткой формой (@). Они оба работают одинаково.	E5 (если текущая строка — строка 5)

Стратегии работы со структурированными ссылками

При работе со структурированными ссылками учитывайте следующее.

Использование автозаполнения формул Использование автозаполнения формул может быть очень полезным при вводе структурированных ссылок и для обеспечения правильного использования синтаксиса. Дополнительные сведения см. в разделе Использование автозаполнения формул.
Принятие решения о создании ссылок на структурированные таблицы По умолчанию при создании формулы щелчок по диапазону ячеек в таблице приводит к выбору ячеек наполовину и автоматически вводит структурированную ссылку вместо диапазона ячеек в формуле .Такое поведение выбора упрощает ввод структурированных ссылок. Вы можете включить или отключить это поведение, установив или сняв флажок Использовать имена таблиц в формулах в диалоговом окне > Параметры файла > Формулы > Работа с формулами.
Использование книг с внешними ссылками на Excel в других книгах Если книга имеет внешнюю ссылку на таблицу Excel в другой книге, связанная исходная книга должна быть открыта в Excel, чтобы избежать ошибок #ССЫЛКА! в целевой книге, содержащей ссылки.Если вы сначала откроете целевую книгу и отобразится #ССЫЛКА!, они будут разрешены, если вы затем откроете исходную книгу. Если вы сначала откроете исходную книгу, коды ошибок не должны быть кодами ошибок.
Преобразование диапазона в таблицу и таблицы в диапазон При преобразовании таблицы в диапазон все ссылки на ячейки преобразуются в эквивалентные им абсолютные ссылки A1.При преобразовании диапазона в таблицу любые ссылки на ячейки в этом диапазоне не заменяются автоматически эквивалентными им структурированными ссылками.
Отключение заголовков столбцов Заголовки столбцов таблицы можно включить или отключить на вкладке «Дизайн» в> Строка заголовка. Отключение заголовков столбцов таблицы не влияет на структурированные ссылки, в которых используются имена столбцов, и их по-прежнему можно использовать в формулах.Структурированные ссылки, непосредственно ссылающиеся на заголовки таблиц (например, = SprzWWS [[# Headers]; [% Commission]] ), приведут к #REF.
Добавление и удаление столбцов и строк в таблице Диапазоны данных таблицы часто изменяются, поэтому ссылки на ячейки для структурированных ссылок настраиваются автоматически. Например, если в формуле используется имя таблицы для подсчета всех ячеек данных в таблице, и вы добавляете строку данных, ссылка на ячейку будет автоматически совпадать.
Переименование таблицы или столбца При переименовании таблицы или столбца все структурированные ссылки в книге, использующие это имя, автоматически изменяются.
Перемещение, копирование и заполнение структурированных ссылок При копировании или перемещении формулы, использующей структурированную ссылку, все структурированные ссылки остаются без изменений.

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

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

Нужна дополнительная помощь?

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

Связанные темы

Обзор таблиц данных Excel
Видеоклип: Создание и форматирование таблицы Excel
Сумма данных в таблице Excel
Форматирование таблицы Excel таблицы
Изменение размера таблицы путем добавления или удаления строк и столбцов
Фильтрация данных в диапазоне или таблице
Преобразование таблицы в диапазон
Проблемы совместимости таблиц Excel
Экспорт Excel в файл SharePoint
Обзор формул в Excel

Условное форматирование, т.е. цветные ячейки

Чтение минут: 6

Обновлено: 14 января 2021 г.

Сегодня я расскажу вам, как выразить свое художественное чутье в электронной таблице. В ленте ОСНОВНЫЕ ИНСТРУМЕНТЫ в Excel есть кнопка УСЛОВНОЕ ФОРМАТИРОВАНИЕ . Я знаю, это звучит сложно, но он отлично справляется с тем, для чего его можно использовать.

Форматирование, т. е. изменение «формата» ячейки, например, цвета фона, шрифта, добавление значка.
Условный, т.е. основанный на определенном правиле/условии, относящемся к содержимому форматируемой ячейки.

Зачем нужно условное форматирование?

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

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

Если ответить на вопрос "зачем?" прежде чем нажать на кнопку УСЛОВНОЕ ФОРМАТИРОВАНИЕ , нам будет намного проще выбрать конкретный вариант форматирования.

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

Мы проверим возможности Excel, ответив на вопросы: что и как мы хотим отформатировать? Начиная с вопроса "что?" - мы можем выбрать из таких вариантов, как:

Больше / меньше / Между / Равно — Форматирует ячейки на основе их значений. Используется, когда мы хотим выделить, например, все ячейки, значение которых больше/меньше нуля (или другого значения).
Текст, содержащий — форматирует ячейки на основе букв, присутствующих в тексте (без учета регистра).Используйте, когда вы хотите выделить все ячейки, содержащие определенную фразу в тексте — независимо от того, появляется ли она в начале или в конце слово.
Дата, которая появляется в - Форматирует ячейки на основе даты. Здесь у нас есть варианты: вчера, сегодня, завтра, следующий/этот/прошлый месяц, следующий/эта/прошлая неделя, последние 7 дней.
Дублирование значений - Форматирует ячейки с повторяющимися значениями. Имея список данных, мы можем поймать, например, повторяющиеся номера счетов.
Первые 10/Последние элементы - Выделяет ячейки на основе их значений.Выбрав самое большое/наименьшее — мы можем указать, сколько первых/последних значений нужно выделить. Например, выберите 5 лучших продавцов по количеству подписанных контрактов.
Первые/последние 10% - выделяет 10% самых высоких/низших результатов, рассчитанных на основе значений из выбранного диапазона.
Выше/ниже среднего - выделяет значения выше/ниже среднего, рассчитанные из выбранного диапазона значений.

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

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

Пример будет очень статистическим - мы будем использовать таблицу, в которой представлено количество людей, зарегистрированных для постоянного проживания в отдельных районах Торуня. Используя условное форматирование, мы выберем первые десять районов с наибольшим количеством зарегистрированных людей. На первом шаге выберите всю таблицу и нажмите: УСЛОВНОЕ ФОРМАТИРОВАНИЕ > ПЕРВЫЕ 10 ЭЛЕМЕНТОВ.

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

Изменим правило так, чтобы выделялись все районы, в которых зарегистрировано более 10 000 жителей. Мы не будем создавать новое правило, а изменим существующее. Вводим УСЛОВНОЕ ФОРМАТИРОВАНИЕ>УПРАВЛЕНИЕ ПРАВИЛАМИ .

Если вы не видите своего правила в отображаемом окне (и вы его ранее создали), то в верхней панели ПОКАЗАТЬ ПРАВИЛА ФОРМАТ ДЛЯ выберите опцию ЭТОТ ЛИСТ .Затем выбираем наше правило и нажимаем на кнопку EDIT RULE.

В окне редактирования правила измените параметры с ФОРМАТИРОВАТЬ ТОЛЬКО ОПЦИИ, КЛАССИФИЦИРОВАННЫЕ КАК ПЕРВАЯ И ПОСЛЕДНЯЯ на ФОРМАТИРОВАТЬ ТОЛЬКО ЯЧЕЙКИ. Далее мы определяем, какому условию должны соответствовать отформатированные значения (более 10 000):

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

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

Мы уже ответили на вопрос "что?" мы хотим отформатировать (количество зарегистрированных жителей в отдельных округах). Настало время ответить на вопрос «как?» Во-первых, давайте посмотрим, какие у нас есть варианты.

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

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

Для вышеуказанных настроек в двухцветной шкале наша таблица выглядит так:

Цветные полосы - полоса, показывающая уровень заданного значения, появится в каждой ячейке диапазона в зависимости от установленного минимума (без полосы) - максимума (полоса во всей ячейке).В этом варианте форматирования мы можем «скрыть» значения, а получатели данных оставят только панель (установив флажок ПОКАЗАТЬ БАР ТОЛЬКО ). Скрытие данных полезно, если мы хотим акцентировать внимание получателя не на отдельных значениях, а на их интенсивности по сравнению с другими данными.

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

Наборы значков - рядом с данными будут появляться значки в зависимости от значения ячейки (установленные интервалы).Например, я предположил, что Excel должен помечать красным крестом районы с зарегистрированными жителями менее 50 человек, а в диапазоне от 50 до 1000 — желтым восклицательным знаком. Значения выше 1000 — зеленой галочкой.

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

Облегчить прием данных

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

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

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

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

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

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

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

Что, если мы получим рабочий лист, который уже содержит правила форматирования, которые мешают нам получать данные? Вы можете удалить правила из выбранного диапазона, всего листа, таблицы или сводной таблицы.Способ - использовать путь: УСЛОВНОЕ ФОРМАТИРОВАНИЕ > ОЧИСТИТЬ ПРАВИЛА и выбрать интересующий нас вариант:

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

Агнешка Кмеч

Возникли проблемы с Excel? Получите справку по группе Сохраняйте спокойствие и используйте Excel .

Blender уроки

Категории