Что такое столбец в Excel

Обновлено: 07.07.2024

Функция СТОЛБЦ в Excel — это функция поиска/ссылки. Эта функция полезна для поиска и предоставления номера столбца данной ссылки на ячейку. Например, формула =СТОЛБОН(A10) возвращает 1, так как столбец A является первым столбцом.

Формула

=СТОЛБЦ([ссылка])

Функция СТОЛБЦ использует только один аргумент — ссылку — необязательный аргумент. Это ячейка или диапазон ячеек, для которых нам нужен номер столбца. Функция даст нам числовое значение.

Несколько моментов, которые следует помнить для аргумента ссылки:

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

Как использовать функцию СТОЛБЦ в Excel?

Это встроенная функция, которую можно использовать как функцию рабочего листа в Excel. Чтобы понять, как использовать функцию СТОЛБЦ в Excel, рассмотрим несколько примеров:

Пример 1

Предположим, что нам даны следующие ссылки:

В первой ссылке, которую мы предоставили, мы не дали ссылку, поэтому она дала нам результат 4 с функцией COLUMN в ячейке C5, как показано на снимке экрана ниже:

Когда мы использовали ссылку B6, она вернула результат 3, поскольку ссылка на столбец была B.

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

Пример 2

Предположим, расходы на цветы оплачиваются каждый третий месяц. Стоимость цветка 100 долларов. Если мы хотим генерировать фиксированное значение каждый третий месяц, мы можем использовать формулу, основанную на функции ОСТАТ.

Используется формула =IF(MOD(COLUMN(B8)-1,3)=0,$A$2,0), как показано ниже:

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

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

Здесь используются две формулы: MOD и COLUMN. MOD является основной используемой функцией. Функция полезна для формул, требующих выполнения определенного действия каждый n-й раз. Здесь, в нашем примере, число было создано с помощью функции COLUMN, которая вернула номер столбца ячейки B8, число 2 минус 1, которое предоставляется как «смещение». Почему мы использовали смещение? Это потому, что мы хотели убедиться, что Excel начинает считать с 1, независимо от фактического номера столбца.

Дивизор фиксируется как 3 (расходы на цветы) или 2, так как мы хотим делать что-то каждые 3 или 2 месяца. При проверке на нулевой остаток выражение вернет TRUE на 3-м, 6-м, 9-м и 12-м месяцах или на 2-м, 4-м, 6-м, 8-м, 10-м и 12-м месяцах.< /p>

Использование формулы и работа с датой

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

Пример 3

Если мы хотим, мы можем использовать эту функцию столбца, чтобы получить сумму каждого n-го столбца. Предположим, нам даны следующие данные:

Поскольку мы хотим суммировать каждый n-й столбец, мы использовали формулу, объединяющую три функции Excel: СУММПРОИЗВ, МОД и СТОЛБЦ.

Используется следующая формула:

Давайте посмотрим, как работает функция СТОЛБЦ в Excel.

В приведенной выше формуле столбец G представляет собой значение n в каждой строке. Использование функции ОСТАТ вернет остаток для каждого номера столбца после деления его на N. Так, например, когда N = 3, ОСТАТ вернет что-то вроде этого:

Итак, формула использует =0, чтобы получить ИСТИНА, когда остаток равен нулю, и ЛОЖЬ, если это не так. Обратите внимание, что мы использовали двойное отрицание (-), чтобы получить ИСТИНА или ЛОЖЬ для единиц и нулей. Он дает такой массив:

Где единицы теперь обозначают «энные значения». Он входит в СУММПРОИЗВ как массив1 вместе с B5:F5 как массив2. Теперь СУММПРОИЗВ умножает, а затем складывает произведения массивов.

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

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по важным функциям Excel! Потратив время на изучение и освоение этих функций, вы значительно ускорите свой финансовый анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

Функции Excel для финансов Excel для финансов В этом руководстве по Excel для финансов представлены 10 основных формул и функций, которые необходимо знать, чтобы стать отличным финансовым аналитиком в Excel.
Усовершенствованные формулы Excel, которые необходимо знать Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel очень важны для понимания и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
Сочетания клавиш Excel для ПК и Mac Ярлыки Excel для ПК Mac Сочетания клавиш Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel - формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

поиск меню

Урок 6. Изменение столбцов, строк и ячеек

Введение

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

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

Чтобы изменить ширину столбца:

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

Для автоподбора ширины столбца:

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

Вы также можете автоматически подобрать ширину для нескольких столбцов одновременно. Просто выберите столбцы, для которых требуется автоподбор, затем выберите команду «Автоподбор ширины столбца» в раскрывающемся меню «Формат» на вкладке «Главная». Этот метод также можно использовать для высоты строки.

< бр />

Чтобы изменить высоту строки:

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

Чтобы изменить все строки или столбцы:

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

< /p>

< бр />

Вставка, удаление, перемещение и скрытие

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

Чтобы вставить строки:

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

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

Чтобы вставить столбцы:

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

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

Чтобы удалить строку или столбец:

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

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

Чтобы переместить строку или столбец:

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

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

Чтобы скрыть и показать строку или столбец:

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

Обтекание текстом и объединение ячеек

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

Чтобы перенести текст в ячейки:

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

< /p>

Нажмите команду "Перенести текст" еще раз, чтобы развернуть текст.

Чтобы объединить ячейки с помощью команды "Объединить и центрировать":

Выберите диапазон ячеек, который хотите объединить. В нашем примере мы выберем A1:F1.
Нажмите команду "Объединить и центрировать" на вкладке "Главная". В нашем примере мы выберем диапазон ячеек A1:F1.

Чтобы получить доступ к дополнительным параметрам слияния:

Если щелкнуть стрелку раскрывающегося списка рядом с командой "Объединить и центрировать" на вкладке "Главная", появится раскрывающееся меню "Объединить".

Здесь вы можете выбрать:

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

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

Выравнивание по центру

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

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

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

Строки на листе Excel

Как и строка в любой таблице, строка в листе Excel расположена горизонтально. Строки на листе Excel обозначаются номерами строк. Номера строк в Excel 2019 начинаются с 1 и заканчиваются на 1048576.

Столбцы на листе Excel

Подобно столбцу в любой таблице, столбец на листе Excel расположен вертикально. Столбцы на листе Excel обозначаются буквами столбцов. Буквы столбцов в Excel 2019 начинаются с «A» и заканчиваются на «XFD».

Ячейки на листе Excel

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

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

Ячейка, показанная ниже, представляет собой пересечение столбца "C" и строки "5". Поэтому адрес ячейки, показанный ниже, равен «C5».

Толстая рамка вокруг выбранной ячейки на изображении выше указывает на то, что это активная ячейка на листе Excel. Активная ячейка — это место, на котором находится фокус, и куда будут вводиться данные при нажатии клавиши на клавиатуре. На изображении ниже ячейка Actice показана как C5, выделенная толстой рамкой. Адрес активной ячейки отображается в поле «Имя ячейки» в строке формул Excel.

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

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

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

Это руководство охватывает:

Выберите и получите СУММУ столбца в строке состояния

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

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

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

Как только вы выберете весь столбец, вы заметите, что в строке состояния отображается СУММА столбца.

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

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

Получить СУММУ столбца с помощью автосуммы (с помощью одного щелчка/ярлыка)

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

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

Ниже приведены шаги для получения суммы столбца:

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

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

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

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

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

Сочетание клавиш для автосуммирования

Хотя использование параметра "Автосумма" на вкладке "Формула" выполняется достаточно быстро, вы можете еще быстрее получить СУММ с помощью сочетания клавиш.

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

Использование функции СУММ для ручного вычисления суммы

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

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

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

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

Ниже приведены шаги по использованию функции СУММ вручную:

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

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

Ниже приведен пример, в котором я назвал диапазон – Продажи. Приведенная ниже формула также дает мне сумму столбца продаж:

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

Если вы хотите, чтобы скрытые ячейки не включались при суммировании столбца, вам нужно использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ или АГРЕГАТ (описанные далее в этом руководстве).

Суммировать только видимые ячейки в столбце

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

Ниже приведен пример того, что может пойти не так:

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

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

Если вы хотите получить только сумму видимых ячеек, вы не можете использовать функцию СУММ. В этом случае вам нужно использовать функцию АГРЕГАТ или ПРОМЕЖУТОЧНЫЙ ИТОГ.

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

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

Функция Aggregate принимает следующие аргументы:

function_num: это число, которое сообщает функции АГРЕГАТ вычисление, которое необходимо выполнить. В этом примере я использовал 9, так как мне нужна сумма.
Параметры: в этом аргументе вы можете указать, что вы хотите игнорировать при выполнении вычислений. В этом примере я использовал 3, что «игнорирует скрытую строку, значения ошибок, вложенные функции ПРОМЕЖУТОЧНЫЙ ИТОГ и АГРЕГАТ». Короче говоря, для вычислений используются только видимые ячейки.
массив: это диапазон ячеек, для которых вы хотите получить значение. В нашем примере это B2:B6 (где также есть несколько скрытых/отфильтрованных строк)

Если вы используете Excel 2007 или более ранние версии, вы можете использовать следующую формулу Excel:

Ниже приведена формула, в которой я показываю, как суммировать столбец при наличии отфильтрованных ячеек (с помощью функции ПРОМЕЖУТОЧНЫЕ ИТОГО)

<Р>