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

Рубрика: 7. Полезняшки Excel

Если вам требуется автоматически заполнить диапазон ячеек серией значений, то можно внести первое значение и записать формулу для расчета следующего значения (рис. 1). [1] В ячейке А1 содержится значение 1, а в ячейке А2 формула, скопированная вниз по столбцу: =А1+1.

%d1%80%d0%b8%d1%81-1-%d1%81%d0%b5%d1%80%d0%b8%d1%8f-%d0%bd%d0%be%d0%bc%d0%b5%d1%80%d0%be%d0%b2-%d0%bd%d0%b0-%d0%be%d1%81%d0%bd%d0%be%d0%b2%d0%b5-%d1%84%d0%be%d1%80%d0%bc%d1%83%d0%bb%d1%8b

Рис. 1. Серия номеров на основе формулы

Скачать заметку в формате Word или pdf, примеры в формате Excel

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

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

Такое поведение можно включать и отключать. Если в ячейке нет маркера, выполните команду Файл –> Параметры, перейдите в раздел Дополнительно в диалоговом окне Параметры Excel и установите флажок Разрешить маркеры заполнения и перетаскивание ячеек (рис. 2).

%d1%80%d0%b8%d1%81-2-%d0%bd%d0%b0%d1%81%d1%82%d1%80%d0%be%d0%b9%d0%ba%d0%b0-%d0%b0%d0%b2%d1%82%d0%be%d0%b7%d0%b0%d0%bf%d0%be%d0%bb%d0%bd%d0%b5%d0%bd%d0%b8%d1%8f

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

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

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

%d1%80%d0%b8%d1%81-3-%d0%b8%d1%81%d0%bf%d0%be%d0%bb%d1%8c%d0%b7%d0%be%d0%b2%d0%b0%d0%bd%d0%b8%d0%b5-%d1%80%d0%b0%d1%81%d0%ba%d1%80%d1%8b%d0%b2%d0%b0%d1%8e%d1%89%d0%b5%d0%b3%d0%be%d1%81%d1%8f-%d1%81

Рис. 3. Использование раскрывающегося списка Параметры автозаполнения для изменения типа заполнения

Вот еще один интересный прием работы с автозаполнением. Если вы начинаете работать с нерегулярными данными, то Excel делает автозаполнение методом линейной регрессии и вносит в таблицу прогнозируемые таким образом значения (рис. 4). Если выделить диапазон С2:С10, а потом протянуть его до ячейки С13, то программа выстроит наиболее вероятную линейную тенденцию продаж и дополнит эту модель недостающими значениями (рис. 5). Правда программа не учтет традиционный сезонный рост в четвертом квартале (как учитывать этот рост см. Прогнозирование на основе экспоненциального сглаживания).

%d1%80%d0%b8%d1%81-4-%d0%b8%d1%81%d0%bf%d0%be%d0%bb%d1%8c%d0%b7%d0%be%d0%b2%d0%b0%d0%bd%d0%b8%d0%b5-%d0%b0%d0%b2%d1%82%d0%be%d0%b7%d0%b0%d0%bf%d0%be%d0%bb%d0%bd%d0%b5%d0%bd%d0%b8%d1%8f-%d0%b4%d0%bb

Рис. 4. Использование автозаполнения для прогнозирования объема продаж методом линейной регрессии

%d1%80%d0%b8%d1%81-5-%d0%b4%d0%b0%d0%bd%d0%bd%d1%8b%d0%b5-%d0%be-%d0%bf%d1%80%d0%be%d0%b4%d0%b0%d0%b6%d0%b0%d1%85-%d0%bf%d0%be%d1%81%d0%bb%d0%b5-%d0%b8%d1%81%d0%bf%d0%be%d0%bb%d1%8c%d0%b7%d0%be

Рис. 5. Данные о продажах после использования автозаполнения вместе с прогнозом продаж в четвертом квартале

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

%d1%80%d0%b8%d1%81-6-%d1%8d%d1%82%d0%b8-%d0%bd%d0%b0%d0%b7%d0%b2%d0%b0%d0%bd%d0%b8%d1%8f-%d0%b1%d1%83%d0%b4%d1%83%d1%82-%d0%b8%d1%81%d0%bf%d0%be%d0%bb%d1%8c%d0%b7%d0%be%d0%b2%d0%b0%d1%82%d1%8c

Рис. 6. Эти названия будут использовать функцию автозаполнения

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 165–168.


Прокомментировать