|
- Как вставить функцию в ячейку ? 2004
- Как ввести функцию без использования знака равно (=) ?
29.04.2007
- Как воспользоваться собственной функцией ?
Что такое пользовательская (собственная) функция и зачем она нужна ? 2004
- Как использовать все возможности функции СУММ ? 2004
- Как суммировать диапазон ячеек, который находится
в нескольких листах ? 2004
- Как суммировать все ячейки в столбце или строке ? 2004
- Как суммировать данные столбца, расположив функцию СУММ
в этом же столбце ? 22.12.2005
- Как суммировать только отфильтрованные данные ? 04.10.2006
- Как суммировать только данные скрытых ячеек ? 01.06.2007
- Как суммировать каждую n ячейку в столбце или строке ?
15.02.2007
- Как суммировать числовые значения, хранящиеся в виде текста ?
12.03.2007
- Как суммировать =B2+C2+F2 только числовые значения ячеек,
т.е. без учёта текстовых значений, которые могут содержать эти ячейки ?
15.04.2007
- Как обойти ограничение на 30 аргументов в стандартных функциях
рабочего листа, например, СУММ, МИН, МАКС, СЧЁТ, СЧЁТЗ и т.д. ? 13.03.2007
- Как обойти ограничение на одно условие в функциях СУММЕСЛИ,
СЧЁТЕСЛИ ? 21.03.2011
- Как можно быстро, но подробно узнать почти о всех
функциях в 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
Для полноценной работы справочника необходимо "включить макросы"
| | | | | | | | | | | | |
|