Функция столбец в excel

Функция СТОЛБЕЦ в Excel

Приветствую вас на сайте TopExcel!

Темой этой статьи я хочу продолжить рассмотрения функционала раздела «Ссылки и массивы», а именно как работает функция СТОЛБЕЦ в Excel. Совсем недавно я рассматривал сестру этой функции – это функция СТРОКА. Общий принцип работы для этих функций и остается одинаковый, разнятся только координаты и направления, исходя из названий формул, в нашем случае функция СТОЛБЕЦ будет возвращать тот номер столбика для указания ссылки на ячейку или же диапазон ячеек.

Рассмотрим детально синтаксис, который использует функция СТОЛБЕЦ в Excel, хотя, что там смотреть, всего один аргумент, но всё же: =СТОЛБЕЦ(ссылка), где

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

Как видите всё максимально просто, но всё же несколько нюансов как используется функция СТОЛБЕЦ в Excel стоит прояснить, во избежание возникновения ошибок. Список будет небольшим, но полезным:

  • Ссылаться на несколько областей, аргумент «ссылка» не может;
  • В случае, когда аргумент «ссылка» ссылается на диапазон ячеек, а функция СТОЛБЕЦ вExcel введена не как формула для горизонтального массива, то функция вернет номер крайнего левого столбика вашей таблицы;
  • Если же аргумент «ссылка» вами был не указан или же указана ссылка на диапазон ячеек, а функция СТОЛБЕЦ вExcel введена в таблице, как формула горизонтального массива, то наша функция также вернет номера столбиков в ссылке, и выглядеть будет это как горизонтальный массив. Внимание! Для ввода вашей формулы массива вам необходимо начиная с первой ячейки формулы, выделить нужный диапазон, в котором должна содержаться формула вашего массива. Для начала вы нажимаете клавишу F2, а после жмёте «горячее» сочетание клавиш Ctrl + Shift +Enter.
  • Если вы не указали аргумент функции «ссылка», то по умолчанию этим аргументом будет та ячейка в которой, собственно и размещается функция СТОЛБЕЦ вExcel.

Обратите внимание! В программе Excel Online возможность создания формулы массива невозможно.

Рассмотрим на примерах, как используется функция СТОЛБЕЦ в Excel: Пример №1: Как видите с примера, и как я писал ранее, функция СТОЛБЕЦ без аргумента «ссылка» возвращает нам номер того столбика, где размещается, собственно, ваша формула. То есть, так как наша формула размещена в ячейке А9, то и результатом будет возвращения значения 1 (то есть первый столбик).

Пример №2: Зададим в этом примере аргументу «ссылка», например, ссылку на город «Рим» по адресу D5, и результатом станет номер «4», так название столицы Италии размещено в четвёртом столбике.

Пример №3: Также вы можете увидеть возможность, как используется функция СТОЛБЕЦ в Excel, на примере создания пронумерованного списка в статье «5 интересных способов как происходит нумерация строк в Excel».

Как видите, функция СТОЛБЕЦ очень проста в понимании и использовании, поэтому ее использование для вас станет простым и лёгким. С другими интересными и полезными функциями вы можете познакомиться в «Справочнике функций».

Если статья вам понравилась и пригодилась, напишите комментарий, поставьте лайк или делитесь с друзьями!

До новых встреч на страницах TopExcel.ru!

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

Джеймс Болдуин

Источник: topexcel.ru

Функция СТОЛБЕЦ

Функция СТОЛБЕЦ возвращает номер столбца по заданной ссылке.

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

Если ссылка опущена, то предполагается, что это ссылка на ячейку, в которой находится сама функция СТОЛБЕЦ.

Если ссылка является интервалом ячеек, и если функция СТОЛБЕЦ введена как горизонтальный массив, то функция СТОЛБЕЦ возвращает номера столбцов в ссылке в виде горизонтального массива.

Ссылка не может ссылаться на несколько областей.

Функцию СТОЛБЕЦ удобно применять совместно с функцией ВПР для задания в последней, номера столбца возвращаемых данных. Особенно это удобно если табличка с возвращаемыми данными достаточно широкая и имеет много столбцов последовательность которых совпадает с последовательностью столбцов таблицы исходных данных. В этом случае, задав номер столбца в функции ВПР с помощью функции СТОЛБЕЦ можно просто копировать функцию ВПР по горизонтали, при этом номера столбцов будут пересчитываться автоматом. Если требуется корректировка номера столбца то к нему вполне возможно прибавить или вычесть фиксированное значение или значение, возвращаемое какой либо функцией.

Замечено еще одно интересное свойство функции СТОЛБЕЦ. Если в результате вычитания получается отрицательное или равное “0” значение, то ячейка, которая возвращает указанные значения, автоматом приобретает текстовый формат и сохраняет его при последующем изменении формул, например для получения положительного значения. Как это использовать, пока не знаю, но свойство очень интересное.

Добавление, изменение и удаление условных форматов

1. Выберите ячейку, для которой требуется добавить, изменить или удалить условное форматирование (Условный формат. Формат (например, узор ячейки или цвет шрифта), который Microsoft Excel автоматически применяет к ячейке, если выполняется указанное условие.).

2. В меню Формат выберите команду Условное форматирование.

3. Выполните одно из следующих действий.

Добавление условного формата

1. Выполните одно из следующих действий.

Читайте также:  Вычитание функция в excel

Чтобы в качестве условия форматирования использовать значения выделенных ячеек, выберите параметр значение, выберите операцию сравнения, а затем введите заданное (Константа. Постоянное (не вычисляемое) значение. Например, число 210 и текст «Квартальная премия» являются константами. Формула и результат вычисления формулы константами не являются.) значение или формулу. Перед формулой нужно поставить знак равенства (=).

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

2. Выберите команду Формат.

3. Выберите тип форматирования, который требуется применить, когда значение ячейки отвечает условию или формула возвращает значение ИСТИНА.

4. Для добавления условия выберите команду Добавить, а затем повторите шаги 1-5.

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

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

Копирование форматов в другие ячейки

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

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

Изменение или удаление условного формата

Выполните одно или несколько из указанных ниже действий.

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

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

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

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

Источник: www.myxcel.ru

Функция СТОЛБЕЦ() в EXCEL

Синтаксис функции СТОЛБЕЦ()

СТОЛБЕЦ ( ссылка )

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

В качестве ссылки указан адрес одной ячейки

Если в качестве ссылки указан адрес одной ячейки, то функция вернет номер столбца этой ячейки

=СТОЛБЕЦ(B1) вернет 2, =СТОЛБЕЦ(F2) вернет 6. Чтобы убедиться, что номер столбца F действительно 6 включите стиль ссылок R1C1 ( Кнопка Офис/ Параметры Excel/ Формулы/ Работа с формулами/ Стиль ссылок R1C1 )

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

Аргумент ссылки опущен

Если аргумент ссылки опущен, то предполагается, что это ссылка на ячейку, в которой находится сама функция СТОЛБЕЦ()

Т.е. если формула = СТОЛБЕЦ() введена в ячейку С5 , то формула вернет 3.

В качестве ссылки указан диапазон ячеек

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

=СТОЛБЕЦ(D26:G26) вернет 4, т.е. номер столбца D .

Если в Строке формул выделить формулу и нажать клавишу F9 , то мы увидим, что результатом формулы является не одно значение, а четыре <4;5;6;7>Так как в ячейке можно вывести только одно значение, то выводится, только первое, т.е. 4.

Чтобы вывести все 4 значения (см. статью Формулы массива, возвращающие несколько значений ), выделите четыре ячейки, расположенные по горизонтали ( A27:D27 ), в Строке формул введите формулу =СТОЛБЕЦ(D26:G26) и нажмите CTRL+SHIFT+ENTER . см. файл примера .

Формирование массива последовательных чисел

Записав формулу =СТОЛБЕЦ(A26:D26) мы сформируем массив последовательных чисел , который можно использовать в других задачах. Например, найдем сумму числового ряда 1+1/2+1/3+1/4+1/5+1/6+1/7 с помощью формулы =СУММПРОИЗВ(1/СТОЛБЕЦ(A1:G1))

Сумму ряда от 1 до 1/n^2, где n = 7 можно вычислить так: =СУММПРОИЗВ(1/(СТОЛБЕЦ(A1:G1)^2))

Источник: excel2.ru

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

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

Выполнение умножения в программе

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

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

Пример 1: умножаем число на число

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

  1. Встаем в любую свободную ячейку, затем:
    • ставим в ячейке (или в строке формул) знак “=”;
    • пишем первое число (множимое;
    • ставим знак умножения – *;
    • указываем второе число (множитель).
  2. После нажатия клавиши Enter получаем результат в ячейке с формулой.

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

Читайте также:  Функция целое в excel

В отличие от того, как записываются выражения со скобками на бумаге, в программе всегда ставится знак умножения (и до, и после скобок). Допустим, у нас есть вот такое выражение: 32+28(5+7) .

В ячейке нам нужно написать следующую формулу: =32+28*(5+7) .

Нажав Enter получаем ответ.

Пример 2: умножаем ячейку на число

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

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

  1. Встаем в ячейку, в которой требуется выполнить умножение (в нашем случае – это самая верхняя ячейка столбца C, не считая шапки таблицы). Затем:
    • ставим знак “=”;
    • указываем адрес ячейки – первого числа (для нас – это B2). Сделать это можно путем ручного ввода координат с клавиатуры. Также можно просто кликнуть по нужной ячейке в самой таблице;
    • пишем знак умножения *;
    • указываем число 5.
  2. Нажимаем Enter, чтобы получить результат.

Пример 3: умножаем ячейку на ячейку

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

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

Формулу пишем в ячейке D2, и она имеет следующий конечный вид: =B2*С2 .

Жмем клавишу Enter и получаем требуемый результат.

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

Пример 4: умножаем столбец на число

Этот пример можно считать продолжением Примера 2. У нас уже есть результат произведения ячейки и числа для самого верхнего элемента в колонке C. Остается только растянуть формулу на другие строки.

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

Отпустив кнопку мыши в колонке C получаем результаты умножения значений из столбца B на число 5.

Пример 5: умножаем столбец на столбец

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

Все что нам остается, это растянуть формулу с помощью маркера заполнения на оставшиеся строки.

Получаем в колонке D результат умножения столбца B на столбец C.

Пример 6: умножаем столбец на ячейку

Предположим, нам нужно посчитать скидку по перечню товаров, представленному в одном столбце (B). Размер скидки указан в отдельной ячейке (E2).

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

  1. Для начала пишем в самой верхней ячейке столбца C (не считая шапки) формулу умножения ячейки B2 на E2.
  2. Не спешим нажимать клавишу Enter. Дело в том, что сейчас в формуле используются относительные ссылки, а это значит, что при ее копировании в другие ячейки произойдет смещение адресов (т.е. B3 будет умножаться на E3). Нам же нужно зафиксировать адрес E2, т.е. сделать ссылку на эту ячейку абсолютной. Для этого находясь курсором до, после, или внутри адреса (между буквой и цифрой) нажимаем клавишу F4.
  3. Перед обозначением столбца и номером строки появятся символы “$”. Теперь можно нажимать Enter.
  4. Растягиваем формулу на другие ячейки с помощью маркера заполнения.
  5. Как мы можем убедиться на примере формулы в ячейке C9, в ней по-прежнему участвует ячейка E2, а это значит, что мы все сделали правильно.

Оператор ПРОИЗВЕД

В Эксель, помимо использования формулы, также предусмотрена специальная функция для выполнения умножения под названием – ПРОИЗВЕД. Работа с функцией предполагает следующие шаги:

  1. Выбираем ячейку, в которой планируем выполнить расчеты и нажимаем кнопку “Вставить функцию” рядом со строкой формул.
  2. В открывшемся окне мастера функций выбираем категорию “Математические”, находим оператор “ПРОИЗВЕД”, отмечаем его и жмем OK.
  3. Откроется окно с аргументами функции (максимум, до 255), в которых могут быть указаны:
    • обычные числа;
    • ссылки на ячейки (относительные или абсолютные), которые пишутся либо вручную, либо просто выбираются путем клика на листе;
    • комбинированные аргументы, т.е. и конкретные числа, и ссылки на ячейки.
  4. Когда все аргументы заполнены, жмем кнопку OK и получаем результат в ячейке с функцией (в нашем случае – произведение ячеек).

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

Заключение

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

Источник: microexcel.ru

Функция СТОЛБЕЦ в Excel и полезные примеры ее использования

Функция СТОЛБЕЦ в Excel возвращает номер столбца на листе по заданным условиям. Синтаксис элементарный: всего один аргумент. Но с ее помощью можно эффективно решать разнообразные задачи.

Читайте также:  Функция или в excel если или

Описание и синтаксис функции

Функция с параметром: = СТОЛБЕЦ (С3) возвращает значение 3, т.к. (C) является третьим по счету.

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

Аргумент – ссылка на ячейку:

Функция выдала номер колонки для этой ячейки.

Функция вернула номер столбца, в котором находится.

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

Функция вернула номер столбца, в котором расположен диапазон.

Аргумент – горизонтальный диапазон ячеек:

Функция СТОЛБЕЦ вернула номер крайнего левого столбца (А) в указанном диапазоне. Если выделить формулу в строке формул и нажать кнопку F9, то программа выдаст все номера столбцов заданного диапазона.

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

Чтобы на листе появились номера всех столбцов диапазона, который является аргументом функции СТОЛБЕЦ, нужно использовать формулу массива. Выделяем такое количество ячеек, сколько элементов входит в горизонтальный диапазон. Вводим формулу и нажимаем сочетание кнопок Ctrl + Shift + Enter.

Аргумент – ссылка на горизонтальный массив:

Формула вернула номера столбцов в виде горизонтального массива.

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

Полезные примеры функции СТОЛБЕЦ в Excel

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

Например, рассчитаем значение выражения 1 + ½ + 1/3. Используем формулу: =СУММПРОИЗВ(1/СТОЛБЕЦ(A2:C2)).

Выполним более сложные манипуляции с числовым рядом: найдем сумму значений от 1 до 1/n^3, где n = 6. Формула расчета: =СУММПРОИЗВ(1/СТОЛБЕЦ(A9:F9)^3).

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

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

Аргументы функции ВПР: искомое значение, массив данных для анализа, номер столбца, интервальный просмотр (точный или приблизительный поиск). Сам номер можно задать с помощью такой формулы: =ВПР(8;A1:C10;СТОЛБЕЦ(C1);ИСТИНА).

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

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

Функция СТОЛБЕЦ должна вычесть 1 из номера колонки C. Поэтому функция ВПР возвращает значение не из третьего, а из второго столбца девятой строки.

Теперь проиллюстрируем, как работает многоразовое копирование без необходимости в ручной правке. Сначала в формуле закрепим ссылки на таблицу (кнопка F4). Скопируем формулу ВПР поперек столбцов – номер меняется автоматически (функция СТОЛБЕЦ сдвигается вместе с другими ссылками).

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

Источник: exceltable.com

30 функций Excel за 30 дней: ЧИСЛСТОЛБ (COLUMNS)

Вчера в марафоне 30 функций Excel за 30 дней мы получили детальную информацию о ячейке при помощи функции CELL (ЯЧЕЙКА) и выяснили, что в некоторых случаях она весьма полезна, например, чтобы получить имя листа.

В 12-й день марафона мы будем заниматься изучением функции COLUMNS (ЧИСЛСТОЛБ). Будет ли эта функция так же полезна? Или она всего лишь ещё одна ленивая функция, вроде AREAS (ОБЛАСТИ)? Как и обещано, она подсчитывает количество столбцов, и ещё она отличный командный игрок, но никакой экзотики или суперсилы Вы здесь не найдёте.

Итак, посмотрим подробнее информацию по функции COLUMNS (ЧИСЛСТОЛБ), а также примеры ее использования. Если у Вас имеется дополнительная информация или свои примеры, пожалуйста, делитесь ими в комментариях.

Функция 12: COLUMNS (ЧИСЛСТОЛБ)

Функция COLUMNS (ЧИСЛСТОЛБ) возвращает количество столбцов в массиве или ссылке.

Как можно использовать функцию COLUMNS (ЧИСЛСТОЛБ)?

Функция COLUMNS (ЧИСЛСТОЛБ) может показать размер таблицы или именованного диапазона:

  • Подсчитывает столбцы в таблице Excel.
  • Суммирует последний столбец в именованном диапазоне.

Синтаксис COLUMNS (ЧИСЛСТОЛБ)

Функция COLUMNS (ЧИСЛСТОЛБ) имеет вот такой синтаксис:

COLUMNS(array)
ЧИСЛСТОЛБ(массив)

  • array (массив) – это массив, формула массива или ссылка на диапазон.

Ловушки COLUMNS (ЧИСЛСТОЛБ)

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

Пример 1: Считаем столбцы в таблице Excel

В Excel 2007 и Excel 2010 Вы можете создать таблицу, используя команду Table (Таблица), и сослаться на её имя в формуле. В этом примере показана таблица с названием RegionSales.

Функция COLUMNS (ЧИСЛСТОЛБ) считает количество столбцов в этой таблице.

Пример 2: Суммируем последний столбец в именованном диапазоне

Если использовать функцию COLUMNS (ЧИСЛСТОЛБ) в сочетании с SUM (СУММ) и INDEX (ИНДЕКС), можно получить итоговую сумму для последнего столбца в именованном диапазоне. В нашем примере это диапазон с именем MyRange.

Следующая формула суммирует последний столбец в именованном диапазоне:

Источник: office-guru.ru