0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Автозаполнение ячеек в Excel

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

Автозаполнение дней недели в Excel

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

Автозаполнение последовательности чисел в Excel

Excel также может распознавать числа в тексте. Поэтому легко составить список кварталов. В ячейке введите «1 квартал» и перетащите вниз.

Автозаполнение кварталов в Excel

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

Автозаполнение в Excel из списка данных

Понятно, что помимо дней недели и месяцев могут понадобиться и другие списки. Например, иногда необходимо ввести список городов, в которых у компании есть сервисные центры: Минск, Гомель, Брест, Гродно, Витебск, Могилев, Москва, Санкт-Петербург, Воронеж, Ростов-на-Дону, Смоленск, Белгород. Первое, что вам нужно сделать, это создать полный список имен и сохранить его. Перейдите в меню Файл — Параметры — Дополнительно — Общие — Изменить списки.

Изменить списки для автозаполнения в Excel

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

Диалоговое окно для изменения списков в Excel

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

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

Нажмите OK. Список создан, вы можете использовать его для автозаполнения.

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

Команда Прогрессия в Excel

Настройки диалогового окна Прогрессия

С помощью переключателя в левой части окна можно настроить направление последовательности: вниз (строки) или вправо (столбцы).

В центре вы выбираете нужный тип:

  • Арифметическая прогрессия — каждое последующее значение умножается на число, указанное в поле Шаг
  • Геометрическая прогрессия — каждое последующее значение умножается на число, указанное в поле Шаг
  • Даты — создает последовательность дат. Выбор этого типа активирует переключатели справа, где можно выбрать тип устройства. Существует 4 варианта:
      • День — список календарных дат (шагами ниже)
      • Рабочий день — порядок рабочих дней (выходные пропускаются)
      • Месяц — изменяются только месяцы (число фиксируется как в первой ячейке)
      • Год — изменяются только годы
      • Автозаполнение — эта команда эквивалентна перемещению левой кнопки мыши. Другими словами, Excel сам определяет, продолжить ли последовательность чисел или расширить список. Если вы предварительно заполните две ячейки значениями 2 и 4, 6, 8 и т.д. появятся в других выделенных ячейках. Если вы предварительно заполните больше ячеек, Excel рассчитает аппроксимацию линейной регрессии, т.е. предсказание прямолинейного тренда (приятная особенность — подробнее см. ниже).

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

      Предельное значение в прогрессии

      Результатом будет заполненный столбец от 2 до 1000. Таким же образом можно создать количество рабочих дней на предстоящий год (значение отсечки должно быть последней датой, например, 31.12.2016). Опция заполнения столбца (или строки) последним значением очень полезна, поскольку избавляет от лишней работы при растягивании. На этом настройки автозаполнения заканчиваются. Давайте двигаться дальше.

      Автозаполнение чисел с помощью мыши

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

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

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

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

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

      Автозаполнение с помощью правой кнопки мыши

      Это добавляет несколько команд. Дополнительные операции автозаполнения доступны с помощью Progress (см. настройки выше). Это не повлияет на длину последовательности, так как диапазон будет выбран, и последняя ячейка будет пределом.

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

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

      Данные для с равномерным ростом

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

      Прогноз с помощью линейного тренда на диаграмме

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

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

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

      Прогноз по методу экспоненциального приближения

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

      Автозаполнение дат с помощью мыши

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

      Автозаполнение дат в Excel с помощью мыши

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

      Автозаполнение по месяцам

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

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

      Это практически все. В этом видеоуроке демонстрируется процесс автозаполнения ячеек в Excel.

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

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

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

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

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

      Готовый пример.

      Пользователям достаточно указать, делал ли клиент заказ в течение текущего месяца, введя текст «заказ» в соответствующую ячейку. Главное условие выделения: если контрагент не сделал ни одного заказа в течение 3 месяцев, его номер автоматически выделяется красным цветом.

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

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

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

      ДАТА ГОД СЕГОДНЯ.

      Можно ли сделать так, чтобы исходящие месяцы формировались автоматически?

      На рисунке формула возвращает период времени, начинающийся с даты написания статьи: 17.09.2017. Первый аргумент в функции DATA — это вложенная формула, которая всегда возвращает текущий год на сегодняшнюю дату благодаря функциям: ГОД и СЕКУНДА. В качестве второго аргумента укажите номер месяца (-1). Отрицательное число означает, что нас интересует, какой месяц был в прошлом времени. Пример условий для второго аргумента:

      • 1 означает первый месяц (январь) года, указанного в первом аргументе;
      • 0 означает 1 месяц назад;
      • -1 означает 2 месяца назад начала текущего года (т.е.: 01.10.2016).

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

      Затем перейдите в ячейку С1 и введите следующую формулу

      ДАТА ГОД МЕСЯЦ.

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

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

      Выберите диапазон ячеек B1:L1 и выберите HOME — Cells — Formats cells или нажмите CTRL+1. В появившемся диалоговом окне на вкладке «Numbers» в поле «Number formats:» выберите «(all formats)» в поле «Number formats:». В поле «Тип:» введите значение: МММ.ГГ (всегда прописными буквами). Это приведет к сокращенному отображению значений данных в заголовках случаев, что упростит визуальный анализ и сделает его более удобным за счет лучшей читабельности.

      Пользовательский Формат ячеек.

      Обратите внимание! Как только наступает январь (D1), формула начинает отсчет со следующего года.

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

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

      1. Выделите диапазон ячеек B2:L15 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». А в появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулу для определения форматируемых ячеек» Создать правило.
      2. В поле ввода введите формулу: Формула столбца.
      3. Щелкните на кнопку «Формат» и укажите на вкладке «Заливка» каким цветом будут выделены ячейки актуального месяца. Например – зеленый. После чего на всех окнах для подтверждения нажмите на кнопку «ОК».

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

      Столбец текущего месяца.

      Какова формула для выбора цвета колонки на основе условий?

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

      Автоматическое выделение цветом столбца по условию текущего месяца

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

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

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

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

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

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

      1. Выделите диапазон ячеек A2:A15 (то есть список номеров клиентов) и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». А в появившемся окне «Создание правила форматирования» выберите опцию: «Использовать формулу для определения форматируемых ячеек»
      2. В этот раз в поле ввода введите формулу: СЧЁТЕСЛИ.
      3. Щелкните на кнопку «Формат» и укажите красный цвет на вкладке «Заливка». После чего на всех окнах нажмите «ОК».
      4. Заполоните ячейки текстовым значением «заказ» как на рисунке и посмотрите на результат:

      Номера клиентов выделены красным цветом, если в их строке нет значения ‘order’ в последних трех ячейках текущего месяца (включая).

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

      Давайте начнем с нашей средней части формулы. Как следует из названия, СМЕЩ возвращает ссылку на диапазон, смещенный относительно базового диапазона, заданного количеством строк и столбцов. Это может быть одна ячейка или целый диапазон ячеек. Более того, вы можете настроить количество возвращаемых строк и столбцов. В нашем примере функция возвращает диапазон ячеек за последние 3 месяца.

      В нашем условии выбора цвета важен первый аргумент HUM. По нему определяется месяц, с которого начинается смена. Здесь это ячейка D2. начало года — январь. Аналогично, номер строки базовой ячейки будет соответствовать номеру строки остальных ячеек в столбце. Эти два аргумента обеспечивают выполнение сдвига на нужное количество строк и столбцов. Поскольку вычисления для каждого клиента будут выполняться в одной и той же строке, значение смещения для строк мы указываем -¬ 0.

      В то же время для вычисления смещения строки третьего аргумента мы используем вложенную формулу MONTH(TODAY()), которая в соответствии с условиями возвращает номер текущего месяца в текущем году. Из вычисленного формулой номера месяца вычитаем число 4, то есть в случае ноября получаем смещение на 8 столбцов. Разница для июня, например, составляет всего 2 столбца.

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

      Первая функция в формуле ДЕЙСТВИТЕЛЬНО проверяет условие: сколько раз текстовое значение «order» встречается в диапазоне, возвращаемом функцией SMESH. Если функция возвращает 0, это означает, что в течение 3 месяцев не было ни одного заказа от клиента с таким номером. И в соответствии с нашими условиями, ячейка с номером этого клиента выделена красным цветом.

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

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

      голоса
      Рейтинг статьи
      Читайте так же:
      Как автоматически заполнить дату или только последовательность дней недели в Excel?
Ссылка на основную публикацию
Adblock
detector