Как в Экселе протянуть формулу по строке или столбцу: 5 способов
Работая с таблицами Excel, пользователям часто приходится использовать для вычислений одни и те же формулы. Причем расположенные не просто рядом, а вдоль всей строки или столбца. Работу можно заметно упростить — для этого надо познакомиться с различными способами, как протянуть формулу в Эксель в нужном направлении.
Н аши советы помогут работать с обычными суммами значений в выбранном диапазоне ячеек или сложными вычислениями с десятками аргументов. Главное, что при большом количестве формул их будет легко расположить в нужных местах.
1 Простое протягивание формулы
Это самый простой и привычный для многих пользователей способ распространения формулы сразу на несколько ячеек строки или столбца . Он требует выполнения следующих действий:
- В первую ячейку с одной из сторон (например, сверху) надо записать нужную формулу и нажать Enter.
- После появления рассчитанного по формуле значения навести курсор в нижний правый угол ячейки. Подождать, пока толстый белый крестик не превратиться в тонкий черный.
- Нажать на крестик и, удерживая его, протянуть формулу в нужном направлении. В указанном примере — вниз.
Аргументы в формуле будут изменяться соответственно новому расположению. И если в самой первой ячейке это были F7 и G7, в последней позиции столбца это будет уже F12 и G12. Соответственно, если начинать распространять формулы по строкам, изменяться будут не цифры, а буквы в обозначениях ячеек.
Способ отличается простотой и высокой скоростью. Но не всегда подходит для больших таблиц. Так, если в столбце несколько сотен или даже тысяч значений, формулу проще растягивать другими способами, чтобы сэкономить время. Один из них – автоматическое копирование, требующее всего лишь двойного клика кнопкой мыши.
2 Быстрое автозаполнение
Еще один способ в Excel протянуть формулу до конца столбца с более высокой по сравнению с первой методикой скоростью. Требует от пользователя применить такие действия:
Как протянуть формулу вправо
Регистрация на форуме тут, о проблемах пишите сюда — alarforum@yandex.ru, проверяйте папку спам! Обязательно пройдите восстановить пароль
| Поиск по форуму |
| Расширенный поиск |
| К странице. |
| Ваша тема закрыта, почему это могло произойти? Возможно, Нет наработок или кода, если нужно готовое решение — создайте тему в разделе Фриланс и оплатите работу. Название темы включает слова — «Помогите», «Нужна помощь», «Срочно», «Пожалуйста». Название темы слишком короткое или не отражает сути вашего вопроса. Тема исчерпала себя, помните, один вопрос — одна тема Прочитайте правила и заново правильно создайте тему. |
| Serge_Bliznykov |
| Посмотреть профиль |
| Найти ещё сообщения от Serge_Bliznykov |
вставлю свои 5 копеек
двойной клик по маркеру заполнения классно: быстро и удобно, но.
этот способ приводит к тому, что у всех нижних ячеек появляется формат, как у текущей. То есть, если, например, кое-где имеется другая заливка или другие границы ячеек, то всё нижнее форматирование замениться на формат кликнутой ячейки.
Лучший вариант (на мой взгляд) все таки: копировать, перейти вниз, выделить на клавишах куда вставлять, а затем Правка-Специальная вставка-Формулы (сочетание клавишь удерживая зажатым alt при включенной русской раскладке клавиатуры и русском экселе: п е ф и нажать ввод)
| Konstantin_Z |
| Посмотреть профиль |
| Найти ещё сообщения от Konstantin_Z |
| Konstantin_Z |
| Посмотреть профиль |
| Найти ещё сообщения от Konstantin_Z |
Интенсив по Python: Работа с API и фреймворками 24-26 ИЮНЯ 2022. Знаете Python, но хотите расширить свои навыки?
Slurm подготовили для вас особенный продукт! Оставить заявку по ссылке — https://slurm.club/3MeqNEk
Как протянуть строки в Excel
Копирование и протягивание в Excel
В Excel сохраняются стандартные способы копирования, вырезания и вставки данных (путём соответствующих команд основного и контекстного меню, «горячих» клавиш Ctrl-C (копировать), Ctrl-X (вырезать), Ctrl-V (вставить)), а также перемещения диапазона ячеек с помощью мыши. Чтобы переместить диапазон, нужно выделить его мышью, «зацепить» мышью за любую из его границ и переместить в нужное место. Если нужно не переместить, а скопировать диапазон, при перемещении необходимо нажать и удерживать клавишу Ctrl.
Важно понимать, что при копировании (вставке, перетаскивании) в новую ячейку может попасть разное содержимое в зависимости от того, что содержалось в исходной ячейке: число/текст или формула. При этом стоит отметить, что при копировании формул ссылки в переносимых ячейках изменяются в целях сохранения структуры данных в книге. Далее рассмотрим это подробнее.
В Excel добавлен ещё один очень удобный метод копирования — протягивание, аналогичный вышеперечисленным командам, но выполняемый только с помощью мыши.
В нижнем правом углу выделенной ячейки/диапазона находится небольшой чёрный квадратик — маркер автозаполнения. Если нажать на него левой клавишей мыши и «потянуть» в любом направлении, содержимое ячейки/диапазона скопируется в новые ячейки. Это особенно удобно для заполнения больших таблиц однородными данными, а также копирования формата ячейки. Обратите внимание на следующий пример: выделены две ячейки с числами 1 и 2, выполнена операция протягивания. Excel «понял» принцип заполнения ячеек и автоматически заполнил следующие ячейки числами от 3 до 10.
Рис.5.1. Протягивание
Протягивать можно как одну ячейку по вертикали или горизонтали, так и целый диапазон из одной строчки или одного столбца ячеек.
Есть небольшая хитрость: если не тянуть за уголок, а два раза щёлкнуть мышью на маркере автозаполнения, то Excel автоматически выполнит операцию протягивания.
Дополнительная полезность протягивания — автоматическое формирование прогрессий, то есть последовательностей чисел или текстовых значений. Рассмотрим примеры.
Практическая работа: протягивание
Откройте лист Протягивание, абсолютные ссылки файла Учебник — основы. В ячейке А10 напишите «1», переместитесь снова на эту ячейку. Наведите указатель мыши на маркер заполнения, нажмите левую клавишу, и, удерживая её, потяните вниз, до ячейки А21. Содержимое исходной ячейки скопируется в них, теперь во всех ячейках стоят единицы. Таким образом работает протягивание по одному значению.
Теперь отмените последнюю операцию (через меню или комбинацией клавиш Ctrl-Z). Добавьте в ячейке А11 число «2», выделите диапазон А10:А11 и протяните его до ячейки А21. Excel «понял» зависимость двух ячеек А10 и А11 как арифметическую прогрессию, и при протягивании продолжил этот числовой ряд. Теперь все строчки этой таблицы пронумерованы по порядку.
То же самое сделайте со столбцом «Месяц», программа автоматически заполнит значения месяцев. То же со столбцом «Выручка»: выделите два первых значения и протяните их до строчки «Декабрь» (здесь предполагается, что выручка растёт на 15000 ежемесячно). Столбец с данными выручки заполняется автоматически.
Теперь разберёмся с разными видами ссылок.
Относительные и абсолютные ссылки в Excel
При протягивании важно помнить, что именно содержится в протягиваемых ячейках. Если в ячейке значение (число, текст, дата), то при протягивании в новые ячейки попадёт это значение (и формат). Если в ячейке была формула, то значение в новой ячейке будет зависеть от того, какой тип ссылок был использован. При протягивании все обычные ссылки на другие ячейки автоматически смещаются, это относительная ссылка. Чтобы ссылка не смещалась по ячейкам (т.е. чтобы сохранить ссылку на конкретную ячейку, строку или столбец), в формуле добавляется символ $ перед адресом строки и/или столбца, который необходимо оставить неизменным. Если символ $ добавляется в обоих случаях, то это абсолютная ссылка на конкретную ячейку. Если меняется только строка или только столбец, то такая ссылка является смешанной.
Практическая работа: абсолютные и относительные ссылки
Заполните столбец «Постоянные расходы»: обратите внимание на формулу в ячейке Е11. Протяните эту ячейку до декабря. Посмотрите на формулу в любой из ячеек Е12:Е21. Увидите, что адрес ячейки в формуле каждый раз новый. Это правильное использование относительной ссылки.
Теперь введите в ячейку D10 формулу для расчёта маржинальной прибыли в январе: «=C10*C7/(1+C7)». Маржинальная прибыль для января посчитана. Протяните эту формулу до декабрьской ячейки: увидите, что использование относительной ссылки в данном случае некорректно (выделите любую ячейку от D11 до D21, нажмите мышью на строку формул или нажмите F2). Программа верно берёт для расчёта значение выручки из столбца С, но значение маржинальной прибыли сместилось из-за протягивания, и теперь во всех ячейках, кроме первой, неверные формулы. Нам нужно, чтобы в исходной формуле адрес ячейки С10 менялся при протягивании, а адрес С7 — нет. Вернитесь в ячейку D10 и исправьте формулу следующим образом: «=C10*C$7/(1+C$7)». Значение в ячейке не изменилось, так как для этой ячейки все виды ссылок совпадут. Протяните новую формулу до декабрьской ячейки, теперь суммы рассчитаны верно. Здесь мы видим пример использования смешанной ссылки.
Добавьте суммирование годовой выручки: выделите ячейку В22, нажмите кнопку меню Главная — Сумма (иконка с символом ∑), либо клавиши Alt+=, в ячейку автоматически будет подставлена формула суммирования СУММ. Проверьте правильность диапазона суммирования В10:В21 и нажмите Enter, будет посчитана итоговая выручка. Протяните эту формулу вправо на три соседние ячейки. Добавьте расчёт прибыли до налогообложения в ячейку F10 и протяните её до декабря. �?тоговые суммы рассчитаются автоматически.
Теперь рассмотрим работу смешанных ссылок по столбцам и горизонтальное протягивание. В следующем задании в ячейку С32 поставьте формулу «=В32*(1+D29)». Для данной ячейки формула будет верная, но при протягивании вправо относительная ссылка испортит расчёт. Добавьте символ $ перед буквой столбца: «=В32*(1+$D29)» и протяните формулу горизонтально.
Рассмотрите последнее задание со всеми вариантами адресации, кроме относительной. В нём рассчитывается общая выручка, исходя из заданного базового объёма производства, средней цены за единицу в четырёх сценариях увеличения объёма производства. Попробуйте корректно написать формулу в ячейке С45 и заполнить всю остальную таблицу протягиванием: сначала протянуть ячейку горизонтально (или вертикально), потом протянуть всю строку (или столбец) на всю таблицу. Ссылка на ячейку с ценой С38 должна быть абсолютной как по строкам, так и по столбцам, поэтому в формуле эта ячейка будет такой: $C$38.
Специальная вставка в Excel
Как мы видим, при копировании или протягивании (которое является быстрым копированием) значение конечной ячейки определяет содержимое копируемой. Это не всегда удобно, и Excel предоставляет дополнительные возможности выборочного копирования.
�?ногда бывает необходимо сохранить данные, полученные в виде формул, как обычные значения.
Практическая работа: специальная вставка
Допустим, в последнем задании определено, что сценарий с 15-процентным приростом от базового плана самый приемлемый, но есть желание посмотреть, как изменится ситуация с изменением цены на товар.
Выделите столбец с названиями месяцев (диапазон А45:А56), скопируйте в буфер обмена. Теперь выделите ячейку В60 и нажмите правую кнопку мыши, выберите Специальная вставка, в открывшемся окне поставьте флажок Транспонировать, ОК. Теперь значения расположены не в столбец, а в строчку (это действие функции транспонирования). В ячейке А60 напишите «Цена», в ячейке А61 — «10», А62-«9», А63 — «8».
Добавьте колонку для итоговых значений в столбце N, рассчитайте их автосуммированием. Отформатируйте вновь созданную таблицу, на этот раз воспользовавшись удобным встроенным средством: выделите всю созданную таблицу А60:N63, выберите раздел Стили в меню Главная, нажмите кнопку Форматировать как таблицу и выберите стиль таблицы по вкусу.
Уменьшите размер шрифта в строках 56-59, нарисуйте границу таблицы, отформатируйте цвет ячеек по желанию. Скопируйте диапазон со значениями выручки F45:F56, вставьте его как значения и форматы чисел с транспонированием в ячейку В57. �?зменяя значения цены в ячейке С38, заполните две следующих строчки. Обратите внимание, что при копировании сохранился формат числа (при этом теперь в ячейках находятся не формулы, а значения), но формат ячейки не сохранился, что в данном случае очень удобно: нет необходимости форматировать таблицу заново.
Предыдущая глава: Глава 4. Защита данных от нежелательных изменений