Справочник клиентов 1 страница

Код клиента	Наименование клиента	Адресклиента	Расчетныйсчет
	ЗАО«Альфа»	Вологда
	ООО«Восток»	Архангельск
	НПЦ«Планета»	Санкт-Петербург
	КЦ«Право»	Новороссийск
	ГК«Лидер»	Москва

Присвойте имя группе ячеек:

•выделите ячейки A3 -D7;

•выберите команду Присвоить имя в разделе Определенные имена меню Формулы (рис. 5);

Рис. 5. Вид окна «Создание имени»

•нажмите OK.

Переименуйте Лист 2 в Ведомость учета оказанных услуг салона связи (аналогично действиям п. 2).

Создайте таблицу Ведомость учета оказанных услуг салона связи (аналогично действиям п. 3–5) (рис. 6).

Рис. 6. Вид таблицы «Ведомость учета оказанных услуг салона связи»

6. Введите исходные данные.

7. Заполните графу Наименование клиента:

•сделайте ячейку В3 активной;

•воспользуйтесь командой Вставить функцию меню Формулы;

•в поле Категория выберите Ссылки и массивы;

•в поле Выберите функцию нажмите ВПР (рис. 7);

Рис. 7. Вид первого окна Мастера функций

•нажмите OK;

•введите в поле Искомое_значение, щелкнув по ячейке А3;

•нажмите Enter;

•введите информацию в поле Таблица;

•воспользуйтесь командой Использовать в формуле меню

Формулы, выбрав Вставить имена;

•в окне Вставка имени выделите Имя: Код клиента (рис. 8);

Рис. 8. Ввод имени массива в качестве аргумента формулы

•нажмите OK;

•нажмите Enter;

•введите информацию – цифру 2в поле Номер столбца;

•введите информацию – цифру 0 в поле Интервальный просмотр (рис. 9);

Рис. 9. Вид второго окна Мастера функций

•нажмите OK;

•установите курсор на маркер в правом нижнем углу ячейки В3, щелкните левой клавишей мыши и протяните его до ячейки В14.

Заполненная таблица выглядит так, как это показано на рис. 10.

Рис. 10. Результат заполнения таблицы

«Ведомость учета оказанных услуг салона связи»

8. Создайте сводную таблицу Фактическое оказание услуг:

•установите курсор в поле таблицы Ведомость учета оказанных услуг салона связи;

•воспользуйтесь командой Сводная таблица из меню Вставка;

•в окне Создание сводной таблицы (MS Office 2010) нажмите

OK (рис. 11).

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

Чтобы вставить поле в сводную таблицу, его необходимо перетащить в одну из четырех областей: Фильтр отчета, Названия столбцов, Названия строк и Значения:

•перенесите в поле Фильтр отчета надпись Код услуги (для этого поставьте курсор на поле Код услуги, нажмите левую клавишу мыши и, удерживая ее, перенесите в поле Фильтр отчета);

•перенесите в поле Названия строк надпись Наименование клиента;

•перенесите в поле Значения надпись Сумма по счетам за оказанные услуги;

•в результате получится сводная таблица (рис. 12);

Рис. 12. Фрагмент листа «Фактическое оказание услуг»

•переименуйте лист со сводной таблицей в Фактическое оказание услуг.

Создайте ведомость Фактическое оказание услуг по клиентам и датам:

•установите курсор в поле таблицы Ведомость учета оказанных услуг салона связи;

•воспользуйтесь командой Сводная таблица из меню Вставка;

•перенесите в поле Названия строк надпись Наименование клиента;

•перенесите в поле Названия строк надпись Дата оказания услуги;

•перенесите в поле Фильтр отчета надпись Код клиента;

•перенесите в поле Значения надпись Сумма по счетам за оказанные услуги;

•переименуйте лист со сводной таблицей в Фактическое оказание услуг по клиентам и датам (рис. 13).

Рис. 13. Фактическое оказание услуг по клиентам и датам (сводная таблица)

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

Используя сводную таблицу Фактическое оказание услуг, получите данные для кода услуги 10005:

•сделайте активным лист Фактическое оказание услуг;

•нажмите кнопку правее поля Код услуги;

•выберите код услуги – 10005 (рис. 14).

Рис. 14. Фильтрация данных по полю «Код услуги»

•нажмите OK.

Фактическое оказание услуг по коду 10005 представлено в виде сводной таблицы (рис. 15).

Рис. 15. Фактическое оказание услуг по коду «10005»

9. Представьте наглядно результаты расчетов, создав диаграмму по данным сводной таблицы:

•сделайте активным лист Фактическое оказание услуг;

•выделите диапазон A8 -B8;

•выберите команду Гистограмма в разделе Диаграммы меню Вставка;

•в разделе Объемная гистограмма выберите Объемная гистограмма с группировкой;

•переименуйте гистограмму в Фактическое оказание услуг

(рис. 16).

Рис. 16. Гистограмма «Фактическое оказание услуг»

Заключение

Таким образом, формирование сводных таблиц на основе «Ведомости учета оказания услуг салона связи» позволяет решить поставленные задачи:

отслеживать соблюдение графика и объемов оказания услуг;

контролировать своевременность погашения и уменьшение объема дебиторской задолженности.

Создание различных диаграмм (гистограмм, графиков) на основе данных сводных таблиц средствами MS Excel позволяет не только наглядно представлять результаты обработки информации для проведения анализа с целью принятия решений, но и достаточно быстро осуществлять манипуляции в области их построения в пользу наиболее удобного представления результатов визуализации по задаваемым пользователем (аналитиком) параметрам.

Приложение 3

Варианты заданий контрольной работы

Предложенные экономические задачи решите с использованием табличного процессора MS Excel. Отсутствующие числовые значения данных и единицы их измерения задайте самостоятельно.

Вариант 1

Предприятие ООО «Энергос» осуществляет деятельность по обеспечению физических и юридических лиц электроэнергией и производит расчеты по предоставленным услугам. Данные, на основе которых производятся расчеты по оплате, представлены в табл. 1.

1. Постройте табл. 1 в MS Excel.

2. Рассчитайте расход электроэнергии за месяц и сумму к оплате (табл. 2). Результаты вычислений представьте в виде таблицы MS Excel и в графическом виде.

Таблица 1

Показания электросчетчиков

Кодпла-тельщика	ФИО плательщика	Адрес	Показаниясчетчикананачаломесяца,кВт	Показаниясчетчиканаконецмесяца,кВт
	КоломиецИ.И.	проспект Мира,44-1
	ПетровА.А.	проспект Мира,44-3
	МатвееваК.К.	проспект Мира,44-5
	СорокинаМ.М.	проспект Мира,44-7
	ИвлевС.С.	проспект Мира,44-9

Таблица 2

Расчет оплаты электроэнергии

ФИО плательщика	Кодплательщика	Расходэлектроэнергииза месяц,кВт	Коплате,руб.
КоломиецИ.И.
МатвееваК.К.
ИвлевС.С.
ПетровА.А.
СорокинаМ.М.
Итого

Вариант 2

В бухгалтерии предприятия ООО «Бета» производится расчет налоговых вычетов, предоставляемых сотрудникам, и формирование платежных ведомостей. Данные для выполнения расчета налоговых вычетов приведены в табл. 1.

Стандартный налоговый вычет предоставляется каждому сотруднику в размере 400 руб. в месяц до тех пор, пока совокупный доход с начала года не превысит 40 000 руб.

Налоговый вычет на ребенка предоставляется в размере 1000 руб. в месяц до тех пор, пока совокупный доход с начала года не превысит 280 000 руб.

НДФЛ (налог на доходы физических лиц – 13%) рассчитывается с начисленной суммы заработной платы за минусом размера налоговых вычетов.

1. Постройте табл. 1 в MS Excel.

Рассчитайте размер налогового вычета, предоставляемого сотрудникам в текущем месяце, используя функцию ВПР или ПРОСМОТР. Результаты вычислений представьте в виде таблицы MS Excel (табл. 2).

2. Сформируйте и заполните форму документа «Расчетная ведомость по заработной плате» за текущий месяц (табл. 3).

Результаты вычислений представьте в графическом виде, проведите их анализ.

Таблица 1

Данные для расчета налоговых вычетов

ФИО сотрудника	Начисленоза месяц,руб.	Совокупныйдоходс начала года,руб.
ВасечкинМ.М.	5890,00	36000,00
ИвановИ.И.	7800,00	25000,00
КузнецоваС.С.	6350,00	32000,00
ПетровА.А.	9500,00	24000,00
СидороваК.К.	10200,00	39000,00

Таблица 2

Размер налоговых вычетов, предоставляемых сотрудникам в текущем месяце

ФИО сотрудника	Стандартныйналоговыйвычет на физ.лицо,руб.	Количество детей, накоторых предоставляется налоговыйвычет	Размер налоговоговычета затекущий месяц,руб.
ВасечкинМ.М.	400,00
ИвановИ.И.	400,00
КузнецоваС.С.	400,00
ПетровА.А.	400,00
СидороваК.К.	400,00

Таблица 3

Расчетная ведомость по заработной плате

	Расчетныйпериод
	с	по
	.__.20	.__.20
Табель-ныйномер	ФИО сотрудника	Начис-лено замесяц,руб.	Размерналоговоговычета,руб.	НДФЛ, руб.	Квыпла-те,руб.
	ИвановИ.И.
	ПетровА.А.
	ВасечкинМ.М.
	СидороваК.К.
	КузнецоваС.С.
ИТОГО ПОВЕДОМОСТИ

Вариант 3

Компания «Страховщик» осуществляет страховую деятельность на территории России по видам полисов, представленных в табл. 1. Каждый полис имеет фиксированную цену.

Компания имеет свои филиалы в нескольких городах (табл. 2) и поощряет развитие филиалов, предоставляя определенный дисконт. Дисконт пересматривается ежемесячно по итогам общих сумм договоров по филиалам.

В конце каждого месяца составляется общий реестр договоров по всем филиалам (табл. 3).

1. Постройте табл. 1–3 в MS Excel.

2. Организуйте межтабличные связи для автоматического заполнения документа «Реестр договоров» (табл. 3), используя функцию ВПР или ПРОСМОТР.

Произведите расчет суммы полисов по филиалам.

Результаты вычислений представьте в графическом виде, проведите их анализ.

Таблица 1

Виды страховых полисов

Код вида страхового полиса	Наименование страхового полиса	Сумма страхового полиса,руб.
	От несчастногослучая
	От автокатастрофы
	От авиакатастрофы
	Медицинский
	Автомобильный
	Жилищный

Таблица 2

Список филиалов компании «Страховщик»

Код филиала	Наименование филиала	Дисконтный% с каждого полиса По филиалу
	Московский
	Тульский
	Уфимский
	Липецкий
	Ростовский
	Воронежский

Таблица 3

Данные реестра договоров

Код филиа- ла	Наименованиефилиала	Код стра- хового полиса	Наименование страхового полиса	Дата выдачи полиса	Сумма полиса,руб.	Сумма скидки по дисконту,руб.
				11.11.12
				12.11.12
				13.11.12
				14.11.12
				11.11.12
				16.11.12
				17.11.12
				12.11.12
				19.11.12
				20.11.12

Вариант 4

Клиент банка «Акцепт+» осуществляет ежемесячное погашение кредита равными (аннуитетными) платежами.

Ежемесячная сумма погашения основного долга рассчитывается как отношение суммы кредита к количеству месяцев, на которое выдан кредит.

Сумма процентов определяется как произведение суммы текущего остатка по кредиту на процентную ставку в месяц.

Процентная ставка в месяц рассчитывается как отношение процентной ставки кредита к количеству месяцев, на которое выдан кредит.

Сумма текущего остатка по кредиту определяется как разница между суммой предыдущего остатка по кредиту и текущей суммой погашения основного долга.

Платеж по кредиту складывается из текущей суммы процента по кредиту и текущей суммы погашения основного долга.

Рассчитайте сумму ежемесячных платежей по кредиту. Постройте таблицу в MS Excel и заполните ее. Результаты округлите до целого числа, используя функцию ОКРУГЛ.

Для того чтобы итоговая сумма погашения основного долга

равнялась сумме выданного кредита, используйте функцию ЕСЛИ для отражения остатков по платежу в последнем платеже. Учтите, что сумма последнего платежа по погашению основного долга будет больше, чем сумма платежа за любой предыдущий месяц.

По данным таблицы постройте гистограмму «Аннуитетные платежи по месяцам».

Таблица

Платежи по кредиту клиента банка «Акцепт+» за 9 месяцев 2012 г.

Годовая процентнаяставка	12%
Кредит выданна	12месяцев
Сумма кредита,руб.
Номерплатежа	Датаплатежа	Текущийостатокпокре-диту,руб.	Суммапроцен-тов,руб.	Погаше-ниеос-новногодолга,руб.	Платежпокре-диту,руб.
	Январь
	Февраль
	Март
	Апрель
	Май
	Июнь
	Июль
	Август
	Сентябрь
Итого

Вариант 5

ОАО «Россвязь» оказывает услуги междугородней телефонной связи абонентам квартирного сектора, в конце месяца производит расчеты и направляет абоненту квитанцию об оплате телефонных разговоров.

1. Рассчитайте стоимость междугородних телефонных разговоров абонента по заданным значениям (табл. 1). Для определения дня недели, когда производился звонок, используйте функцию

ДЕНЬНЕД, а также функции ЕСЛИ и ИЛИ.

2. Сформируйте документ «Квитанция для оплаты телефонных разговоров» (табл. 2).

По данным квитанции постройте гистограмму «Стоимость звонков на определенную дату».

Таблица 1

Тарифы на услуги междугородней телефонной связи

Тарифы на услуги междугородней телефонной связи ОАО«Россвязь» для абонентов квартирногосектора
Наименованиегорода	Код города	В рабочие дни, руб.	В выходные дни,руб.
Волгоград		4,50	4,20
Киров		4,50	4,20
Пенза		5,50	4,50
Челябинск		7,50	4,80
Новосибирск		7,50	4,80

Таблица 2

Квитанция для оплаты телефонных разговоров

Квитанция для оплаты телефонных разговоров Номер телефона 123-45-67
Дата	Кодгорода	Минут	Стоимость, руб.
15.01.2012
16.01.2012
17.01.2012
18.01.2012
23.01.2012
Итого
Срок оплаты счетадо:

Вариант 6

Исходные данные для расчета заработной платы сотрудников организации представлены в табл. 1, 2.

1. Постройте табл. 1–3 в MS Excel.

2. В табл. 3 для заполнения столбцов «Фамилия» и «Отдел» используйте функцию ПРОСМОТР.

3. Для получения результата в столбце «Сумма по окладу», используя функцию ПРОСМОТР, по табельному номеру найдите соответствующий оклад, разделите его на количество рабочих дней и умножьте на количество отработанных дней. Сумма по надбавке считается аналогично.

4. Сформируйте документ «Ведомость заработной платы сотрудников» (табл. 3).

5. Данные результирующей таблицы отсортируйте по номеру отдела, рассчитайте итоговые суммы по отделам.

Результаты вычислений представьте в графическом виде, проведите их анализ.

Таблица 1

Данные о сотрудниках

Табельныйномер	Фамилия	Отдел	Оклад,руб.	Надбавка,руб.
	ИвановаИ.И.	Отделкадров	7000,00	4000,00
	ПетроваП.П.	Бухгалтерия	9500,00	3000,00
	СидороваС.С.	Отделкадров	6000,00	4500,00
	МишинМ.М.	Столовая	6500,00	3500,00
	ВасинВ.В.	Бухгалтерия	7500,00	1000,00
	ЛьвовЛ.Л.	Отделкадров	4000,00	3000,00
	ВолковВ.В.	Отделкадров	3000,00	3000,00

Таблица 2

Данные об учете рабочего времени

Табельный номер	Количество рабочих дней	Количество отработанных дней

Таблица 3

Ведомость заработной платы сотрудников

Табель-ный номер	Фами-лия	Отдел	Суммапо окла-ду,руб.	Суммапо над- бавке,руб.	Суммазара-ботной платы,руб.	НДФЛ, %	СуммаНДФЛ, руб.	Суммак вы-даче, руб.
Всего

Вариант 7

Торговое предприятие ООО «Электрон» осуществляет розничную продажу бытовых электроприборов и средств вычислительной техники (СВТ). Для продвижения товара на рынок предприятие организует рекламную кампанию, на проведение которой формируется рекламный бюджет в зависимости от объема продаж и показателя отчислений на рекламу. Данные, на основе которых выполняется расчет рекламного бюджета предприятия, приведены в табл. 1, 2.

1. Постройте табл. 1, 2 в MS Excel.

2. Рассчитайте объем продаж предприятия по каждому месяцу за текущий год (табл. 1).Организуйте межтабличные связи для автоматического формирования рекламного бюджета, используя функцию ВПР или ПРОСМОТР.

3. Сформируйте и заполните итоговую таблицу, содержащую расчет рекламного бюджета предприятия ООО «Электрон» по месяцам и за текущий год (табл. 3).

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

Таблица 1

Объем продаж

Месяц	Объем продаж бытовой техники, руб.	Объем продаж СВТ,руб.	Объем продаж, всего за месяц,руб.
Январь
Февраль
Март
Апрель
Май
Июнь
Июль
Август
Сентябрь
Октябрь
Ноябрь
Декабрь

Таблица 2

Показатель отчислений на рекламу, %

Месяц
3,2	3,5	3,4	4,1	4,8	4,9	3,9	4,5	3,2	3,3	4,2	4,9

Таблица 3

Расчет рекламного бюджета

Месяц	Объем продаж,всего за месяц, руб.	Показательотчислений на рекламу,%	Рекламныйбюджет,руб.
Январь
Февраль
Март
Апрель
Май
Июнь
Июль
Август
Сентябрь
Октябрь
Ноябрь
Декабрь
Итого общий рекламный бюджет за год,руб.

Вариант 8

Туристическая фирма ООО «Турист» осуществляет реализацию заграничных туров, в стоимость которых включаются обязательные экскурсионные программы. Первичный расчет стоимости тура осуществляется в долларах, а затем в рублях по текущему курсу. Данные для выполнения расчетов представлены в табл. 1, 2.

Дата добавления: 2015-03-31