Как сделать формулу в эксель по горизонтали

Обновлено: 07.07.2024

Как сделать подстановку значений в строку "1" из столбца "A".
Пример: Имеем заполненные ячейки A1-A1000
Необходимо с ячейки B1 по ZZZ1 (1000-ая ячейка к примеру) подставить значения из А2 и т.д. до А1000
Ручками сделал "=" первые 5 значений и понял что я умру вбивая 1000 раз "=ссылка на Ax"

Попробую получше разъяснить
Начиная с ячейки А2 и до A1000 идут фамилии, так вот их же надо отразить начиная с B1 и до ZZZ1 (я не знаю какие буквы у 1000ого столбца)
Спасибо!

Попробую получше разъяснить
Начиная с ячейки А2 и до A1000 идут фамилии, так вот их же надо отразить начиная с B1 и до ZZZ1 (я не знаю какие буквы у 1000ого столбца)
Спасибо! Алексей К

Включите режим ссылок R1C1, тогда столбцы тоже числами будут нумероваться Автор - Pelena
Дата добавления - 03.09.2013 в 19:25

Я поняла (вот что значит без примера), Вам нужно транспонировать столбец в строку: выделяете диапазон -- копировать -- становитесь в В1 -- специальная вставка -- транспонировать -- ок

Спасибо, а ларчик просто открывался а есть вариант заполнения "горизонтали" через "ссылку"? Ну например, Если будут изменения в вертикали, то автоматически будут поменяться и в горизонтали, в противном случае придется периодически копировать весь столбец и заново вставлять через специальную вставку

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

Суммирование значений в строке

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

Способ 1: арифметическая формула

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

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

В итоге в конкретно нашем случае получается следующая формула:

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

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

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

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

Способ 3: функция СУММ

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

Оператор СУММ принадлежит к группе математических функций Эксель. Его задачей является суммирование чисел. Синтаксис этой функции имеет такой вид:

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

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

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

Способ 4: массовое суммирование значений в строках

Но что делать, если нужно просуммировать не одну и не две строчки, а, скажем 10, 100 или даже 1000? Неужели для каждой строки требуется в отдельности применять вышеописанные действия? Как оказывается, совсем не обязательно. Для этого нужно просто скопировать формулу суммирования в другие ячейки, в которые вы планируете выводить сумму по остальным строчкам. Сделать это можно при помощи инструмента, который носит наименование маркера заполнения.

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

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

Наиболее распространенная функция, которую используют большинство пользователей Microsoft Excel, это функция СУММ. Неудивительно, что Microsoft Excel решили добавить специальную кнопку на ленту Excel, которая автоматически вставляет функцию СУММ.

По сути, Автосумма в Excel автоматически вводит формулу для суммирования чисел на вашем листе. В данной статье мы рассмотрим следующую информацию:

Где находится Автосумма в Excel

Кнопку Автосумма можно найти в двух местах на ленте Excel.

Автосумма в Excel – Кнопка Автосуммы в Excel

Автосумма в Excel – Кнопка Автосумма во вкладке Формулы

Теперь где Автосумма в Excel вы знаете.

Как сделать автосумму в Excel

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

Чтобы использовать Автосумму в Excel, выполните следующие 3 простых действия:

Выберите ячейку рядом с цифрами, которые вы хотите просуммировать:

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

Автосумма в Excel – Выбор ячейки, после который вы хотите посчитать сумму

В выбранной ячейке появляется формула суммы, а диапазон суммируемых ячеек выделяется (B2:B6 в этом примере):

Автосумма в Excel – Кликните на кнопке Автосумма на вкладке Главная и Формулы (в данном случае на вкладке Формула)

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

Нажмите клавишу Enter , чтобы завершить формулу.

Теперь вы можете увидеть вычисленную сумму в ячейке и формулу СУММ в строке формул:

Автосумма в Excel – Кликните клавишу Enter, чтобы завершить формулу СУММ

Разберем причины, по которым автосумма в Excel выдает 0.

Автосумма в Excel не считает

Бывают случаи, когда Автосумма в Excel не работает, то есть автосумма в Excel выдает 0. Самая распространенная причина почему не работает Автосумма в Excel - это числа, отформатированные как текст. На первый взгляд, эти значения могут выглядеть как обычные числа, но Excel рассматривает их как текстовые строки и не учитывает при расчетах.

Автосумма в Excel – Преобразование числа в текстовом формате, в числовой

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

Автосумма в Excel горячие клавиши

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

Автосумма в Excel – Автосумма в Excel горячие клавиши

При удерживании клавиши Alt , и нажатии знака равенства (=), в выбранной ячейке (ячейках) вставляется формула СУММ точно так же, как при нажатии кнопки Автосумма на ленте, и затем вы нажимаете клавишу Enter для ввода формулы.

Как использовать Автосумму с другими функциями

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

СРЕДНЕЕ - возвращает среднее (среднее арифметическое) чисел.

СЧЕТЧИК - подсчитывает количество ячеек с числами.

МАКСИМУМ - возвращает наибольшее значение.

МИНИМУМ - возвращает наименьшее значение.

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

Например, именно так вы можете получить наибольшее число в столбце B:

Автосумма в Excel – Использование кнопки Автосумма для вставки других формул (в данном случае Максимум)

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

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

Если ваши данные организованы в таблице Excel (что можно легко сделать, нажав сочетание клавиш Ctrl+T), и вы отфильтровали свои данные, применив один из параметров фильтрации, нажатие кнопки Автосуммы вставляет формулу ПРОМЕЖУТОЧНЫЕ.ИТОГИ, а не СУММ, как показано на следующем изображении:

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

В исходной таблице были скрыты 3, 6, 9 и 12 строка. В данном случае, использование Автосуммы и функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ, были просуммированы только видимые ячейки.

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

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

Если вы хотите суммировать значения в нескольких столбцах или строках, выберите все ячейки, в которые вы хотите вставить формулу СУММ, а затем нажмите кнопку Автосумма на ленте или нажмите сочетание горячих клавиш Автосуммы в Excel.

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

Автосумма в Excel – Автосумма нескольких столбцов одновременно

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

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

Автосумма в Excel – Сумма выбранных ячеек вертикально по столбцам

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

Автосумма в Excel – Сумма выбранных ячеек горизонтально по строкам

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

Автосумма в Excel – Суммирование выбранных ячеек столбцам и строкам

Среди всех арифметических действий можно выделить четыре основных: сложение, умножение, деление и вычитание. О последнем и пойдет в речь в данной статье. Давайте рассмотрим, какими методами можно выполнять данное действие в Excel.

Процедура вычитания

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

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

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

Пример 1: разность конкретных чисел

Допустим, нам нужно найти разность между конкретными числами: 396 и 264. Выполнить вычитание можно с помощью простой формулы:

Примечание: безусловно, программа Эксель умеет работать и с отрицательными числами, поэтому, вычитание можно выполнить в обратном порядке. В этом случае формула выглядит так: =264-365 .

Пример 2: вычитание числа из ячейки

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

Как и в первом методе, сначала выбираем свободную ячейку, куда хотим вывести результат вычисления. В ней:
- пишем знак “=”.
- указываем адрес ячейки, в которой находится уменьшаемое. Сделать это можно вручную, прописав координаты с помощью клавиш на клавиатуре. Либо можно выбрать нужную ячейку, кликну по ней левой кнопкой мыши.
- добавляем в формулу знак вычитания (“-“).
- пишем вычитаемое (если вычитаемых несколько, добавляем их через символ “-“).
После нажатия клавиши Enter, получаем результат в выбранной ячейке.

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

Пример 3: разность между числами в ячейках

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

Пример 4: Вычитание одного столбца из другого

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

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

Пример 5: Вычитание конкретного числа из столбца

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

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

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

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

Алгоритм действий в данном случае следующий:

Переходим в самую верхнюю ячейку столбца для вычислений. Пишем в нем привычную формулу вычитания между двумя ячейками.
Когда формула готова, не спешим нажимать клавишу Enter. Чтобы при растягивании формулы зафиксировать адрес ячейки с вычитаемым, необходимо напротив ее координат вставить символы “$” (другими словами, сделать адрес ячейки абсолютными, так как по умолчанию ссылки в программе относительные). Сделать это можно вручную, прописав в формуле нужные символы, или же, при ее редактировании переместить курсор на адрес ячейки с вычитаемым и один раз нажать клавишу F4. В итоге формула (в нашем случае) должна выглядеть так:
После того, как формула полностью готова, жмем Enter для получения результата.
С помощью маркера заполнения производим аналогичные вычисления в остальных ячейках столбца.

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

Заключение

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

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

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

Отразить данные с помощью SORT и вспомогательного столбца

Один из самых простых способов изменить порядок данных в Excel — использовать вспомогательный столбец, а затем использовать этот вспомогательный столбец для сортировки данных.

Переверните данные по вертикали (обратный порядок вверх ногами)

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

Ниже приведены шаги, чтобы перевернуть данные по вертикали:

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

После этого можете удалить вспомогательный столбец.

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

Отразить данные по горизонтали

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

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

Ниже приведены шаги для этого:

Вышеупомянутые шаги перевернут всю таблицу по горизонтали.

После этого вы можете удалить вспомогательную строку.

Отразить данные с помощью формул

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

В этом разделе я покажу вам, как это сделать, используя формулу СОРТБИ (если вы используете Microsoft 365) или формулу ИНДЕКС (если вы не используете Microsoft 365).

Использование функции СОРТИРОВКИ (доступно в Microsoft 365)

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

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

Теперь используйте следующую формулу под ячейкой в крайнем левом заголовке:

Приведенная выше формула сортирует данные и использует результат функции СТРОКА в качестве основы для их сортировки.

Функция ROW (Строка) в этом случае вернет массив чисел, который представляет номера строк между указанным диапазоном (который в этом примере будет серией чисел, таких как 2, 3, 4 и т. Д.).

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

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

Использование функции ИНДЕКС

Если у вас нет доступа к функции SORTBY, не беспокойтесь — вы можете использовать удивительную функцию INDEX.

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

Ниже приведена формула для этого:

Как работает эта формула?

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

Настоящая магия происходит во втором аргументе, где я использовал функцию ROWS (Строки).

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

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

По мере того, как мы спускаемся по строкам, результат функции ROWS будет уменьшаться на 1, с 11 до 10, до 9 и так далее.

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

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

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

Ниже приведена формула, которая сделает это за вас:

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

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

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

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

Перевернуть данные с помощью VBA

Если перелистывание данных в Excel — это то, что вам нужно делать довольно часто, вы также можете попробовать метод VBA.

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

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

Ниже приведен код VBA, который переворачивает выбранные данные на листе по вертикали.

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

Как работает этот код?

Приведенный выше код сначала подсчитывает общее количество строк в наборе данных и присваивает его переменной EndNum.

Затем он использует цикл Do While, в котором происходит сортировка данных.

Он сортирует эти данные, беря первую и последнюю строки и меняя их местами. Затем он переходит во вторую строку во второй последней строке и меняет их местами. Затем он переходит в третий ряд в третьем последнем ряду и так далее.

Цикл заканчивается, когда вся сортировка завершена.

Он также использует свойство Application.ScreenUpdating и устанавливает для него значение FALSE во время выполнения кода, а затем возвращает его в значение TRUE, когда код завершает выполнение.

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

Как пользоваться кодом?

Выполните следующие шаги, чтобы скопировать и вставить этот код в редактор VB:

Чтобы запустить код макроса VBA, сначала выберите набор данных, который вы хотите перевернуть (за исключением заголовков).

Выбрав данные, перейдите в редактор VB и нажмите зеленую кнопку воспроизведения на панели инструментов или выберите любую строку в коде и затем нажмите клавишу F5.

Итак, вот некоторые из методов, которые вы можете использовать для переворота данных в Excel (т. Е. Обратного порядка набора данных).

Все методы, которые я рассмотрел в этом руководстве (формулы, функция СОРТИРОВКИ и VBA), можно использовать для зеркального отражения данных по вертикали и горизонтали (вам придется соответствующим образом настроить формулу и код VBA для горизонтального переворота данных).

Читайте также: