Как протянуть формулу в эксель по всему столбцу

Как в Экселе протянуть формулу по строке или столбцу: 5 способов

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

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

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

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

• В первую ячейку с одной из сторон (например, сверху) надо записать нужную формулу и нажать Enter.
• После появления рассчитанного по формуле значения навести курсор в нижний правый угол ячейки. Подождать, пока толстый белый крестик не превратиться в тонкий черный.
• Нажать на крестик и, удерживая его, протянуть формулу в нужном направлении. В указанном примере — вниз.

Аргументы в формуле будут изменяться соответственно новому расположению. И если в самой первой ячейке это были F7 и G7, в последней позиции столбца это будет уже F12 и G12. Соответственно, если начинать распространять формулы по строкам, изменяться будут не цифры, а буквы в обозначениях ячеек.

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

2 Быстрое автозаполнение

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

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

Как протянуть формулу в Excel: 5 способов продлить формулу до конца таблицы

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

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

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

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

Виды формул

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

Простые

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

СУММ

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

= СУММ (значение1; значение2)

= СУММ (начальный_интервал: конечный_интервал)

ПРОИЗВЕД

Умножьте все числа в выбранном диапазоне.

= ПРОДУКТ (начальный_интервал: конечный_интервал)

ОКРУГЛ

Помогает округлить дробное число вверх (ROUNDUP) или вниз (ROUNDDOWN).

ВПР

Это поиск необходимых данных в таблице или диапазоне по строкам. Рассмотрим функцию на примере поиска сотрудника из списка по коду.

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

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

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

Альтернативные функции — ИНДЕКС / ПОИСК.

СЦЕПИТЬ/СЦЕП

Объедините содержимое нескольких ячеек.

= СЦЕПИТЬ (значение1; значение2) — весь текст

= СЦЕПИТЬ (значение1; «»; значение2) — пробел или знак препинания между словами

КОРЕНЬ

Вычислите квадратный корень из любого числа.

ПРОПИСН

Альтернатива Caps Lock для преобразования текста.

СТРОЧН

Преобразование текста в нижний регистр.

СЧЁТ

Подсчитайте количество ячеек с числами.

=СЧЁТ(диапазон_ячеек)

СЖПРОБЕЛЫ

Удаляет лишние пробелы. Это будет полезно, когда данные переносятся в таблицу из другого источника.

Сложные

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

Важно! Критерии должны быть заключены в кавычки.

ПСТР

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

= MID (ссылка_в_ячейке_с_текстом; начальное_номер_позиции; число_символов_в_экстракт)

ЕСЛИ

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

= IF (what_data_checked; if_value_met_conditional_condition; if_value_doesn’t_met_condition_condition)

СУММЕСЛИ

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

= СУММЕСЛИ (DO2: DO5; SI2: SI5; «90»)

На основе примера программа рассчитала суммы всех чисел больше 10.

СУММЕСЛИМН

Суммирует числа на основе нескольких указанных условий.

= СУММЕСЛИ (диапазон_суммы; диапазон_условий1; условие1; диапазон_условий2; условие2;…)

Программа рассчитывала итоговые зарплаты кассиров.

Функции СЧЁТЕСЛИ, СРЕДНЕЛИ и т.д. Работают одинаково.

Комбинированные

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

Возникает проблема: найти сумму трех чисел и умножить ее на коэффициент 1,3, если оно меньше 80, и на коэффициент 1,6, если оно больше 80.

Комбинированные формулы могут включать 10 и более функций.

Пример

Использование функций для вычислений

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

Поговорим о некоторых особенностях.

Как задать формулы «Если» в Excel

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

В первой ячейке под заголовком этого столбца установите указатель и щелкните элемент «Логический» на панели инструментов. Выбираем функцию «Если».

Как и при вводе любой функции, откроется окно для ввода аргументов.

Указываем состояние. Для этого щелкните первую строку и выберите первую ячейку «Продано». Далее ставим знак «>» и указываем цифру 4.

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

Нажмите ОК и скопируйте запись для всего столбца.

Чтобы в ячейке не отображалось «ЛОЖЬ», снова откройте функцию и исправьте ее. Переместите указатель на первую ячейку и нажмите Fx рядом со строкой формул. Поставьте курсор на третью строку и заключите пробел в кавычки.

Затем ОК и снова скопируйте.

Теперь посмотрим, какой товар нужно приобрести.

Формула текст в Excel

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

В первую ячейку вставляем функцию (кнопка «Текст» в разделе «Формулы»).

В окне аргументов укажите ссылку на ячейку итоговой суммы и установите формат «# руб.».

Нажмите ОК и скопируйте.

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

«VALUE» указывает, что расчет не может быть выполнен.
Вы можете увидеть примеры форматов на скриншоте.

Формула даты в Excel

Excel предлагает множество возможностей для работы с датами. Один из них, DATE, позволяет построить дату из трех чисел. Это полезно, если у вас есть три разных столбца: день, месяц, год.

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

Соответственно расположите адреса ячеек и нажмите ОК.

Скопируйте запись.

Автосумма в Excel

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

Перейдите на панель формул и нажмите «Автосумма».

Excel автоматически выделит ближайший диапазон чисел.

Можно выделить другой диапазон. В этом примере Excel все сделал правильно. Щелкните ОК. Обратите внимание на содержимое ячейки. Функция СУММ заменена автоматически.

При вводе диапазона указываются адрес первой ячейки, двоеточие и адрес последней ячейки. «:» Означает «Взять все ячейки между первой и последней. Если вам нужно перечислить несколько ячеек, разделите их адреса точкой с запятой:
СУММ (F5; F8; F11)

Быстрое автозаполнение

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

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

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

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

Как посчитать количество строк (с одним, двумя или более условиями)

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

Ну, например, сколько раз имя «Саша» встречается в таблице ниже (см. Скриншот). Очевидно, дважды (но это потому, что таблица слишком мала и взята в качестве иллюстративного примера). Как можно рассчитать это по формуле?

«= СЧЁТЕСЛИ (A2: A7; A2)» — где:

• A2: A7 — диапазон, в котором будут проверяться и считываться строки;
• A2 — условие установлено (обратите внимание, что можно было написать условие типа «Саша», а можно просто указать ячейку).

Результат показан в правой части скриншота ниже.

Количество строк с условием

А теперь представьте себе более крупную проблему: вам нужно подсчитать строки, в которых появляется имя «Саша» и где цифра «6» будет в столбце «B». Забегая вперед, скажу, что есть только одна из этих строк (скриншот с примером ниже).

Формула будет выглядеть так:

= СЧЁТЕСЛИМН (A2: A7; A2; B2: B7; «6») — (примечание: обратите внимание на кавычки — они должны быть такими, как на скриншоте ниже, а не как у меня), где:

A2: A7; A2 — первый интервал и условие поиска (аналогично примеру выше);

B2: B7; «6» — второй диапазон и условие поиска (обратите внимание, что условие можно задать несколькими способами: указать ячейку или просто текст / число пишется в кавычках).

Суммируем диапазон ячеек.

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

Формула расчета выглядит так:

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

В формуле последовательно перечисляем несколько диапазонов:

= СУММ (B2: D4; B8: D10)

Конечно может не два, а гораздо больше: до 255 штук.

Сумма каждых N строк.

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

Мы используем тот факт, что SUM () может добавлять значения не только в диапазон данных, но и в массив. Этот массив значений может быть предоставлен функцией СМЕЩЕНИЕ.

Помните, что здесь вам нужно указать несколько аргументов:

1. Отправная точка. Обратите внимание, что мы ввели C2 как абсолютную ссылку.

2. Сколько ступенек вниз нужно сделать

3. Сколько шагов вправе предпринять. Затем мы подходим к начальной точке (вверху слева) массива.

4. Сколько значений взять, снова снижаясь.

5. Сколько столбцов будет в массиве. Мы приходим к конечной точке (внизу справа) массива значений.

Итак, формула для первой недели:

= СУММ (СМЕЩЕНИЕ ($ C $ 2; (СТРОКА () — 2) * 5,0,5,1))

В этом случае ROW () похож на наш недельный счетчик. Обратный отсчет должен начинаться с 0, чтобы инициировать действия непосредственно из ячейки C2, без движения вниз. Для этого мы используем STRING () — 2. Поскольку сама формула находится в ячейке F2, результатом будет 0. Исходной точкой будет C2, а ее конец будет иметь 5 меньших значений в том же столбце.

СУММ просто добавит пять предложенных значений.

Для 2-й недели в F3 просто скопируйте формулу. LINE () — 2 даст здесь результат 1, поэтому начало массива будет 1 * 5 = 5, что составляет 5 значений в ячейках от C7 до C11. И т.п.

Работа в Excel с формулами: пример

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

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

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

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

Как вычислить сумму каждой N-ой строки.

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

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

Это можно сделать двумя способами.

Первый — самый простой, «передний». Складываем все номера нужного отдела обычной математической операцией сложения. Звучит просто, но представьте, что у вас есть статистика, скажем, за 3 года? Придется вычислить 36 чисел…

Второй способ — более «продвинутый», но универсальный.

= СУММ (ЕСЛИ (ОТДЫХ (СТРОКА (C2: C16) +1; 3) = 0; C2: C16))

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

Как это работает? Нам нужен первый, третий, шестой и так далее. С помощью функции LINE () вычисляем номер текущей позиции. И если остаток от деления на 3 равен нулю, значение будет учтено при вычислении. В противном случае нет.

Для этого счетчика мы будем использовать номера строк. Но наша первая цифра находится во второй строке листа Excel. Так как необходимо начинать с первой позиции, а затем брать каждую третью, а диапазон начинается со 2-й строки, поэтому мы добавляем 1 к его порядковому номеру, тогда счетчик начнет отсчет с числа 3. Для этого, мы используем выражение LINE (C2: C16) +1. Получаем 2 + 1 = 3, остаток от деления на 3 равен нулю. Итак, возьмем первый, третий, шестой и так далее.

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

Когда мы найдем продажи для отдела 2, мы изменим выражение:

= СУММ (ЕСЛИ (ОТДЫХ (СТРОКА (C2: C16); 3) = 0; C2: C16))

Мы ничего не добавляем, так как первое совпадающее значение находится на 3-й позиции.

Аналогично для Дивизиона 3

= СУММ (ЕСЛИ (ОТДЫХ (СТРОКА (C2: C16) -1,3) = 0; C2: C16))

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

И, конечно же, не забудьте нажать CTRL + SHIFT + ENTER.

Примечание. Таким же образом можно суммировать каждый n-й столбец таблицы. Только вместо функции СТРОКА () вам нужно будет использовать СТОЛБЕЦ().

Способ 3. Наглядное суммирование

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

1. Выберите мышью нужный столбец или строку.
2. Посмотрите на результат.

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

Как найти сумму наибольших (наименьших) значений.

Цель: сложить 3 максимальных или 3 минимальных значения.

Функция НАИБОЛЬШИЙ возвращает наибольшее значение в списке данных. Хитрость в том, что его второй аргумент показывает, какое именно значение нужно вернуть: 1 — наибольшее, 2 — второе наибольшее и т.д. И если вы укажете , то вам понадобятся три самых больших. Но не забудьте применить формулу массива и закончить с помощью CTRL + SHIFT + ENTER.

= СУММ (НАИБОЛЬШИЙ (B2: D13; ))

Аналогичная ситуация и с меньшими значениями:

= СУММ (МАЛЕНЬКИЙ (B2: D13; ))

Как вводить и скопировать формулы в Excel

Они всегда вводятся после нажатия «=». Но что, если существует много однотипных вычислений? В этом случае вы можете указать один, а затем скопировать его. Для этого введите формулу, затем «растяните» ее в нужном направлении для умножения.
Поместите указатель на ячейку, которую нужно скопировать, и переместите указатель мыши в правый нижний угол (над квадратом). Он должен иметь форму простого креста с равными сторонами.

Нажмите левую кнопку и перетащите.

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

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

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

Цена с НДС рассчитывается как цена * (1 + НДС). Вставляем последовательность в первую ячейку.

Попробуем скопировать запись.

Результат странный.

Проверим содержимое второй ячейки.

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

Нажмите F4. Адрес будет разбавлен знаком «$». Это абсолютно клеточный признак.

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

Как написать формулу в Excel

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

1. Каждый начинается со знака «=».
2. В расчетах могут участвовать значения из ячеек и функций.
3. Операторы используются как обычные математические символы для операций.
4. Когда вы вставляете запись, ячейка по умолчанию отражает результат расчета.
5. Вы можете увидеть конструкцию в строке над таблицей.

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

Итак, как создать и вставить формулу в Excel? Действуйте по следующему алгоритму:

• Установите указатель на ячейку, в которой будет производиться расчет.
  
• Нажмите знак «=» на клавиатуре «=».
  
• Щелкните мышью по первой ячейке, участвующей в вычислениях.
  
• Используйте знак оператора, например «*».
  
• Щелкните по второй ячейке.
  
• Нажмите Ввод.
  
• Наведите указатель мыши на ячейку с вычислениями, и вы увидите, как выглядит результат ваших действий.
  

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

Обозначение Значение

+ Дополнение
— Вычитание
/ Разделение
* Умножение

Если вам нужно указать число, а не адрес ячейки, введите его с клавиатуры. Чтобы указать отрицательный знак в формуле Excel, нажмите «-».

Считаем сумму в столбце в Экселе

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

Способ 1: Просмотр общей суммы

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

1. С помощью мыши выделите диапазон ячеек в столбце, сумму значений, которые вы хотите вычислить. В нашем примере это будут ячейки с 3 по 9 в столбце G.

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

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

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

Примечание: Слева от суммы указывается количество выбранных ячеек, а также среднее значение для всех указанных в них чисел.

Способ 2: Автосумма

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

Щелкните левой кнопкой мыши (ЛКМ) по пустой ячейке, расположенной ниже тех, количество которых вы хотите вычислить.

Щелкните ЛКМ кнопку «Сумма», расположенную на панели инструментов «Редактировать» (вкладка «Главная»). Как вариант, вы можете использовать комбинацию клавиш «ALT» + «=».

Убедитесь, что формула, отображаемая в выделенной ячейке и строке формул, содержит адрес первого и последнего адреса, который вы хотите добавить, и нажмите «ENTER».

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

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

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

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

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

С помощью мыши выберите диапазон ячеек, который вы хотите добавить, и нажмите «ENTER».

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

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

Способ 3: Автосумма нескольких столбцов

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

Щелкните ЛКМ по ячейке, в которой вы хотите отобразить сумму всех значений.

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

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

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

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

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

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

Способ 2. Ручной ввод формулы

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

1. Установите курсор на ячейку с будущей суммой.
2. Введите =. Любая формула в Excel начинается с равенства.
3. Щелкните левой кнопкой мыши по ячейкам, которые нужно добавить, вставив между ними + и Enter.

3 Протягивание без изменения ячеек в формуле

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

Для развертывания без изменения адреса ячейки выполните те же действия, что и для обычного извлечения или автоматического копирования. Но при вводе формулы следует исправить адреса, которые не изменятся. Для этого используются символы доллара — $. Если одна и та же ячейка используется в каждой новой записи столбца в вычислениях, значки должны быть размещены как перед номером строки, так и перед буквой, обозначающей столбец. Как в примере: $ G $ 6.

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

4 Простое копирование

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

• Запишите желаемую формулу в одну из крайних ячеек строки или столбца и нажмите Enter.
• Скопируйте значение функции: с помощью контекстного меню, значка на панели или комбинации клавиш Ctrl + C.
• Поместите курсор на противоположную сторону столбца или строки.
• Нажмите клавиши Ctrl + Shift + стрелка вверх. Результат — выбор нужного диапазона, даже если в этом разделе столбца несколько сотен или тысяч точек.
• Введите формулу. Самый простой способ сделать это — нажать комбинацию Ctrl + V.

Результатом будет такое же распределение столбцов функции, как и при использовании метода n. 2. Но, наоборот, здесь можно выбрать только часть диапазона. Или, наоборот, растяните этот участок дальше, даже если есть пустые строки. Однако во втором случае лишнее значение придется убирать вручную.

Этот небольшой трюк работает и при укладке вдоль линии. В этом случае вместо нажатия Ctrl + Shift + Стрелка вверх вам нужно будет нажать Ctrl + Shift + Стрелка влево (или вправо, если скопированная формула находится в крайнем левом столбце).

• https://ichip.ru/sovety/ekspluataciya/kak-v-eksele-protyanut-formulu-po-stroke-ili-stolbcu-5-sposobov-752522
• https://sysadmin-note.ru/article/kak-sdelat-formulu-v-excel/
• https://support.microsoft.com/ru-ru/office/%D1%81%D1%82%D0%BE%D0%BB%D0%B1%D0%B5%D1%86-%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D1%8F-%D1%81%D1%82%D0%BE%D0%BB%D0%B1%D0%B5%D1%86-44e8c754-711c-4df3-9da4-47a55042554b
• https://WindowsTips.ru/formuly-v-excel
• https://ocomp.info/kak-napisat-formulu-v-excel.html
• https://mister-office.ru/funktsii-excel/sum-function-excel.html
• https://hd01.ru/info/kak-poschitat-summu-vsego-stolbca-v-excel/

Как протянуть формулу в Excel: 5 способов продлить формулу до конца таблицы

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

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

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

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

Виды формул

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

Простые

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

СУММ

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

= СУММ (значение1; значение2)

= СУММ (начальный_интервал: конечный_интервал)

ПРОИЗВЕД

Умножьте все числа в выбранном диапазоне.

= ПРОДУКТ (начальный_интервал: конечный_интервал)

ОКРУГЛ

Помогает округлить дробное число вверх (ROUNDUP) или вниз (ROUNDDOWN).

ВПР

Это поиск необходимых данных в таблице или диапазоне по строкам. Рассмотрим функцию на примере поиска сотрудника из списка по коду.

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

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

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

Альтернативные функции — ИНДЕКС / ПОИСК.

СЦЕПИТЬ/СЦЕП

Объедините содержимое нескольких ячеек.

= СЦЕПИТЬ (значение1; значение2) — весь текст

= СЦЕПИТЬ (значение1; «»; значение2) — пробел или знак препинания между словами

КОРЕНЬ

Вычислите квадратный корень из любого числа.

ПРОПИСН

Альтернатива Caps Lock для преобразования текста.

СТРОЧН

Преобразование текста в нижний регистр.

СЧЁТ

Подсчитайте количество ячеек с числами.

=СЧЁТ(диапазон_ячеек)

СЖПРОБЕЛЫ

Удаляет лишние пробелы. Это будет полезно, когда данные переносятся в таблицу из другого источника.

Сложные

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

Важно! Критерии должны быть заключены в кавычки.

ПСТР

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

= MID (ссылка_в_ячейке_с_текстом; начальное_номер_позиции; число_символов_в_экстракт)

ЕСЛИ

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

= IF (what_data_checked; if_value_met_conditional_condition; if_value_doesn’t_met_condition_condition)

СУММЕСЛИ

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

= СУММЕСЛИ (DO2: DO5; SI2: SI5; «90»)

На основе примера программа рассчитала суммы всех чисел больше 10.

СУММЕСЛИМН

Суммирует числа на основе нескольких указанных условий.

= СУММЕСЛИ (диапазон_суммы; диапазон_условий1; условие1; диапазон_условий2; условие2;…)

Программа рассчитывала итоговые зарплаты кассиров.

Функции СЧЁТЕСЛИ, СРЕДНЕЛИ и т.д. Работают одинаково.

Комбинированные

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

Возникает проблема: найти сумму трех чисел и умножить ее на коэффициент 1,3, если оно меньше 80, и на коэффициент 1,6, если оно больше 80.

Комбинированные формулы могут включать 10 и более функций.

Пример

Использование функций для вычислений

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

Поговорим о некоторых особенностях.

Как задать формулы «Если» в Excel

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

В первой ячейке под заголовком этого столбца установите указатель и щелкните элемент «Логический» на панели инструментов. Выбираем функцию «Если».

Как и при вводе любой функции, откроется окно для ввода аргументов.

Указываем состояние. Для этого щелкните первую строку и выберите первую ячейку «Продано». Далее ставим знак «>» и указываем цифру 4.

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

Нажмите ОК и скопируйте запись для всего столбца.

Чтобы в ячейке не отображалось «ЛОЖЬ», снова откройте функцию и исправьте ее. Переместите указатель на первую ячейку и нажмите Fx рядом со строкой формул. Поставьте курсор на третью строку и заключите пробел в кавычки.

Затем ОК и снова скопируйте.

Теперь посмотрим, какой товар нужно приобрести.

Формула текст в Excel

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

В первую ячейку вставляем функцию (кнопка «Текст» в разделе «Формулы»).

В окне аргументов укажите ссылку на ячейку итоговой суммы и установите формат «# руб.».

Нажмите ОК и скопируйте.

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

«VALUE» указывает, что расчет не может быть выполнен.
Вы можете увидеть примеры форматов на скриншоте.

Формула даты в Excel

Excel предлагает множество возможностей для работы с датами. Один из них, DATE, позволяет построить дату из трех чисел. Это полезно, если у вас есть три разных столбца: день, месяц, год.

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

Соответственно расположите адреса ячеек и нажмите ОК.

Скопируйте запись.

Автосумма в Excel

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

Перейдите на панель формул и нажмите «Автосумма».

Excel автоматически выделит ближайший диапазон чисел.

Можно выделить другой диапазон. В этом примере Excel все сделал правильно. Щелкните ОК. Обратите внимание на содержимое ячейки. Функция СУММ заменена автоматически.

При вводе диапазона указываются адрес первой ячейки, двоеточие и адрес последней ячейки. «:» Означает «Взять все ячейки между первой и последней. Если вам нужно перечислить несколько ячеек, разделите их адреса точкой с запятой:
СУММ (F5; F8; F11)

Быстрое автозаполнение

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

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

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

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

Как посчитать количество строк (с одним, двумя или более условиями)

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

Ну, например, сколько раз имя «Саша» встречается в таблице ниже (см. Скриншот). Очевидно, дважды (но это потому, что таблица слишком мала и взята в качестве иллюстративного примера). Как можно рассчитать это по формуле?

«= СЧЁТЕСЛИ (A2: A7; A2)» — где:

• A2: A7 — диапазон, в котором будут проверяться и считываться строки;
• A2 — условие установлено (обратите внимание, что можно было написать условие типа «Саша», а можно просто указать ячейку).

Результат показан в правой части скриншота ниже.

Количество строк с условием

А теперь представьте себе более крупную проблему: вам нужно подсчитать строки, в которых появляется имя «Саша» и где цифра «6» будет в столбце «B». Забегая вперед, скажу, что есть только одна из этих строк (скриншот с примером ниже).

Формула будет выглядеть так:

= СЧЁТЕСЛИМН (A2: A7; A2; B2: B7; «6») — (примечание: обратите внимание на кавычки — они должны быть такими, как на скриншоте ниже, а не как у меня), где:

A2: A7; A2 — первый интервал и условие поиска (аналогично примеру выше);

B2: B7; «6» — второй диапазон и условие поиска (обратите внимание, что условие можно задать несколькими способами: указать ячейку или просто текст / число пишется в кавычках).

Суммируем диапазон ячеек.

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

Формула расчета выглядит так:

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

В формуле последовательно перечисляем несколько диапазонов:

= СУММ (B2: D4; B8: D10)

Конечно может не два, а гораздо больше: до 255 штук.

Сумма каждых N строк.

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

Мы используем тот факт, что SUM () может добавлять значения не только в диапазон данных, но и в массив. Этот массив значений может быть предоставлен функцией СМЕЩЕНИЕ.

Помните, что здесь вам нужно указать несколько аргументов:

1. Отправная точка. Обратите внимание, что мы ввели C2 как абсолютную ссылку.

2. Сколько ступенек вниз нужно сделать

3. Сколько шагов вправе предпринять. Затем мы подходим к начальной точке (вверху слева) массива.

4. Сколько значений взять, снова снижаясь.

5. Сколько столбцов будет в массиве. Мы приходим к конечной точке (внизу справа) массива значений.

Итак, формула для первой недели:

= СУММ (СМЕЩЕНИЕ ($ C $ 2; (СТРОКА () — 2) * 5,0,5,1))

В этом случае ROW () похож на наш недельный счетчик. Обратный отсчет должен начинаться с 0, чтобы инициировать действия непосредственно из ячейки C2, без движения вниз. Для этого мы используем STRING () — 2. Поскольку сама формула находится в ячейке F2, результатом будет 0. Исходной точкой будет C2, а ее конец будет иметь 5 меньших значений в том же столбце.

СУММ просто добавит пять предложенных значений.

Для 2-й недели в F3 просто скопируйте формулу. LINE () — 2 даст здесь результат 1, поэтому начало массива будет 1 * 5 = 5, что составляет 5 значений в ячейках от C7 до C11. И т.п.

Работа в Excel с формулами: пример

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

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

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

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

Как вычислить сумму каждой N-ой строки.

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

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

Это можно сделать двумя способами.

Первый — самый простой, «передний». Складываем все номера нужного отдела обычной математической операцией сложения. Звучит просто, но представьте, что у вас есть статистика, скажем, за 3 года? Придется вычислить 36 чисел…

Второй способ — более «продвинутый», но универсальный.

= СУММ (ЕСЛИ (ОТДЫХ (СТРОКА (C2: C16) +1; 3) = 0; C2: C16))

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

Как это работает? Нам нужен первый, третий, шестой и так далее. С помощью функции LINE () вычисляем номер текущей позиции. И если остаток от деления на 3 равен нулю, значение будет учтено при вычислении. В противном случае нет.

Для этого счетчика мы будем использовать номера строк. Но наша первая цифра находится во второй строке листа Excel. Так как необходимо начинать с первой позиции, а затем брать каждую третью, а диапазон начинается со 2-й строки, поэтому мы добавляем 1 к его порядковому номеру, тогда счетчик начнет отсчет с числа 3. Для этого, мы используем выражение LINE (C2: C16) +1. Получаем 2 + 1 = 3, остаток от деления на 3 равен нулю. Итак, возьмем первый, третий, шестой и так далее.

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

Когда мы найдем продажи для отдела 2, мы изменим выражение:

= СУММ (ЕСЛИ (ОТДЫХ (СТРОКА (C2: C16); 3) = 0; C2: C16))

Мы ничего не добавляем, так как первое совпадающее значение находится на 3-й позиции.

Аналогично для Дивизиона 3

= СУММ (ЕСЛИ (ОТДЫХ (СТРОКА (C2: C16) -1,3) = 0; C2: C16))

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

И, конечно же, не забудьте нажать CTRL + SHIFT + ENTER.

Примечание. Таким же образом можно суммировать каждый n-й столбец таблицы. Только вместо функции СТРОКА () вам нужно будет использовать СТОЛБЕЦ().

Способ 3. Наглядное суммирование

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

1. Выберите мышью нужный столбец или строку.
2. Посмотрите на результат.

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

Как найти сумму наибольших (наименьших) значений.

Цель: сложить 3 максимальных или 3 минимальных значения.

Функция НАИБОЛЬШИЙ возвращает наибольшее значение в списке данных. Хитрость в том, что его второй аргумент показывает, какое именно значение нужно вернуть: 1 — наибольшее, 2 — второе наибольшее и т.д. И если вы укажете , то вам понадобятся три самых больших. Но не забудьте применить формулу массива и закончить с помощью CTRL + SHIFT + ENTER.

= СУММ (НАИБОЛЬШИЙ (B2: D13; ))

Аналогичная ситуация и с меньшими значениями:

= СУММ (МАЛЕНЬКИЙ (B2: D13; ))

Как вводить и скопировать формулы в Excel

Они всегда вводятся после нажатия «=». Но что, если существует много однотипных вычислений? В этом случае вы можете указать один, а затем скопировать его. Для этого введите формулу, затем «растяните» ее в нужном направлении для умножения.
Поместите указатель на ячейку, которую нужно скопировать, и переместите указатель мыши в правый нижний угол (над квадратом). Он должен иметь форму простого креста с равными сторонами.

Нажмите левую кнопку и перетащите.

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

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

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

Цена с НДС рассчитывается как цена * (1 + НДС). Вставляем последовательность в первую ячейку.

Попробуем скопировать запись.

Результат странный.

Проверим содержимое второй ячейки.

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

Нажмите F4. Адрес будет разбавлен знаком «$». Это абсолютно клеточный признак.

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

Как написать формулу в Excel

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

1. Каждый начинается со знака «=».
2. В расчетах могут участвовать значения из ячеек и функций.
3. Операторы используются как обычные математические символы для операций.
4. Когда вы вставляете запись, ячейка по умолчанию отражает результат расчета.
5. Вы можете увидеть конструкцию в строке над таблицей.

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

Итак, как создать и вставить формулу в Excel? Действуйте по следующему алгоритму:

• Установите указатель на ячейку, в которой будет производиться расчет.
  
• Нажмите знак «=» на клавиатуре «=».
  
• Щелкните мышью по первой ячейке, участвующей в вычислениях.
  
• Используйте знак оператора, например «*».
  
• Щелкните по второй ячейке.
  
• Нажмите Ввод.
  
• Наведите указатель мыши на ячейку с вычислениями, и вы увидите, как выглядит результат ваших действий.
  

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

Обозначение Значение

+ Дополнение
— Вычитание
/ Разделение
* Умножение

Если вам нужно указать число, а не адрес ячейки, введите его с клавиатуры. Чтобы указать отрицательный знак в формуле Excel, нажмите «-».

Считаем сумму в столбце в Экселе

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

Способ 1: Просмотр общей суммы

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

1. С помощью мыши выделите диапазон ячеек в столбце, сумму значений, которые вы хотите вычислить. В нашем примере это будут ячейки с 3 по 9 в столбце G.

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

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

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

Примечание: Слева от суммы указывается количество выбранных ячеек, а также среднее значение для всех указанных в них чисел.

Способ 2: Автосумма

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

Щелкните левой кнопкой мыши (ЛКМ) по пустой ячейке, расположенной ниже тех, количество которых вы хотите вычислить.

Щелкните ЛКМ кнопку «Сумма», расположенную на панели инструментов «Редактировать» (вкладка «Главная»). Как вариант, вы можете использовать комбинацию клавиш «ALT» + «=».

Убедитесь, что формула, отображаемая в выделенной ячейке и строке формул, содержит адрес первого и последнего адреса, который вы хотите добавить, и нажмите «ENTER».

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

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

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

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

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

С помощью мыши выберите диапазон ячеек, который вы хотите добавить, и нажмите «ENTER».

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

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

Способ 3: Автосумма нескольких столбцов

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

Щелкните ЛКМ по ячейке, в которой вы хотите отобразить сумму всех значений.

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

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

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

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

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

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

Способ 2. Ручной ввод формулы

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

1. Установите курсор на ячейку с будущей суммой.
2. Введите =. Любая формула в Excel начинается с равенства.
3. Щелкните левой кнопкой мыши по ячейкам, которые нужно добавить, вставив между ними + и Enter.

3 Протягивание без изменения ячеек в формуле

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

Для развертывания без изменения адреса ячейки выполните те же действия, что и для обычного извлечения или автоматического копирования. Но при вводе формулы следует исправить адреса, которые не изменятся. Для этого используются символы доллара — $. Если одна и та же ячейка используется в каждой новой записи столбца в вычислениях, значки должны быть размещены как перед номером строки, так и перед буквой, обозначающей столбец. Как в примере: $ G $ 6.

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

4 Простое копирование

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

• Запишите желаемую формулу в одну из крайних ячеек строки или столбца и нажмите Enter.
• Скопируйте значение функции: с помощью контекстного меню, значка на панели или комбинации клавиш Ctrl + C.
• Поместите курсор на противоположную сторону столбца или строки.
• Нажмите клавиши Ctrl + Shift + стрелка вверх. Результат — выбор нужного диапазона, даже если в этом разделе столбца несколько сотен или тысяч точек.
• Введите формулу. Самый простой способ сделать это — нажать комбинацию Ctrl + V.

Результатом будет такое же распределение столбцов функции, как и при использовании метода n. 2. Но, наоборот, здесь можно выбрать только часть диапазона. Или, наоборот, растяните этот участок дальше, даже если есть пустые строки. Однако во втором случае лишнее значение придется убирать вручную.

Этот небольшой трюк работает и при укладке вдоль линии. В этом случае вместо нажатия Ctrl + Shift + Стрелка вверх вам нужно будет нажать Ctrl + Shift + Стрелка влево (или вправо, если скопированная формула находится в крайнем левом столбце).

• https://ichip.ru/sovety/ekspluataciya/kak-v-eksele-protyanut-formulu-po-stroke-ili-stolbcu-5-sposobov-752522
• https://sysadmin-note.ru/article/kak-sdelat-formulu-v-excel/
• https://support.microsoft.com/ru-ru/office/%D1%81%D1%82%D0%BE%D0%BB%D0%B1%D0%B5%D1%86-%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D1%8F-%D1%81%D1%82%D0%BE%D0%BB%D0%B1%D0%B5%D1%86-44e8c754-711c-4df3-9da4-47a55042554b
• https://WindowsTips.ru/formuly-v-excel
• https://ocomp.info/kak-napisat-formulu-v-excel.html
• https://mister-office.ru/funktsii-excel/sum-function-excel.html
• https://hd01.ru/info/kak-poschitat-summu-vsego-stolbca-v-excel/

Как протянуть формулу в эксель по всему столбцу

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

Применить формулу ко всему столбцу в Excel

• Двойным щелчком по дескриптору заполнения
• Перетаскивая маркер заполнения
• Использование команды «Заполнить»
• Использование сочетаний клавиш
• Копируя ячейку

Устранение неполадок с дескриптором заполнения и проблемами пересчета

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

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

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

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

Примечание: Этот метод применит формулу к столбцу и остановится на последней заполненной ячейке в соседнем столбце.

Реплицируйте формулу вниз по столбцу, перетаскивая маркер заполнения.

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

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

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

Примените формулу ко всему столбцу с помощью команды «Заполнить».

Excel Заполнить Команда — еще один эффективный способ применить формулу к столбцу. Вот как его использовать:

1. Выберите столбец ячеек, к которому вы хотите применить формулу. Убедитесь, что первая выделенная ячейка содержит формулу, которую вы хотите скопировать.
2. На Главная в Редактирование группа, выберите Заполнять >вниз.

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

Скопируйте формулу вниз по столбцу с помощью сочетаний клавиш

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

1. Выберите столбец ячеек, к которому вы хотите применить формулу. Убедитесь, что первая выделенная ячейка содержит формулу, которую вы хотите скопировать.
2. Нажмите Ctrl + D .

Наконечник: Чтобы применить формулу справа в строке, выберите строку, начинающуюся с ячейки, содержащей формулу, затем нажмите Ctrl + R .

Применение формулы ко всему столбцу путем копирования ячейки

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

1. Выделите ячейку, содержащую формулу, и нажмите Ctrl + C чтобы скопировать его.
2. Выделите все ячейки, к которым вы хотите применить формулу, и нажмите Ctrl + V чтобы вставить скопированную формулу.

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

Устранение неполадок с дескриптором заполнения, пересчетом и форматированием

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

Не видеть дескриптор заполнения

Если маркер заполнения не отображается, проверьте, включен ли он в параметрах Excel. Идти к Файл > Опции > Фильтр, и под Параметры редактирования , обеспечить Включить маркер заполнения и перетаскивание ячейки опция отмечена.

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

Если формулы не пересчитываются автоматически после использования вышеуказанных методов, вероятно, это связано с настройками вычислений в книге. Чтобы это исправить, установите в книге автоматический расчет: перейдите к Формулы > Варианты расчетаИ выберите Автоматический. Это гарантирует обновление формул сразу после внесения изменений.

Избегайте нежелательного форматирования при копировании формул.

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

• При использовании маркера заполнения: после применения формулы Параметры автозаполнения значок появится в правом нижнем углу примененного диапазона. Нажмите на этот значок и выберите Заполнить без форматирования.
• При использовании команды «Заливка» или сочетаний клавиш: Имейте в виду, что прямого способа заполнить только формулу без ее форматирования не существует. В таких случаях рассмотрите возможность использования других методов применения вашей формулы.
• При использовании метода копирования и вставки: Параметры вставки значок появится в правом нижнем углу примененного диапазона после вставки формулы. Нажмите на него и выберите Формулы вариант . При этом будут вставлены только формулы, исключая форматирование исходной ячейки.

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

Статьи по теме

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