ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 17.04.2021
Просмотров: 1377
Скачиваний: 2
61
Для
того
чтобы
изменить
вид
или
способ
вычисления
данных
сводной
таб
-
лицы
,
необходимо
дважды
щелкнуть
мышью
на
каждом
из
размещенных
в
различных
областях
заголовках
.
После
щелчка
на
заголовке
Откуда
/
Куда
появится
диалоговое
окно
Вы
-
числение
поля
сводной
таблицы
.
В
списке
Скрыть
элементы
выделите
статьи
доходов
,
которые
не
должны
отображаться
в
сводной
таблице
,
фик
-
сирующей
расходы
.
В
результате
двойного
щелчка
на
заголовке
Данные
в
списке
Операция
укажите
Сумма
.
В
поле
Имя
будет
указано
имя
операции
–
Сумма
по
полю
Расход
.
Нажмите
Далее
.
5.
Укажите
место
расположения
таблицы
новый
лист
,
нажмите
Готово
.
В
результате
получится
следующая
сводная
таблица
.
62
Рисунок
50
Выбирая
элементы
из
списка
,
расположенного
в
ячейке
В
3,
вы
будете
об
-
новлять
сводную
таблицу
.
Создание
собственных
средств
анализа
данных
Определим
сумму
,
потраченную
за
период
с
5
по
15
февраля
на
по
-
купку
летней
обуви
для
матери
.
1
способ
.
Простое
суммирование
.
2
способ
.
На
новом
листе
Анализ
данных
создается
шапка
таблица
:
в
которую
переносятся
данные
,
относящиеся
к
интересующему
нас
перио
-
ду
,
для
этого
:
1.
Определите
записи
,
у
которых
дата
больше
или
равна
05.02.06.
Для
это
-
го
в
ячейку
А
4
занесите
формулу
:
=
ЕСЛИ
('
журнал
регистрации
'!A2>='
анализ
'!$A$3;1;0)
2.
Формула
работает
следующим
образом
:
если
условие
соблюдается
,
то
в
ячейку
заносится
число
1,
иначе
0.
3.
В
ячейку
В
4
поместите
формулу
для
определения
расходов
с
листа
Журнал
регистрации
:
=
ЕСЛИ
(A4=0;0;'
журнал
регистрации
'!C2)
4.
В
ячейку
С
4
занесите
формулу
,
определяющую
записи
,
у
которых
даты
меньше
15.02.06:
=
ЕСЛИ
('
журнал
регистрации
'!A2<=
анализ
!$C$3;1;0)
5.
В
ячейку
D4
поместите
формулу
,
определяющую
расходы
с
листа
Жур
-
нал
регистрации
:
=
ЕСЛИ
(C4=0;0;'
журнал
регистрации
'!C2)
63
6.
В
столбце
Е
проверьте
,
выполняются
ли
условия
в
столбцах
А
и
С
:
=
ЕСЛИ
(A4+C4=2;D4;0)
7.
В
ячейке
В
3
и
Е
3
соответственно
,
происходит
суммирование
всех
ото
-
бранных
предыдущими
формулами
значений
.
В
итоге
получаем
сумму
,
по
-
траченную
за
период
с
05.02.06
по
15.02.06.
Рабочие
листы
с
формулами
и
числовыми
значениями
приведены
ниже
:
Рисунок
51
Рисунок
52
Использование
формул
массива
для
анализа
данных
Массив
–
это
множество
ячеек
,
содержимое
которых
обрабатывается
как
единое
целое
.
Такие
ячейки
могут
указываться
как
именованный
диа
-
пазон
.
Формула
массива
–
это
формула
,
оперирующая
с
одним
или
не
-
сколькими
массивами
.
При
работе
с
формулами
массива
необходимо
знать
:
признаком
форму
-
лы
массива
являются
фигурные
скобки
в
начале
и
конце
формулы
,
которые
вводятся
нажатием
Ctrl+Shift+Enter
либо
после
завершения
ввода
форму
-
лы
,
либо
в
процессе
ее
редактирования
.
На
новом
листе
создадим
таблицу
,
выполняющую
анализ
расходов
по
заданным
критериям
.
По
окончании
работы
она
должна
выглядеть
так
:
64
Рисунок
53
Алгоритм
расчета
.
1.
В
ячейку
В
2
введем
формулу
,
которая
суммирует
все
значения
расхо
-
дов
,
произведенных
5.02.06
и
далее
.
{=
СУММ
(
ЕСЛИ
(
Дата
>=A2;
Расход
;0))}
Дата
и
Расход
–
это
имена
диапазонов
,
они
вставляются
в
формулу
коман
-
дой
Вставка
→
Имя
→
Вставить
.
2.
В
ячейку
В
3
введем
формулу
,
которая
суммирует
все
значения
расхо
-
дов
,
произведенные
до
15.02.06
и
далее
:
{=
СУММ
(
ЕСЛИ
(
Дата
<=
А
3;
Расход
;0))}
3.
Определяется
,
какая
сумма
потрачена
на
мать
:
{=
СУММ
(
ЕСЛИ
(
Кто
=A4;
Расход
;0))}
4.
Определяется
,
какая
сумма
потрачена
на
Обувь
:
{=
СУММ
(
ЕСЛИ
(
Откуда
_
Куда
=A5;
Расход
;0))}
5.
Определяется
,
какая
сумма
потрачена
на
летнюю
обувь
:
{=
СУММ
(
ЕСЛИ
(
На
_
что
=A6;
Расход
;0))}
Для
создания
модуля
последователь
-
но
вложим
формулы
друг
в
друга
и
получим
,
сколько
потрачено
на
лет
-
нюю
обувь
для
мамы
:
{=
СУММ
(
ЕСЛИ
(
Дата
>=
А
2;
ЕСЛИ
(
Дата
<=A3;
ЕСЛИ
(
Кто
=A4;
ЕСЛИ
(
Откуда
_
Куда
=A5;
ЕСЛИ
(
На
_
что
=A6;
Расход
;0);0);0);0);0))}
Рисунок
54
Созданный
модуль
позволяет
для
любого
указанного
периода
полу
-
чить
следующие
данные
:
−
какая
денежная
сумма
потрачена
на
определенного
члена
семьи
;
−
какая
денежная
сумма
проходит
по
определенной
статье
расходов
;
−
что
именно
приобретено
по
этой
статье
расходов
.
Рассмотрим
принцип
применения
созданных
формул
и
внедрения
их
в
таблицы
анализа
.
65
Расходы
на
каждого
члена
семьи
и
по
статьям
На
листе
Расходы
1
создайте
таблицу
:
Алгоритм
.
1.
Ячейкам
В
1
и
В
2
присваиваются
имена
ПериодС
и
ПериодПо
соответ
-
ственно
.
2.
В
ячейке
В
4
просуммируйте
расходы
за
указанный
период
:
=
СУММ
(
В
6:
В
9)
.
3.
В
ячейке
В
6
определяется
сумма
денег
,
потраченная
за
указанный
пе
-
риод
времени
на
конкретного
члена
семьи
.
Для
создания
формулы
вос
-
пользуйтесь
модулем
,
разработанным
ранее
.
Скопируйте
формулу
,
нахо
-
дящуюся
в
ячейке
В
2
листа
Модуль
для
анализа
данных
,
и
отредактируйте
следующим
образом
:
{=
СУММ
(
ЕСЛИ
(
Дата
>=
ПериодС
;
ЕСЛИ
(
Дата
<=
ПериодПо
;
ЕСЛИ
(
Кто
=A6;
Расход
;0);0);0))}
Для
всех
остальных
членов
семьи
формулы
копируются
.
4.
Аналогично
определяются
формулы
для
ячеек
В
12:
В
16:
{=
СУММ
(
ЕСЛИ
(
Дата
>=
ПериодС
;
ЕСЛИ
(
Дата
<=
ПериодПо
;
ЕСЛИ
(
Откуда
_
Куда
=A12;
Расход
;0);0);0))}
5.
В
столбце
D
определите
процентное
соотношение
расходов
,
например
,
в
ячейку
D6
введите
формулу
:
=B6/$B$4.
6.
По
c
тройте
диаграммы
расходов
по
каждому
члену
семьи
и
по
статьям
.