Microsoft Excel:

  Таблицы и VBA. Справочник.
  Вопросы и Ответы. Советы. Примеры.
Меню FAQ | Функции


Rambler's Top100


Counter CO.KZ
  1. Как вставить функцию в ячейку ? 2004
  2. Как ввести функцию без использования знака равно (=) ? 29.04.2007
  3. Как воспользоваться собственной функцией ?
    Что такое пользовательская (собственная) функция и зачем она нужна ?
    2004
  4. Как использовать все возможности функции СУММ ? 2004
  5. Как суммировать диапазон ячеек, который находится в нескольких листах ? 2004
  6. Как суммировать все ячейки в столбце или строке ? 2004
  7. Как суммировать данные столбца, расположив функцию СУММ в этом же столбце ? 22.12.2005
  8. Как суммировать только отфильтрованные данные ? 04.10.2006
  9. Как суммировать только данные скрытых ячеек ? 01.06.2007
  10. Как суммировать каждую n ячейку в столбце или строке ? 15.02.2007
  11. Как суммировать числовые значения, хранящиеся в виде текста ? 12.03.2007
  12. Как суммировать =B2+C2+F2 только числовые значения ячеек, т.е. без учёта текстовых значений, которые могут содержать эти ячейки ? 15.04.2007
  13. Как обойти ограничение на 30 аргументов в стандартных функциях рабочего листа, например, СУММ, МИН, МАКС, СЧЁТ, СЧЁТЗ и т.д. ? 13.03.2007
  14. Как обойти ограничение на одно условие в функциях СУММЕСЛИ, СЧЁТЕСЛИ ? 21.03.2011
  15. Как можно быстро, но подробно узнать почти о всех функциях в MS Excel ? 2004

Как вставить функцию в ячейку ?
  • Ответ :
    Выберите ячейку в которой Вы хотите разместить функцию.
    В меню Вставка выберите команду Функция, или нажмите клавиши SHIFT + F3.
    В списке Категория выберите нужный вариант.
    В списке Функция выберите нужную функцию и нажмите кнопку ОК.
    Введите в текстовые поля адреса ячеек (чтобы не набирать вручную, щелкните кнопку справа от текстового поля и выберите диапазон с помощью мышки)
    Закончив заполнение полей нажмите кнопку ОК.
    Обратите внимание на то, что некоторые функции могут отсутствовать в списке и будут недоступны без установки соответствующих надстроек (.xla)

  • Как ввести функцию без использования знака равно (=) ?
  • Ответ :
    Функцию можно ввести использовав символ @, например :
    @СУММ(A2:A100)
  • Как воспользоваться собственной функцией ?
  • Ответ :
    Выполните те же действия [Как вставить функцию в ячейку], но в списке Категория выберите вариант Определенные пользователем.

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

  • Пользовательская (собственная) функция, это функция написанная пользователем на языке VBA.
  • Собственная функция нужна, когда использование стандартных функций невозможно или не приносит нужного результата.
  • Пример такой функции Вы можете найти на этом сайте : [Функция ДатаПолная]
  • Для использования собственной функции во всех файлах Excel, её необходимо сохранить в личной книге макросов, как правило, это файл PERSONAL.XLS
  • Существуют и другие способы, при которых пользовательская функция будет доступна во всех файлах Excel, например использование надстройки .xla
  • Как использовать все возможности функции СУММ ?
  • Ответ :
    Пример I. При вставке функции СУММ в ячейку результат будет выглядеть следующим образом :

    =СУММ(A1;A2;A3;A4;A5)


    Пример II. При запуске функции СУММ (Автосумма) с помощью кнопки Σ на стандартной панели, результат будет выглядеть так :

    =СУММ(A1:A5)


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

    =СУММ(A1:A10;B1;D1:D10;F1)


    Пример III(b). При использовании функции СУММ допускается использование констант.

    =СУММ(A1:A10;B1;D1:D10;F1;17)


    Предполагается, что :
    В первом примере мы суммируем данные расположенные в ячейках с A1 по A5, т.е. суммирует каждую ячейку "последовательно"
    Во втором примере те же данные, но суммируется сразу весь диапазон ячеек A1:A5
    В третьем примере мы суммируем диапазоны ячеек A1:A10, D1:D10 и отдельные ячейки B1 и F1
    В дополнительном примере III(b) мы суммируем диапазоны ячеек A1:A10, D1:D10 и отдельные ячейки B1 и F1, а также константу 17

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

    =СУММ(ЦЕНА;НДС;ТРАНСПОРТ;НАЛОГ;ВЗЯТКА)


    Пример V.

    =СУММ(ЦЕНА_ТОВАРА)


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

    Bonus. Как использовать функцию СУММ для вычитания ? NEW

    =СУММ(A1;-A2;-A3;-A4;A5)

    В дополнительном примере мы суммируем данные расположенные в ячейках A1 и A5, и вычитаем ячейки A2, A3, A4
    Примечание : При использовании функции СУММ можно ссылаться на другой рабочий лист, а также на другой файл (.xls). Допускаются ссылки на несколько разных листов, книг в одной ячейке.

    Другие возможности использования функции СУММ
    [Как суммировать все ячейки в столбце или строке]
    [Как суммировать диапазон ячеек в нескольких листах]
  • Ответ :
    Подобная формула будет выглядеть следующим образом :

    =СУММ(Лист1:Лист3!A1:A5)


    Предполагается, что :
    Исходные данные расположены на листах 1, 2, 3 и Вы хотите просуммировать во всех трёх листах диапазон A1:A5
    [Более подробно о функции СУММ]
  • Ответ :
    Подобная формула будет выглядеть следующим образом :

    =СУММ(A:A)
    =СУММ(1:1)


    Предполагается, что :
    Вы хотите просуммировать диапазон ячеек в столбце A или строке 1

    =СУММ(A:C)
    =СУММ(1:3)


    Предполагается, что :
    Вы хотите просуммировать диапазон ячеек в столбцах A:C, т.е. столбцы A, B, C или строках 1, 2, 3
    [Более подробно о функции СУММ]
    Ячейка с подобной формулой не должна быть размещена в указываемом диапазоне.
    Примечание : Можно ссылаться на другой рабочий лист, а также на другой файл (.xls). Допускаются ссылки на несколько разных листов, книг в одной ячейке.
  • Как суммировать данные столбца, расположив функцию СУММ в этом же столбце ?
  • Ответ :
    Подобные формулы будут выглядеть следующим образом :

    =СУММ(B1:B4;B6:B65536)
    =СУММ(B:B (1:4;6:65536))
    =СУММ((1:4;6:65536) B:B)


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

    Однако, если Вы выделите ячейку A5 или строку 5 и добавите ячейки/строки, то в результате получите функцию, которая будет возвращать некорректный результат.

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

    =СУММ(СМЕЩ(B1;;;СТРОКА()-1);B6:B65536)
    =СУММ(СМЕЩ(B1:B5;;;СТРОКА()-1);B6:B65536)
    =СУММ(СМЕЩ(B1;;;ЧСТРОК(B1:B5)-1);B6:B65536)
    =СУММ(СМЕЩ(B1:B5;;;ЧСТРОК(B1:B5)-1);B6:B65536)
    =СУММ(B1:ИНДЕКС(B1:B5;СТРОКА()-1;0);B6:B65536)
    =СУММ(B1:ДВССЫЛ("B"&СТРОКА()-1);B6:B65536)
    =СУММ(B1:ДВССЫЛ(АДРЕС(СТРОКА()-1;СТОЛБЕЦ()));B6:B65536)

    =СУММ(B:B (СМЕЩ(1:1;;;СТРОКА()-1);6:65536))
    =СУММ(B:B (СМЕЩ(1:5;;;СТРОКА()-1);6:65536))
    =СУММ(B:B (СМЕЩ(1:1;;;ЧСТРОК(1:5)-1);6:65536))
    =СУММ(B:B (ДВССЫЛ("1:"&СТРОКА()-1);6:65536))
    =СУММ(B:B (ДВССЫЛ("1:"&ЧСТРОК(1:5)-1);6:65536))

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

    Пример :
    =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A100)


    Предполагается, что :
    Мы суммируем данные видимых ячеек диапазона A2:A100

    Совет : Для быстрого суммирования можно воспользоваться кнопкой Σ (Автосумма) которая расположена на стандартной панели.

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

    =СУММ(A2:A100)-ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A100)

    Предполагается, что :
    Мы суммируем данные скрытых ячеек диапазона A2:A100

    Для того, чтобы суммировать данные скрытых ячеек, которые были скрыты вручную, можно пойти на небольшую хитрость : отфильтровать исходные данные так, чтобы после применения фильтра, отображались все данные. Так, например, для столбца содержащего только нули и положительные числа, достаточно в качестве критерия отбора указать больше или равно 0, а для фильтрации всех чисел достаточно использовать больше или равно 0 ИЛИ меньше 0. Затем, можно скрыть необходимые строки и воспользоваться вышеупомянутой формулой.
  • Как суммировать каждую n ячейку в столбце и строке ?
  • Ответ :
    Для того, чтобы в нужном диапазоне, например, D10:D69 суммировать данные ячеек D10, D15, D20, ... т.е. суммировать данные ячеек расположенных с определённым интервалом, можно использовать любую из представленных формул :

    {=СУММ((ОСТАТ(СТРОКА(D10:D69);5)=0)*(D10:D69))}
    {=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(D10:D69);5)=0;D10:D69))}
    =СУММПРОИЗВ((ОСТАТ(СТРОКА(D10:D69);5)=0)*(D10:D69))


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

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

    {=СУММ((ОСТАТ(СТРОКА(D10:D69)-СТРОКА($D$10);5)=0)*(D10:D69))}
    {=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(D10:D69)-СТРОКА($D$10);5)=0;D10:D69))}
    =СУММПРОИЗВ((ОСТАТ(СТРОКА(D10:D69)-СТРОКА($D$10);5)=0)*(D10:D69))

    {=СУММ((ОСТАТ(СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(D10:D69)));5)=1)*(D10:D69))}
    {=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(D10:D69)));5)=1;D10:D69))}
    =СУММПРОИЗВ((ОСТАТ(СТРОКА(ДВССЫЛ("1:"&ЧСТРОК(D10:D69)));5)=1)*(D10:D69))

  • Как суммировать числовые значения, хранящиеся в виде текста ?
  • Ответ :
    Для того, чтобы в нужном диапазоне, например, C3:C14 суммировать числа, которые хранятся в виде текста, причём, без дополнительных преобразований [FAQ] можно использовать любую из представленных формул :

    {=СУММ(ЗНАЧЕН(C3:C14))}
    {=СУММ(C3:C14*1)}
    {=СУММ(C3:C14+0)}
    и т.д. и т.п.
    =СУММПРОИЗВ(ЗНАЧЕН(C3:C14))
    =СУММПРОИЗВ(C3:C14/1)
    =СУММПРОИЗВ(C3:C14-0)
    и т.д. и т.п.


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

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

    {=СУММ(ЕСЛИ(ЕЧИСЛО(ЗНАЧЕН(C3:C14));ЗНАЧЕН(C3:C14)))}
    {=СУММ(ЕСЛИ(ЕЧИСЛО(C3:C14*1);C3:C14*1))}
    {=СУММ(ЕСЛИ(ЕЧИСЛО(C3:C14+0);C3:C14+0))}
    и т.д. и т.п.
    {=СУММ(ЕСЛИ(ЕОШИБКА(ЗНАЧЕН(C3:C14));;ЗНАЧЕН(C3:C14)))}
    {=СУММ(ЕСЛИ(ЕОШИБКА(C3:C14/1);;C3:C14/1))}
    {=СУММ(ЕСЛИ(ЕОШИБКА(C3:C14-0);;C3:C14-0))}
    и т.д. и т.п.
  • Как суммировать =B2+C2+F2 только числовые значения ячеек, т.е. без учёта текстовых значений, которые могут содержать эти ячейки ?
  • Ответ :
    Вместо арифметического оператора + воспользуйтесь стандартной функцией рабочего листа =СУММ() которая, при использовании ссылок, игнорирует текст, в т.ч. и "1"

    =СУММ(B2:C2,F2)
  • Как обойти ограничение на 30 аргументов в стандартных функциях рабочего листа, например, СУММ, МИН, МАКС, СЧЁТ, СЧЁТЗ и т.д. ?
  • Ответ :
    Для того, чтобы в вычислениях участвовали данные более 30 несмежных ячеек и/или диапазонов, можно сделать следующее :

    Вариант I. Задать "лишние" ссылки как один аргумент, использовав для этого дополнительные скобки, например :

    =СУММ(A1;A3;A7;A10:A15;...;(C1;C3;C10))
    =СУММ(A1;A3;A7;A10:A15;...;(F1;H1;K5:L10);(C1;C3;C10))


    Вариант II. Создать один или несколько именованных диапазонов и использовать их в дальнейшем, например :

    =МИН(ИМЯ_ДИАПАЗОНА)
    =МИН(ИМЯ_ДИАПАЗОНА_1;ИМЯ_ДИАПАЗОНА_2;ИМЯ_ДИАПАЗОНА_3)


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

    =МАКС(МАКС(...);МАКС(...);МАКС(...))
  • Как обойти ограничение на одно условие в функциях СУММЕСЛИ, СЧЁТЕСЛИ ?
  • Ответ :
    Для того, чтобы "обойти" ограничение на одно условие в вышеупомянутых функциях, достаточно использовать массив и одну дополнительную функцию, для суммирования полученных значений.

    Пример1 : Необходимо подсчитать, сколько было отгружено товара сразу несколькими менеджерами, при этом фамилии всех продавцов находятся в ячейках A2:A1000 , а суммы по накладным в ячейках C2:C1000

    =СУММ(СУММЕСЛИ(A2:A1000;{"Иванов";"Смирнов";"Федякин"};C2:C1000))

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

    {=СУММ(СУММЕСЛИ(A2:A1000;F2:F10;C2:C1000))}
    =СУММПРОИЗВ(СУММЕСЛИ(A2:A1000;F2:F10;C2:C1000))


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

    =СУММ(СЧЁТЕСЛИ(A2:A1000;{"Иванов";"Смирнов";"Федякин"}))

    Если же перечень условий также должен находится в ячейках, то

    {=СУММ(СЧЁТЕСЛИ(A2:A1000;F2:F10))}
    =СУММПРОИЗВ(СЧЁТЕСЛИ(A2:A1000;F2:F10))


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

    {"Иванов*";"Смирнов*";"Федякин*"}
    {"*Иванов*";"*Смирнов*";"*Федякин*"}

  • Как можно быстро, но подробно узнать почти о всех функциях в MS Excel ?
  • Ответ :
  • Скомпилированный HTML-файл справки от Microsoft Office (автор файла мне неизвестен)

    ~ 550 kб [Скачать]

  • Справочник по функциям MS Excel от Microsoft Office

    [Скачать] [Скачать] [Скачать]

    Для полноценной работы справочника необходимо "включить макросы"


    Воспроизведение любых опубликованных здесь материалов возможно только с письменного разрешения автора : Microsoft Excel 95, 97, 2000, XP

  • © 2004-2016 Климов П.Ю. Все права защищены. WebDesign & Error's Klimoff