|
|
| |||
| Как вставить функцию в ячейку ? |
Выберите ячейку в которой Вы хотите разместить функцию. В меню Вставка выберите команду Функция, или нажмите клавиши SHIFT + F3. В списке Категория выберите нужный вариант. В списке Функция выберите нужную функцию и нажмите кнопку ОК. Введите в текстовые поля адреса ячеек (чтобы не набирать вручную, щелкните кнопку справа от текстового поля и выберите диапазон с помощью мышки) Закончив заполнение полей нажмите кнопку ОК.
Как ввести функцию без использования
знака равно (=) ? |
| Функцию можно ввести использовав символ @, например : @СУММ(A2:A100)
Как воспользоваться собственной функцией ?
|
| Выполните те же действия [Как вставить функцию в ячейку], но в списке Категория выберите вариант Определенные пользователем. 
Обратите внимание на то, что пользовательская функция располагается в указанной категории только по умолчанию. И если Вы воспользуетесь этим советом, то пользовательская функция будет доступна уже в другой категории.
Как использовать все возможности функции СУММ ? |
| Пример 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
Другие возможности использования функции СУММ [Как суммировать все ячейки в столбце или строке] [Как суммировать диапазон ячеек в нескольких листах] Подобная формула будет выглядеть следующим образом : =СУММ(Лист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
[Более подробно о функции СУММ]
Как суммировать данные столбца, расположив функцию СУММ
в этом же столбце ? |
| Подобные формулы будут выглядеть следующим образом : =СУММ(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))
Как сделать так, чтобы после добавления новых строк, функция
СУММ автоматически считала данные новых ячеек ? |
| Если надо просуммировать данные, допустим, диапазона A2:A10, то мы можем просто ввести в ячейку A11 формулу =СУММ(A2:A10) Но как только мы выделим вторую и/или десятую строку и Вставить строку или добавим ячейки, то новые ячейки уже не будут участвовать в подсчёте. И если Вы не хотите вручную исправлять формулу, а хотите, чтобы формула автоматически учитывала новые строки, то используйте любой из нижопубликованных вариантов : Вариант I. =СУММ(A1:ИНДЕКС(A:A;СТРОКА()-1)) Вариант II. =СУММ(A1:СМЕЩ(A11;-1;0)) Вариант III. =СУММ(A1:ДВССЫЛ("R[-1]C";0)) Примечание : Обратите внимание на то, что во всех вариантах, мы включаем в суммируемый диапазон ещё и заголовок(шапку) таблицы, т.е. A1 И во втором случае, нужно указать ещё и ячейку, где находится функция СУММ
Как суммировать только отфильтрованные данные ?
| 
| Для того, чтобы суммировать данные только видимых ячеек, которые были получены после применения фильтра (автофильтр/расширенный фильтр), необходимо использовать стандартную функцию рабочего листа =ПРОМЕЖУТОЧНЫЕ.ИТОГИ() Пример : =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(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. Или же использовать несколько стандартных функций рабочего листа, например : =МАКС(МАКС(...);МАКС(...);МАКС(...)) Примечание : Обратите внимание на то, что начиная с версии 2007 количество аргументов было увеличено до 255.
Как обойти ограничение на одно условие в функциях СУММЕСЛИ,
СЧЁТЕСЛИ ? |
| Для того, чтобы "обойти" ограничение на одно условие в вышеупомянутых функциях, достаточно использовать массив и одну дополнительную функцию, для суммирования полученных значений. Пример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 ? |
|  ~ 550 kб [Скачать]
|
[Скачать]
[Скачать]
[Скачать]
Воспроизведение любых опубликованных здесь материалов возможно только с
письменного разрешения автора :