ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 21.10.2020
Просмотров: 1699
Скачиваний: 3
Центр Компьютерного Обучения «Специалист»
www.specialist.ru
26
Сложить если
…
СУММЕСЛИ
(
диапазон
_
проверки
;
критерий
_
проверки
;
диапазон
_
суммирования
)
SUMIF
Суммирует
ячейки из
диапазона суммирования
,
если соответствующие им ячейки в
строке из
диапазона проверки
удовлетворяют заданному условию
–
критерию проверки
СУММЕСЛИ
(
А
2:
А
10
; “
Баунти
”;
С
2:
С
10
)
суммирует числа
из
столбца С
, если
соответствующие им ячейки
столбца А
содержат
слово
Баунти
СУММЕСЛИ
(
B2:B10
;
“>50”
;
С
2:
С
10
)
суммирует числа
из
столбца С
, если
соответствующие им ячейки
столбца
B
содержат
число большее
числа 50
СУММЕСЛИ
(
B2:B10
;
“>”&D2
;
С
2:
С
10
)
суммирует числа
из
столбца С
, если
соответствующие им ячейки
столбца
B
содержат
число
,
большее
числа
из
ячейки
D2
СУММЕСЛИМН
(
диапазон
_
суммирования
;
диапазон
_
условия
1
;
условие
1;
диапазон
_
условия
2
;
условие
2;
…
;
диапазон
_
условия
127
;
условие
127)
SUMIFS
Суммирует
ячейки из
диапазона суммирования
,
если выполняются
все условия
СУММЕСЛИМН
(
С
2:
С
10
;
А
2:
А
10
; “
Баунти
” ;
B2:B10
;
“>
01/01/2008
”
)
суммирует
числа
из
столбца С
, если соответствующие им ячейки
столбца А
содержат
слово
Баунти, а дата столбца
B
больше
01/01/2008
(Баунти, проданные после 01/01/2008)
Статистические функции
МАКС
(
диапазон
)
MAX
Ищет
максимальное значение
в указанном диапазоне ячеек
МИН
(
диапазон
)
MIN
Ищет
минимальное значение
в указанном диапазоне ячеек
СРЗНАЧ
(
диапазон
)
AVERAGE
Ищет
среднее значение
в указанном диапазоне ячеек
СЧЁТ
(
диапазон
)
COUNT
Подсчитывает
количество чисел
в указанном диапазоне ячеек
СЧЁТЗ
(
диапазон
)
COUNTA
Подсчитывает
количество непустых ячеек
в указанном диапазоне
Счет
,
Среднее если
…
СЧЁТ
ЕСЛИ
(
диапазон
;
критерий
_
проверки
)
COUNTIF
Подсчитывает
количество ячеек
из диапазона
,
удовлетворяющих
критерию проверки
СЧЁТЕСЛИ
(
А
2:
А
10
; “
Баунти
”)
количество ячеек
столбца
A
,
содержащих
слово
Баунти
СЧЁТЕСЛИМН
(
диапазон
_
условия
1
;
условие
1
…
диапазон
_
усл
.
127
;
усл
.
127
)
COUNTIFS
Подсчитывает
количество ячеек
из диапазона
,
удовлетворяющих
всем условиям
СЧЁТЕСЛИМН
(
А
2:
А
10
; “
Баунти
” ;
B2:B10
;
“>
01/01/2008
”
)
количество строк
,
в которых
ячейки
столбца А
содержат
слово
Баунти, а дата столбца
B
>
01/01/2008
СРЗНАЧЕСЛИ
(
диапазон
_
проверки
;
условие
;
диапазон
_
усреднения
)
AVERAGEIF
Ищет
среднее
значение в
диапазоне усреднения
,
если соответствующие им ячейки в строке
из
диапазона проверки
удовлетворяют
заданному условию
СРЗНАЧЕСЛИ
(
B2:B10
;
“>50”
;
С
2:
С
10
)
среднее число
из
столбца С
, если
соответствующие им ячейки
столбца
B
содержат
число большее
числа 50
СРЗНАЧЕСЛИМН
(
диапазон
_
усреднения
;
диапазон
_
условия
1
;
усл
.1;
…
)
AVERAGEIFS
Ищет
среднее
значение в
диапазоне усреднения
,
если выполняются
все условия
СРЗНАЧЕСЛИМН
(
С
2:
С
10
;
А
2:
А
10
; “
Баунти
” ;
B2:B10
;
“>01/01/2008”
)
среднее
значение
по
столбцу
C
, если соответствующие ячейки
столбца А
содержат
слово
Баунти, а дата столбца
B
больше
01/01/2008
Расширенные возможности Microsoft Excel 2007
27
Ссылки и массивы
ПОИСКПОЗ
(
что
_
ищем
;
диапазон
_
поиска
;
тип
_
сопоставления
)
MATCH
Находит
относительное положение
элемента
в диапазоне данных
(
поиск позиции
)
Диапазон
поиска
:
чаще
–
один столбец
,
если
указать два
,
то ищет
совпадения
в каждом
Тип
сопоставления
:
1
‐ ищет
max
значение
<=
искомого
(
сортировка
по возрастанию
)
0
‐ ищет
первое точное совпадение
(если не находит –
#
Н
/
Д
или
#N/A
)
1
– ищет
min
значение
>=
искомого
(сортировка
по убыванию
)
ПОИСКПОЗ
(“
Баунти
”;
А
2:
А
10
;
0
)
–
ищет в ячейках столбца А ячейку со значением «Баунти».
Результат
–
число
–
относительное положение ячейки
“Баунти” в
диапазоне А
2:
А
10
ИНДЕКС
(
диапазон
;
номер
_
строки
;
номер
_
столбца
)
INDEX
Возвращает значение ячейки из диапазона
,
заданной номером строки и номером столбца
Диапазон
:
Таблица
(
массив
) состоит из строк и столбцов. Если содержит только одну
строку (столбец), то соответствующий аргумент не является обязательным
ИНДЕКС
(
B2:F11;3;2
)
–
значение ячейки из диапазона
B2:F11
в
3
ей
строке
2
го
столбца
С
4
ПОИСКПОЗ
и
ИНДЕКС
часто используют вместе
,
что позволяет по найденному
значению в одном столбце найти соответствующее ему значение из другого столбца
Задача
:
Внести
в
ТАБЛИЦУ ЗАКАЗОВ
цены товаров
из
ПРАЙС‐ЛИСТА
Шаг
1
.
Подставим из Прайслиста
в Заказы
ц
ен
у
1
го
товара
(Яблоки)
=
ИНДЕКС
(
$F$3:$G$19
;
ПОИСКПОЗ
(B3;$F$3:$F$19;0);
2
)
=
ИНДЕКС
(
Весь
Прайс
;
№
строки
;
№
столбца
)
Шаг
2.
Скопируем результат
по столбцу вниз
(для всех товаров)
Центр Компьютерного Обучения «Специалист»
www.specialist.ru
28
ВПР
(
искомое
_
значение
;
таблица
;
номер
_
столбца
;
тип
)
VLOOKUP
Ищет значение
в крайнем левом столбце
таблицы
,
возвращает значение в той же строке
из указанного
столбца таблицы
.
Функция
ВПР
применяется
для вертикальных таблиц
Номер
столбца:
число
,
соответствующее номеру столбца
с нужными данными таблицы
Тип
:
число
0
(
Ложь
) ‐ ищет
первое точное совпадение
(если не находит –
#
Н
/
Д
или
#N/A
)
число
≠
0
(
Истина
) ‐
если нет совпадения
– выдает
max
значение
,
<
искомого
ВПР
(“
Баунти
”;
А
2:D10
;
4
;
0
)
–
ищет в ячейках
1
го
столбца
(
А
) ячейку со значением “Баунти”
Результат формулы –
значение
ячейки
4
го
столбца таблицы (столбец
D
) строки с “Баунти”.
Задача
:
Внести
в
ТАБЛИЦУ ЗАКАЗОВ
цены товаров
из
ПРАЙС‐ЛИСТА
Шаг
1
.
Подставим из Прайслиста
в Заказы
ц
ен
у
1
го
товара
(Яблоки)
=
ВПР
(
B3
;$F$3:$G$19;
2
;
0)
=
ВПР
(
что
ищем
;
где
;
№
столбца
;
тип
)
Шаг
2.
Скопируем результат
по столбцу вниз
(для всех товаров)
ГПР
(
искомое
_
значение
;
таблица
;
номер
_
строки
;
тип
)
HLOOKUP
Ищет значение в
крайней верхней строке таблицы
,
возвращает значение в том же
столбце из указанной
строки таблицы
.
ГПР
применяется
для горизонтальных таблиц
Номер
строки:
число
,
соответствующее номеру строки
с нужными данными таблицы
Тип
:
число
0
(
Ложь
) ‐ ищет
первое точное совпадение
(если не находит –
#
Н
/
Д
или
#N/A
)
число
≠
0
(
Истина
) ‐
если нет совпадения
– выдает
max
значение
,
<
искомого
Пусть
таблица в первой строке отображает названия товара
, тогда:
ГПР
(“
Баунти
”;
А
1:D10
;
4
;
0
)
–
ищет в ячейках
1
й
строки
таблицы ячейку с товаром “Баунти”
Результат формулы –
значение ячейки
4
й
строки
таблицы найденного столбца с “Баунти”
Расширенные возможности Microsoft Excel 2007
29
Финансовые функции
Расходы
задаются
отрицательными
суммами (
например
,
вклад в банк
)
Доходы
задаются
положительными
суммами (
например
,
кредит в банке
)
Тип
(Type):
число
0
(
выплата
по кредиту/вкладу производится
в конце периода
)
число
1
(
выплата
по кредиту/вкладу производится
в начале периода
)
ПС
–
Приведенная
(
полученная
/
отданная
)
Стоимость
PV
Взяли кредит
:
ПС
=
сумма кредита
(
ПС
> 0,
взяли в долг
,
полученная
сумма
)
Вложили в банк
:
ПС
=–c
умма начального вклада
(
ПС
<
0,
дали в долг
,
отданная
сумма
)
БС
–
Будущая Стоимость
(
накоплений
/
расплаты по кредиту
)
FV
Плата по кредиту
:
БС
=0
(
должны
расплатиться
к концу срока
,
прийти к нулю
),
БС
=–
предоплата
(
кредит с предоплатой
,
БС
<0
,
–
сумма предоплаты
)
Вклад в банк
:
БС
=
будущая сумма накоплений
(
БС
>
0
,
сумма в конце срока
)
СТАВКА
–
периодическая процентная Ставка
(
по кредиту
/
вкладу
)
RATE
Ставка
вычисляется из
номинальной
ставки, если известен
период
платежей/начислений
Дано
:
Номинальная ставка
12%
годовых; пусть год поделен на
12
периодов
(12 месяцев)
Тогда
:
Ставка
=
12%
(
номинальная ставка
)
делим на
12
(
месяцев
)
=
12
%
/
12=1%
Если
СТАВКА
по
кредиту
/
вкладу
даётся
в годовом исчислении
(12%
годовых
),
а
выплаты
_
по
_
кредиту
/
начисления
_
по
_
вкладу
исполняются
раз в месяц
или
раз в
квартал
,
то надо
СТАВКУ
(12%
годовых
)
делить на
12
(
месяцев
)
или
на
4
(
квартала
)
Если
СТАВКА
является
искомой величиной
,
то при использовании в качестве её аргументов
величин в
месяцах
(
ежемесячные платежи
/
ежемесячное пополнение вклада
),–
получаем в
результате
ежемесячную СТАВКУ
(%
в месяц
).
Для получения
годовой СТАВКИ
(%
в год
)
надо
ответ умножить на
12
(
месяцев
)
КПЕР
–
Количество ПЕРиодов
платежей
/
начислений
(
срок кредита
/
вклада
,
всегда
>0
)
NPER
Если
срок кредита
/
вклада
(
КПЕР
)
даётся
в годовом исчислении
(3
года
),
а
выплаты
_
по
_
кредиту
/
начисления
_
по
_
вкладу
исполняются
раз в месяц
или
раз в
квартал
,
то надо
эти
года умножить на
12
(
месяцев
)
или
на
4
(
квартала
)
Если
КПЕР
является
искомой величиной
,
то при использовании в качестве аргументов
величин в
месяцах
(
ежемесячные платежи
/
ежемесячное пополнение вклада
),–
получаем в
ответе
количество периодов в месяцах
.
Для получения
периода в годах
,
надо
ответ разделить на
12
(
месяцев
)
ПЛТ
–
величина периодических равных ПЛаТежей
(
периодические платежи по кредиту
/
периодические вклады
)
PMT
Взяли кредит
:
ПЛТ
=
–
периодические
равные выплаты
(
ПЛТ
<
0,
отдаём
)
Вклад в банк
:
ПЛТ
=–
периодические
равные вклады
(
ПЛТ
<
0,
отдаём
–
откладываем
)
ПЛТ
=0
,
если
начальный вклад
не сопровождается
периодическими равными
вкладами
Если
КПЕР
(
срок кредите
/
вклада
)
изменяется
в месяцах
,
то
Ставка
(%
по вкладу
/
кредиту
)
тоже
переводится в месяцы
,
следовательно
искомая
ПЛТ
(
величина периодических
равных платежей
)
будет измеряться
в месяцах
.
Если известно
,
что
ПЛТ
(
выплаты
_
по
_
кредиту
/
периодические
_
равные
_
начисления
_
на
_
счёт
)
будут в месяцах
(
например
,
500
р
.
ежемесячно
),
следовательно и известные или искомые
величины
КПЕР
и
СТАВКА
должны или будут
тоже исчисляться в месяцах
З
С
в
в
в
З
б
С
в
в
в
П
На
Фу
Центр
КРЕД
Задачи
:
ПС
СТАВКА
в годах
КПЕР
месяцах
ПЛТ
месяцах
ИНВЕ
Задачи
:
БС
будущая
сумма
СТАВКА
в годах
КПЕР
месяцах
ПЛТ
месяцах
ПС
нач
.
вклад
Изв
исх
Час
Эффе
апример
,
П
Т
Форм
ункции:
Компьютерн
ДИТ
(
берем
Сколь
выпла
каждый м
возьму
9
00
1
делим
в фо
3
ФОР
ответ:
ЕСТИЦИИ
Сколько
докладыв
месяц на
для накоп
суммы
9
000,0
11%
делим
н
24
ФОРМУ
ответ:
33
0
вестно
,
чт
ходя из кот
сто банки
ективная
Пусть:
Но
Тогда:
Пе
Эф
(
т
мула
связи
ЭФФЕКТ
НОМИНА
ного Обучен
м
)
ько надо
ачивать
месяц
,
если
у кредит
?
00,00
р.
5%
ормуле
на
1
36
РМУЛА
311,99
р.
И
(
даем
,
от
надо
вать в
счет
,
пления
ы
?
Н
н
в
00
р.
%
на
12
д
о
УЛА
6,97
р
.
то
Номин
торой опре
работают
я
процентн
оминальн
ериодичес
ффективн
.
е
.
Периоди
и
между
Эф
(EFFECT)
–
АЛ
(NOMINA
ния «Специал
Решен
и
За как
смогу о
кре
9
00
2
15
делим
ФОР
ответ
200
ткладыв
На какой сро
адо сделат
клад
,
чтоб
накопить
сумму
?
9
000,00
р.
11%
делим
на
12
ФОРМУЛА
ответ:
37,85
200,00
р.
0
альная
пр
еделяют
п
т по
Эффе
ная ставка
ая
(
годова
ская
проц
ая
процен
ически нач
ффективн
найти
Эф
AL) –
найти
лист»
30
ие задач
кой срок
отдать
дит
?
0,00
р.
5%
м на
12
МУЛА
т:
66,55
0,00
р.
ваем
)
ок
ть
ы
Каков
быть
чт
нак
су
9
00
2
ФОР
ответ
*
А
5
20
роцентна
периодиче
ективной
а
–
эквива
ая
)
ставка
ентная ста
нтная
став
числяя
по
1
ой
и
Номи
фективну
и
Номинал
ч
Найт
банковски
кредиту
таких усл
9
000,0
ФОРМУ
ответ
*12
50
200,0
в должен
%
банка
,
тобы
копить
умму
?
00,00
р.
РМУЛА
*12
=
61
%
24
00,00
р.
0
ая
ставка
ескую
проц
й
процентн
алент годо
а
=
12%
;
П
авка =
1%
вка
=
12,68
1%
в
месяц
инальной
ую
ставку,
льную
став
ти
ий
%
по
у при
ловиях
?
00
р
.
о
УЛА
2
=
5%
00
р.
Какую
сумму
накоплю
?
ФОРМУЛА
ответ:
5
341,71
р
11%
делим на
12
24
200,00
р.
0
а
–
это
год
центную
ст
ной ставке
овой приб
ериодов
=
(
12%
/
12
8
%
годовы
ц
получим
1
ставками
зная Номи
вку
,
зная
Э
www.specia
Какую сум
могу взят
кредит
ФОРМУЛ
твет:
5
769
15%
делим на
36
200,00
р
?
Скол
накопл
допл
сделав
вкла
А
р.
ФОРМ
отв
6
224
а
11%
делим
24
.
0
5
000
довая ста
тавку
(
СТА
е
.
были
=
12
(
месяц
месяцев
)
ых
,
2,68
%
годо
:
инальную
Эффективн
alist.ru
мму
ть в
?
ЛА
9,45
р.
12
р.
лько
лю без
лат
,
в нач
.
ад
?
МУЛА
ет:
,14
р.
%
на
12
4
0
0,00
р.
авка
,
АВКА
)
ев
)
овых
)
;
ную
.