Счетчик затрат на мобильную связь

Классические реляционные базы данных представляют собой совокупность связанных друг с другом таблиц (отношений). Для работы с базами данных предназначены специальные программы, например Access. В Excel также можно создавать и использовать базы данных: они представляют собой списки расположенные на отдельных листах одной, или нескольких книг.

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

Оформление 1‐го листа

На первом листе создадим следующий документ:

1. Введём все значения в таблицу.

2. Выделим визуальную таблицу. Наведём на неё курсор и щёлкнем правой кнопкой мыши. Выберем Формат ячеек. На вкладке Граница выберем внешние и внутренние. Закроем окно.

3. Выделим ячейки А1, В1, С1. Наведём на одну из них курсор и щёлкнем правой кнопкой мыши. Выберем Формат ячеек. На вкладке Заливка выберем цвет. Цвет по вашему желанию. Он не имеет значения. Проделаем то же и со столбцами.

4. Выделим таблицу и выполним команду Главная/(ячейки)Формат/Автоподбор ширины столбца.

5. В нижнем левом углу вы видите Лист1, Лист2, Лист3. Переименуем Лист1 в Справочник. Для этого наведём курсор на Лист1 и дважды нажмём левую кнопку мыши.

Итак, мы создали Справочник абонентов.

Оформление 2‐го листа

Теперь наведём курсор на Лист2 и нажмём левую кнопку мыши. Мы перешли на второй лист. Оформим его так:

1. Введём все значения в таблицу.

2. Выделим визуальную таблицу. Наведём на неё курсор и щёлкнем правой кнопкой мыши. Выберем Формат ячеек. На вкладке Граница выберем внешние и внутренние. Закроем окно.

3. Выделим строки А1, В1. Наведём на одну из них курсор и щёлкнем правой кнопкой мыши. Выберем Формат ячеек. На вкладке Заливка выберем цвет. Цвет по вашему желанию. Он не имеет значения. Проделаем то же и со столбцами.

4. Выделим таблицу и выполним команду Главная/(ячейки)Формат / Автоподбор ширины столбца.

5. Переименуем Лист2 в Тарификация.

Итак, мы создали Справочник тарификации звонков.

Оформление 3‐го листа

А теперь приступим к выполнению главной части данного проекта. Создадим документ такого вида.

1. Введём все значения в таблицу.

2. Выделим визуальную таблицу. Наведём на неё курсор и щёлкнем правой кнопкой мыши. Выберем Формат ячеек. На вкладке Граница выберем внешние и внутренние. Закроем окно.

3. Выделим строки А1:D1. Наведём на одну из них курсор и щёлкнем правой кнопкой мыши. Выберем Формат ячеек. На вкладке Заливка выберем цвет. Цвет по вашему желанию. Он не имеет значения. Проделаем то же и со столбцами.

4. Выделим таблицу и выполним команду Главная/(ячейки)Формат / Автоподбор ширины столбца.

5. Переименуем Лист3 в Разговоры за январь.

6. Итак, мы создали список разговоров за месяц для одного абонента. Но у нас их много, поэтому создадим для всех.

7. Выделим столбцы А, В, С, D, но не выделяя в них первую строку. Наведём курсор на выделение и щёлкнем правой кнопкой мыши. Выберем копировать.

8. Теперь выделим ячейку А33 и щелкнем правой кнопкой мыши. Выберем вставить. Переименуем ячейку А33:

Проделаем так несколько раз, пока не выпишем всех, кто есть в справочнике:

Итак, мы создали Список разговоров абонентов за месяц.

Создание сводной таблицы

Теперь, когда у нас есть вся необходимая информация об абонентах, тарификации и списка разговоров за месяц, нам осталось подсчитать сумму. Мы должны получить документ такого вида.

Для этого необходимо выполнить следующие действия.

1. Выделим всю таблицу и выполним команду Вставка /(таблицы)Сводная таблица. укажем диапазон таблицы (Он должен быть указан автоматически, если перед созданием сводной таблицы мы выделили её). Укажем поместить таблицу на новый лист, и нажмём ОК.

2. Выберем абонентов справа в списке полей сводной таблицы. Добавим Дату в Название столбцов щелкнув правой кнопкой мыши на элементе дата и выбрать Название столбцов, аналогично добавьте Количество исходящих звонков на мобильный в Значения.

3. Вы видите перед собой подсчёты. Но это только сумма по количеству исходящих звонков на мобильный за месяц. Поэтому создадим ещё одну сводную таблицу.

4. Выделим всю таблицу и выполним команду Вставка/(таблицы) Сводная таблица. укажем диапазон таблицы (Он должен быть указан автоматически, если перед созданием сводной таблицы мы выделили её). Укажем поместить таблицу на существующий лист, и укажем диапазон: Лист4!$A$17 и нажмём ОК.

5. Выберем абонентов справа в списке полей сводной таблицы нажав на правую кнопку мыши и выберем добавить в название строк. Аналогично добавим Дату в название столбцов, а Количество исходящих звонков на домашний в Значения.

6. Готово. Теперь выделим ячейки А31 и В31. Придадим им цвет и границы. В А31 напишем Количество потраченных $ в месяц. Теперь выделим ячейку В31. В строке формул введём следующую формулу: =AF14*Тарификация!A2 + AF28*Тарификация!B2.

Заключение

Данная база данных позволяет вычислить сумму затрат на мобильную связь за месяц.

Важно! Вводить новых абонентов и информацию про них через форму, путем: ДАННЫЕ – ФОРМА. Таким образом, если каждый день вписывать данные с телефона можно проверить, правильно ли вас обслуживает оператор сотовой связи.

Задание для самостоятельной работы

1. Создать базу данных, предназначенную для расчета стоимости затрат на Интернет за месяц. База должна состоять из трех списков: абоненты, тарификация, трафик за месяц. Включите в нее 3 абонентов, тарификацию внутренних и внешних ресурсов, как показано на картинка ниже..

2. Сделать сводную таблицу (как показано ниже), с суммарными расходами за Интернет для любого месяца.

Лист 1 (абоненты)

Лист 2 (тарификация)

Лист 3 (Трафик за месяц)

 

Лист 4 (Сводная таблица)