Как заполнить формулами интервал ячеек

Как заполнить формулами интервал ячеек

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

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

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

Перетащите маркер заполнения по ячейкам, которые вы хотите заполнить.

Если маркер не отображается, возможно, он скрыт. Чтобы снова отобразить его:

Щелкните Параметры > файла

Выберите пункт Дополнительно.

В окне Параметры правкив поле Включить перетаскивать ячейки и заполнять ячейки.

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

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

Заполнение смежных ячеек формулами

Для заполнения формулы смежным диапазоном ячеек можно использовать команду Заполнить. Просто сделайте следующее:

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

Щелкните Главная > заливкаи выберите Вниз ,Справа,Вверхили Влево.

Сочетания клавиш: Вы также можете нажать CTRL+D, чтобы заполнить формулой столбец, или CTRL+R, чтобы заполнить формулой справа от строки.

Включить вычисление книги

При заполнении ячеек формулы не пересчитыются, если автоматическое вычисление книги не включено.

Вот как его можно включить:

Выберите Файл > Параметры.

Щелкните Формулы.

В области Вычисления в книгевыберите Авто.

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

Перетащите вниз или вправо от нужного столбца.

Сочетания клавиш: Можно также нажать CTRL+D, чтобы заполнить формулой ячейку в столбце, или CTRL+R, чтобы заполнить формулой ячейку справа в строке.

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

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

Как сделать автозаполнение в Excel

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

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

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

Функция автозаполнения.

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

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

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

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

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

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

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

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

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

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

Как заполнить большой диапазон двойным щелчком.

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

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

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

Получаем список чисел от 1 до 10 буквально за пару кликов.

О других возможностях функции «Заполнить» читайте ниже.

Введите в первую ячейку диапазона начальное число (допустим, 1). Во время протягивания маркера заполнения вниз одновременно с зажатой левой копкой мыши удерживаем клавишу Ctrl (на Mac – клавишу Option ). Рядом с знакомым нам значком в виде черного плюсика при этом должен появиться еще один плюсик поменьше – слева и сверху.

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

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

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

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

Как видите, инструмент автозаполнения отличает текст от чисел. И он даже знает, что есть 4 квартала.

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

Инструмент “Заполнить”

Сделать автозаполнение ячеек в Excel можно с помощью специального инструмента “Заполнить”. Алгоритм действий следующий:

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

Получаем автоматически заполненный диапазон ячеек. В данном случае – с датами.

С помощью функции “Заполнить” можно, в том числе, продолжить ряд прогрессии:

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

Настраиваем параметры прогрессии в появившемся окошке:

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

Использование функции мгновенного заполнения

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

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

Введем имя и фамилию в С2. Затем при помощи меню выберем инструмент Мгновенное заполнение или нажмем комбинацию CTRL+E .

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

Как создать собственный список для автозаполнения

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

1. Введите заголовок и заполните свой список.

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

1. Выберите диапазон ячеек, который содержит данные для вашего нового списка автозаполнения.
2. В Excel 2003 перейдите в Инструменты -> Параметры -> вкладка Пользовательские списки.

В Excel нажмите кнопку Office -> Параметры Excel -> Дополнительно -> прокрутите вниз, пока не увидите кнопку «Изменить настраиваемые списки…».

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

1. Нажмите кнопку «Импорт», чтобы поместить свой набор значений в поле «Пользовательские списки».

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

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

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

Если вам нужна серия повторяющихся значений, вы все равно можете использовать автозаполнение. Например, вам нужно повторить ряд: ДА, НЕТ, ИСТИНА, ЛОЖЬ. Сначала введите все эти значения вручную, чтобы дать Excel шаблон. Затем просто возьмите мышкой маркер заполнения и перетащите его в нужную ячейку.

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

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

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

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

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

Как вставить пустые ячейки при автозаполнении

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

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

То же самое можно делать и с пустыми столбцами, протащив маркер вправо.

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

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

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

Посмотрим, что предлагают эти варианты.

1. Копировать ячейки — заполняет диапазон одним и тем же значением.
2. Заполнить – автозаполнение заполнит диапазон в соответствии с заданным шаблоном.
3. Заполнить только форматы — эта опция автозаполнения Excel будет копировать только формат ячеек без извлечения каких-либо значений. Это может быть полезно, если вам нужно быстро скопировать форматирование, а затем ввести значения вручную.
4. Заполнить только значения — копирует только значения. Если фон исходных ячеек заполнен цветом, то он не сохранится.
5. Заполните дни / будни / месяцы / годы — эти функции делают то, что предполагают их названия. Если ваша начальная ячейка содержит один из них, вы можете быстро заполнить диапазон, щелкнув один из вариантов.
6. Линейное приближение – создает линейный ряд или линейный тренд наилучшего соответствия.
7. Экспоненциальное приближение – генерирует серию роста или геометрическую тенденцию роста.
8. Мгновенное заполнение – помогает ввести много повторяющейся информации и правильно отформатировать данные.
9. Прогрессия… — этот параметр вызывает диалоговое окно с перечнем дополнительных возможностей для выбора: какую именно числовую прогрессию вы хотите получить.

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

1. Другой способ получить список — щелкнуть маркер заполнения, перетащить его и затем щелкнуть значок «Параметры автозаполнения».

При нажатии на этот значок открывается список с параметрами.

Как видите, этот список просто повторяет некоторые функции, которые мы уже описали выше.

Самый быстрый способ автозаполнения формулами

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

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

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

1. Преобразуйте свой диапазон в таблицу Excel. Выберите любую ячейку в диапазоне данных и нажмите Ctrl+Т, чтобы вызвать диалоговое окно «Создать таблицу». Или используйте кнопку «Форматировать как таблицу» на ленте «Главная». Если в ваших данных есть заголовки столбцов, убедитесь, что установлен флажок «Таблица с заголовками». Обычно Excel распознает заголовки таблиц автоматически, а если нет, установите этот флажок вручную.
2. Введите формулу в первую ячейку пустого столбца.

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

Если вы хотите по какой-то причине вернуться от таблицы к обычному диапазону данных, выберите любую ячейку в вашей таблице, затем по правому клику мышкой выберите Таблица => Преобразовать в диапазон.

Вставьте одни и те же данные в несколько ячеек, используя Ctrl+Enter

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

Для этого выполним следующие действия.

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

1. Отредактируйте одну из выделенных ячеек, введя в нее нужное слово «нет»
2. Чтобы сохранить изменения, нажмите Ctrl+Enter вместо Enter . В результате все выбранные ячейки будут заполнены введенными вами данными, как на скриншоте ниже.

Как включить или отключить автозаполнение в Excel

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

1. Нажмите Файл в Excel 2010-2019 или кнопкуOffice в версии 2007.
2. Перейдите в Параметры -> Дополнительно и снимите флажок Разрешить маркер заполнения и перетаскивание ячеек.

На скриншоте выше вы видите, где находится в Excel автозаполнение.

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

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

Включение или отключение параметров автозаполнения

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

1. Перейдите к кнопке Файл / Офис -> Параметры -> Дополнительно и найдите раздел Вырезать, скопировать и вставить.
2. Снимите флажок Показывать кнопки параметров вставки при вставке содержимого.

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

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

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

Как сделать диаграмму Ганта — Думаю, каждый пользователь Excel знает, что такое диаграмма и как ее создать. Однако один вид графиков остается достаточно сложным для многих — это диаграмма Ганта. В этом кратком руководстве я постараюсь показать…
Проверка данных в Excel: как сделать, использовать и убрать — Мы рассмотрим, как выполнять проверку данных в Excel: создавать правила проверки для чисел, дат или текстовых значений, создавать списки проверки данных, копировать проверку данных в другие ячейки, находить недопустимые записи,…
Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых…
Как полностью или частично зафиксировать ячейку в формуле — При написании формулы Excel знак $ в ссылке на ячейку сбивает с толку многих пользователей. Но объяснение очень простое: это всего лишь способ ее зафиксировать. Знак доллара в данном случае служит только…
Чем отличается абсолютная, относительная и смешанная адресация — Важность ссылки на ячейки Excel трудно переоценить. Ссылка включает в себя адрес, из которого вы хотите получить информацию. При этом используются два основных вида адресации – абсолютная и относительная. Они…
Относительные и абсолютные ссылки – как создать и изменить — В руководстве объясняется, что такое адрес ячейки, как правильно записывать абсолютные и относительные ссылки в Excel, как ссылаться на ячейку на другом листе и многое другое. Ссылка на ячейки Excel,…
6 способов быстро транспонировать таблицу — В этой статье показано, как столбец можно превратить в строку в Excel с помощью функции ТРАНСП, специальной вставки, кода VBA или же специального инструмента. Иначе говоря, мы научимся транспонировать таблицу.…
4 способа быстро убрать перенос строки в ячейках Excel — В этом совете вы найдете 4 совета для удаления символа переноса строки из ячеек Excel. Вы также узнаете, как заменять разрывы строк другими символами. Все решения работают с Excel 2019, 2016, 2013…
Как безопасно удалить пустые ячейки в Excel и как не нужно никогда это делать — В этом руководстве вы узнаете, как правильно и безопасно удалять пустые ячейки в таблицах Excel, чтобы они выглядели четкими и профессиональными. Пустые ячейки – это неплохо, если вы намеренно оставляете…
Как быстро заполнить пустые ячейки в Excel? — В этой статье вы узнаете, как выбрать сразу все пустые ячейки в электронной таблице Excel и заполнить их значением, находящимся выше или ниже, нулями или же любым другим шаблоном. Заполнять…

Excel. Диапазон ячеек в формуле

Безусловно, понятие диапазона в Excel – одно из ключевых. Что это такое? Все мы знаем, что лист состоит из ячеек. Вот если несколько из них содержат какую-то информацию, то это диапазон. Простыми словами, это две и более ячейки в документе.

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

Как выбирать ячейки, строки и столбцы

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

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

Чтобы выбрать ячейку, нужно нажать по ней. Каждая ячейка имеет адрес. Например, расположенная на пересечении колонки C и ряда 3 называется C3.

1

Соответственно, чтобы выбрать колонку, необходимо нажать на букву, отображающую название столбца. В нашем случае это колонка C.

2

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

3

Диапазон ячеек: пример

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

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

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

4

Как заполнить диапазон

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

1. Ввести нужное значение в ячейку B2. Оно может быть как числовым, так и текстовым. Также возможно введение формулы. В нашем случае это цифра 2. 5
2. Далее нажимаем маркер автозаполнения (как раз тот самый квадратик, который мы ранее просили не нажимать) и перетаскиваем его вниз до конца диапазона.

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

6

Автозаполнение – одна из самых востребованных функций Excel. Она позволяет осуществлять запись в ячейки диапазона не только одного значения, но и целого набора данных, которые соответствуют определенной закономерности. Например, числовой ряд 2, 4, 6, 8, 10 и так далее.

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

7 8

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

9

После этого осуществляем уже знакомое нами перетаскивание.

10

Перемещение диапазона

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

Потом нужно просто переместить его в нужное место и отпустить мышь.

11 12

Копирование и вставка диапазона

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

Чтобы это сделать, нужно выделить диапазон, сделать по нему правый клик мыши и нажать «Копировать». Также можно воспользоваться комбинацией клавиш Ctrl + C.

13

Также можно найти специальную кнопку на вкладке «Главная» в группе «Буфер обмена».

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

14

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

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

15

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

16

Таким вот способом у нас получилось вставить строку.

17

Именованные диапазоны

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

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

Но вообще, способов несколько. Давайте рассмотрим некоторые примеры.

Пример 1

Предположим, перед нами стоит задача определить объем продажи товаров. Для этой цели у нас отведен диапазон B2:B10. Чтобы присвоить имя, необходимо использовать абсолютные ссылки.

18

В общем, наши действия следующие:

1. Выделить необходимый диапазон.
2. Перейти на вкладку «Формулы» и там найти команду «Присвоить имя».
3. Далее появится диалоговое окно, в котором необходимо указать имя диапазона. В нашем случае это – «Продажи».
4. Там еще находится поле «Область», которое дает возможность выбрать лист, на котором этот диапазон находится.
5. Проверьте, чтобы был указан правильный диапазон. Формула должна быть следующей: =’1сезон’!$B$2:$B$10
6. Кликните «ОК». 19

Теперь можно вместо адреса диапазона вводить его имя. Так, с помощью формулы =СУММ(Продажи) можно вычислить сумму продаж для всех товаров.

20

Аналогично можно осуществить расчет среднего объема продаж с помощью формулы =СРЗНАЧ(Продажи) .

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

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

Пример 2

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

На этом скриншоте диапазоны следующие.

B2:B10 , C 2: C 10 , D 2: D 10 , E2:E10

Соответственно, нам нужно разместить формулы в ячейках B11, C11, D11 и E11.

21

Конечно, чтобы воплотить эту задачу в реальность, вы можете создать несколько диапазонов, но это немного неудобно. Значительно лучше воспользоваться одним. Чтобы упростить так себе жизнь, необходимо использовать относительную адресацию. В таком случае достаточно просто иметь один диапазон, который в случае с нами получит название «Сезонные_продажи»

Для этого необходимо открыть диспетчер имен, ввести имя в диалоговом окне. Механизм тот же самый. Перед тем, как нажимать «ОК», нужно убедиться, что в строку «Диапазон» введена формула =’4сезона’!B$2:B$10

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

Далее методика действий та же самая.

Теперь нам нужно ввести в ячейку B11 формулу =СУММ(Сезонные_Продажи) . Далее, используя маркер автозаполнения, переносим ее в соседние ячейки, и получается такой результат.

22

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

23

Пример 3

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

=СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)

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

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

24

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

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

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

25

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

1. Открыть окно «Присвоить имя».
2. Заполнить поля так, как указано на скриншоте. 26

Важно обратить внимание, что вместо диапазона используется формула с функцией СМЕЩ совместно с функцией СЧЕТ .

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

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

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

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

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

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

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

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

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

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

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

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

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

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