Плт(ставка;кпер;пс;бс;тип)

Аргументы функции:

СТАВКА Процентная ставка по ссуде

КПЕР Общее число выплат

ПС Текущее значение, т. е. общая сумма, которую составят будущие платежи

БС Будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если аргумент БС опущен, то он полага­ется равным 0 (например, будущая стоимость займа равна 0)

ТИП Число 0 или 1, обозначающее, когда должна производиться выпла­та. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 — то в начале периода

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

5. Теперь, зная общую сумму выплат и размер ссуды, вычислите общую сумму комиссионных.

Задача 2

Вы хотите взять ссуду 10000 рублей на 1 год с ежемесячной выплатой. Вы готовы по прошествии каждого месяца платить в течение года по 900 рублей. Какой должна быть процентная ставка? Эта задача может быть решена подбором параметра.

	A	B
1	Годовая процентная ставка	8,0%
2	Размер ссуды	10 000р.
3	Число периодов выплат по займу	12
4	Периодические выплаты	1 327р.

Указания

1. Постройте таблицу как показано выше.

2. Ячейка В1 будет играть роль регулируемой ячейки, в которой вы получите результат поиска искомого значения процентной ставки. В начале же в эту ячейку запишите любое значение, например 8,0%.

3. В ячейке В2 запишите размер ссуды, в ячейке В3 – число периодов выплат (12 месяцев), а в ячейке В4 вычислите сумму периодического платежа, с помощью функции ПЛТ.

4. Выделите ячейку В4 таблицы с формулой.

5. Выполните команду

Данные > Работа с данными > Анализ «Что-Если» > Подбор параметра.

Появится диалоговое окно Подбор параметра. В поле «Установить в ячейке» уже будет находиться адрес выделенной на предыдущем шаге ячейки с формулой.

6. В поле «Значение» введите величину, которой должна быть равна ежемесячная выплата в результате подбора значения процентной ставки. В рассматриваемом примере это значение 900.

7. В поле «Изменяя значение ячейки» введем ссылку на исходную ячейку В1 (можно просто щелкнуть по этой ячейке), влияющую на результат вычислений по формуле.

8. Щелкните на кнопке ОК. Появиться диалоговое окно Результат подбора параметра. Щелкните на кнопке ОК. Искомое значение процентной ставки будет подставлено в ячейку В1.

Самостоятельно «Другие финансовые функции»

Пример. Ссуда в 20 ООО долларов дана на полтора года под ставку 28% годовых с ежеквартальным начислением. Определить сумму конечного платежа.

Решение. Здесь базовый период — квартал. Срок ссуды составляет 6 периодов (4 квартала в году, срок полтора года) за период начисляется 7% = 28% / 4.

Тогда формула, дающая решение задачи, имеет вид:

=БС(28%/4, 4*1,5, , 20000). Она возвращает результат -$30 014,61.

Задание 1.

Сумма 2000 размещена под 9% годовых на 3 года. Проценты начисляются раз в квартал. Какая сумма будет на счёте через 3 года?

Задание 2.

Гражданин взял ссуду $2000 под 5% в месяц и обязуется возвращать банку $100 ежемесячно. Почему гражданин не сможет расплатиться с банком?

Задание 3.

Какова сумма долга через 26 месяцев, если его первоначальная величина 500 000 долларов, ставка 20% годовых, начисление поквартальное?

Задание 4.

Банк принимает вклад на срок 3 месяца с объявленной годовой ставкой 100% или на 6 месяцев под 110%. Как выгоднее вкладывать деньги на полгода: дважды на три месяца или один раз на 6 месяцев?

Задание 5.

Рассчитать будущее значение вклада 1000 долларов через 0, 1, 2...5 при годовых процентных ставках 10%, 20%,..., 50%. Дополнительные поступления и выплаты отсутствуют. Решение оформить в виде таблицы.

Построить семейство графиков зависимости будущего значения от срока.

Задание 6.

На счёт в банке вносится сумма 10 000 долларов в течении 10 лет равными долями в конце каждого года. Годовая ставка 4%.

• Какая сумма будет на счёте после 10 лет?

• Какая сумма будет на счёте после 10 лет, если сумма вносится в начале каждого года?

Задание 7.

Рассматриваются две схемы вложение денег на 3 года:

• Вначале каждого года под 24% годовых

• В конце каждого года под 36% ежегодно вносится по $4000.

Какая схема выгоднее?

Задание 8.

За какой срок в годах сумма, равная 75 000 долларов, достигнет $200 000, при начислении процентов по сложной ставке 15%

• раз в году;

• поквартально?

Подсказка. Воспользуйтесь функцией

КПЕР(норма, выплата, начальное_значение, будущее_значение, тип)

Обратите внимание, что нет необходимости набирать все нули в начальной и конечной сумме — достаточно сохранить между ними пропорциональность.

Задание 9.

Ссуда 63 200 рублей, выданная под 32% погашается ежеквартальными платежами по 8400 рублей. Рассчитайте срок погашения ссуды.

Задание 10.

Пусть в долг на полтора года дана сумма 2000 долларов с условием возврата $3000. Вычислить годовую процентную ставку.

Подсказка. Воспользуйтесь функцией

СТАВКА(количество_периодов, выплата,, начальное_значение, будушее_значение, тип, начальное приближение)

Функция возвращает процентную ставку за один период. Начальное приближение по умолчанию составляет 10%.

Занятие 12 (Выборка и фильтрация)

Откройте файл автомобили.xls. Добавить еще 2Листа.

1. Переименуйте Лист 1в «Исходная Таблица»

• удалите пустую строку под заголовками

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

• Обратите внимание, что при задании стиля автоматически устанавливаются автофильтры. Кроме того, автофильтр можно установить в панели

Главная > Редактирование > Сортировка и фильтр

• заполните столбец Цена (руб) (в соответствии с курсом рубля). Если это необходимо, выполните Автоподбор ширины Столбца.

• по центру расположить значения столбца E.

2. Переименуйте Лист 2в«Автофильтр», аЛист 3в«Расширенный фильтр»

3. Используя Автофильтрыв исходной таблице, сделайте следующие выборки:

• В столбце с марками автомобилей выберите ВАЗ-2106 и Москвич-2141,

Скопируйте полученную таблицу вместе с шапкой на лист «Автофильтр»,

Восстановите показ всей исходной таблицы.

• В столбце «Цена (дол.)» выделите с помощью АвтофильтраЧисловой фильтрпервые 5 автомобилей с наименьшей ценой

Скопируйте полученную таблицу вместе с шапкой на лист «Автофильтр» ниже предыдущей таблицы,

Восстановите показ всей исходной таблицы.

• В столбце «Дата» определите машины, зарегистрированные 9 сентября.

Скопируйте полученную таблицу вместе с шапкой на лист «Автофильтр»,

Восстановите показ всей исходной таблицы.

• С помощью Автофильтра выберите строки по следующим условиям:

  • марка машины ВАЗ (любой модели);

  • стоимостью дешевле 1200, но дороже 700 долларов;

  • состояние – «отл» или «идеал»

Скопируйте полученную таблицу вместе с шапкой на лист «Автофильтр»,

Восстановите показ всей исходной таблицы.

4. Используя Расширенный фильтрв исходной таблице, сделате выборку по следующим условиям:

• если это Москвич старый до 1995 то платить за него не более 500$;

• если речь пойдет о более современном (после 1995) Москвиче, то устроит любая цена.

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

Марка автомобиля	Год выпуска	Цена (дол.)
Москвич	>=1995
Москвич	<1995	<=500

Примените расширенный фильтр, указав в качестве диапазона условий построенную таблицу

Данные > Сортировка и фильтр > Дополнительно.

Скопируйте полученную таблицу вместе с шапкой на лист «Расширенный фильтр»,

Восстановите показ всей исходной таблицы.

5. Самостоятельно выберите данные по следующим условиям:

• ВАЗ после 1995 г. подойдет любой модели, в хорошем состоянии не дороже 1200$, но не белого цвета, и пробег чтобы был меньше сотни.

Скопируйте полученную таблицу вместе с шапкой на лист «Автофильтр»,

Восстановите показ всей исходной таблицы.

Занятие 13 (Составление итоговых отчетов)

1. Откройте таблицу из файла «Население».Плотность населениявычислите сами.

2. Отсортируйте данные вначале по полю Полушарие, затем по полюЧасть светаи по полюСтрана.

   Полушарие	Часть света	Страна	Площадь, тыс. кв. км	Население, тыс. чел.	Плотность населения, чел. / кв. км
   Восточное	Азия	Вьетнам	331,7	60863
   Восточное	Азия	Китай	9 597,0	1317000
   Восточное	Азия	Монголия	1 566,5	1866
   Восточное	Азия	Япония	372,0	120030
   Восточное	Европа	Дания	44,5	5111
   Восточное	Европа	Швеция	450,0	8359
   Западное	Африка	Гвинея	246,0	5290
   Западное	Африка	Либерия	111,0	22200
   Западное	Африка	Сенегал	196,0	6600
   Западное	Юж. Америка	Бразилия	8 512,0	135560
   Западное	Юж. Америка	Перу	12 285,0	19700
   Западное	Юж. Америка	Уругвай	176,0	2947
   Западное	Юж. Америка	Чили	757,0	12470

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

Для этого сделайте копию первого листа, выделите таблицу и выполните операцию ИТОГИиз панелиДАННЫЕ > СТРУКТУРАс указанными ниже параметрами:

1. При каждом изменении в Полушарие

2. Операция Сумма

3. Добавить итоги по Площадь, Население

Полушарие	Часть света	Страна	Площадь, тыс. кв. км	Население, тыс. чел.	Плотность населения, чел. / кв. км
Восточное	Азия	Вьетнам	331,7	60863	183,49
Восточное	Азия	Китай	9 597,0	1317000	137,23
Восточное	Азия	Монголия	1 566,5	1866	1,19
Восточное	Азия	Япония	372,0	120030	322,66
Восточное	Европа	Дания	44,5	5111	114,85
Восточное	Европа	Швеция	450,0	8359	18,58
Восточное Итог			12 361,7	1513229
Западное	Африка	Гвинея	246,0	5290	21,50
Западное	Африка	Либерия	111,0	22200	200,00
Западное	Африка	Сенегал	196,0	6600	33,67
Западное	Юж. Америка	Бразилия	8 512,0	135560	15,93
Западное	Юж. Америка	Перу	12 285,0	19700	1,60
Западное	Юж. Америка	Уругвай	176,0	2947	16,74
Западное	Юж. Америка	Чили	757,0	12470	16,47
Западное Итог			22 283,0	204767
Общий итог			34 644,7	1717996

4. Определите средние значение плотности населения для каждого полушария.

   Полушарие	Часть света	Страна	Площадь, тыс. кв. км	Население, тыс. чел.	Плотность населения, чел. / кв. км
   Восточное	Азия	Вьетнам	331,7	60863	183,49
   Восточное	Азия	Китай	9 597,0	1317000	137,23
   Восточное	Азия	Монголия	1 566,5	1866	1,19
   Восточное	Азия	Япония	372,0	120030	322,66
   Восточное	Европа	Дания	44,5	5111	114,85
   Восточное	Европа	Швеция	450,0	8359	18,58
   Восточное Среднее					129,67
   Западное	Африка	Гвинея	246,0	5290	21,50
   Западное	Африка	Либерия	111,0	22200	200,00
   Западное	Африка	Сенегал	196,0	6600	33,67
   Западное	Юж. Америка	Бразилия	8 512,0	135560	15,93
   Западное	Юж. Америка	Перу	12 285,0	19700	1,60
   Западное	Юж. Америка	Уругвай	176,0	2947	16,74
   Западное	Юж. Америка	Чили	757,0	12470	16,47
   Западное Среднее					43,70
   Общее среднее					83,38

5. Рассчитайте общую площадь и общее число жителей для каждой части света.

   Полушарие	Часть света	Страна	Площадь, тыс. кв. км		Население, тыс. чел.		Плотность населения, чел. / кв. км
   Восточное	Азия	Вьетнам	331,7		60863		183,49
   Восточное	Азия	Китай	9 597,0		1317000		137,23
   Восточное	Азия	Монголия	1 566,5		1866		1,19
   Восточное	Азия	Япония	372,0		120030		322,66
   	Азия Итог		11 867,2		1499759
   Восточное	Европа	Дания	44,5		5111		114,85
   Восточное	Европа	Швеция	450,0		8359		18,58
   	Европа Итог		494,5		13470
   Западное	Африка	Гвинея	246,0		5290		21,50
   Западное	Африка	Либерия	111,0		22200		200,00
   Западное	Африка	Сенегал	196,0		6600		33,67
   	Африка Итог		553,0		34090
   Западное	Юж. Америка	Бразилия	8 512,0		135560		15,93
   Западное	Юж. Америка	Перу	12 285,0		19700		1,60
   Западное	Юж. Америка	Уругвай	176,0		2947		16,74
   Западное	Юж. Америка	Чили	757,0		12470		16,47
   	Юж. Америка Итог			21 730,0		170677
   	Общий итог		34 644,7		1717996

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

7. Постройте на втором листе сводную таблицу, указав в качестве столбцов данные из поля «Полушарие», строк – данные из поля«Страна», а в качестве элементов данных укажите значения из поля«Площадь».

Запустите мастер сводных таблиц (Вставка в группе Таблицы раздел Сводная таблица). Сводная таблица строится в несколько этапов, на каждом этапе открывается диалоговое окно. Вам необходимо, отвечая на вопросы диалоговых окон, задать диапазон исходных данных, место расположения сводной таблицы и указать в макете имена столбцов, строк и определить какие данные должны отображаться в сводной таблице.

Сумма по полю Площадь, тыс. кв. км	Полушарие
Страна	Восточное	Западное	Общий итог
Бразилия		8512	8512
Вьетнам	331,7		331,7
Гвинея		246	246
Дания	44,5		44,5
Китай	9597		9597
Либерия		111	111
Монголия	1566,5		1566,5
Перу		12285	12285
Сенегал		196	196
Уругвай		176	176
Чили		757	757
Швеция	450		450
Япония	372		372
Общий итог	12361,7	22283	34644,7

Занятие 14 (Контрольная работа)

Вариант №1

Создайте книгу в Excelи оформите в ней таблицу «Фильмы», пользуясь следующими указаниями.

1. Количество посетителей задайте произвольно.

2. ВЫРУЧКУ посчитайте, используя формулу и автозаполнение.

3. ИТОГО посчитайте, используя функцию.

4. Столбец ЗАКЛЮЧЕНИЕ заполните, используя логическую функцию ЕСЛИ следующим образом:

• если ВЫРУЧКА < 5000р., то "Фильм снять с проката"

• если 5000  ВЫРУЧКА < 15000р., то "Подготовить замену фильму в течение 3-х дней"

• если ВЫРУЧКА  15000р., то "Еще неделю фильм будет на экране "

5. Отформатируйте таблицу как можно ближе к оригиналу.

6. Задайте условное форматирование на столбец ВЫРУЧКА:

• если ВЫРУЧКА < 5000р., то на черном фоне белые буквы и красная граница;

• если 5000 ВЫРУЧКА < 15000р., то на красном фоне синие буквы;

• если ВЫРУЧКА  15000р., то на зеленом фоне черные буквы.

7. По столбцу ВЫРУЧКА постройте на отдельном листе столбчатую диаграмму: "Доходы от фильмов за день", используя те же цвета, что и при условном форматировании.

ФИЛЬМ	сеансы				ВЫРУЧКА		Заключение
	УТРЕННИЙ	ДНЕВНОЙ	ВЕЧЕРНИЙ
	цена билета
	50р.	70р.	100р.
	КОЛИЧЕСТВО ПОСЕТИТЕЛЕЙ
Вместе	23	54	67	11 630р.		..?..
Герой	…	…	…	..?..		..?..
Последний император	…	…	…	..?..		..?..
Красный гаолян	…	…	…	..?..		..?..
	Итого выручка за день

Вариант №2

Годы	Численность населения, млн. человек	Прирост населения, %
1950	551,9	-
1955	614,6	11,4%
1960	662,1	7,7%
1965	725,4	9,6%
1970	829,9	14,4%
1975	924,2	11,4%
1980	987,1	6,8%
1985	1048	6,2%
1990	1160	10,7%
1992	1205,1	3,9%
2000	1309,7	8,7%
2005	1539,7	17,6%
Прогноз на	2173,8	Экспоненциальное приближение
2025	1770,4	Линейное приближение

Используйте следующие функции:

Линейная функция: ПРЕДСКАЗ(X; Изв_Y; Изв_Х)

Экспоненциальная функция: РОСТ (Изв_Y; Изв_Х; Х)

где Х – элемент данных, для которых предсказывается значение,

Изв_Y – диапазон известных значений,

Изв_Х – диапазон данных для известных значений.

Вариант №3 Бюджет поездки ПЕКИН - ПИНЬЯО(平遥) – ДАТОН(大同) (9 дней)
				Курс валют:					Юани		Доллары
									3,55		28,83
		Цена		Денеж. ед.		Кол-во дней		Стоимость		В рублях
Виза			90		д		1		?		?
Дорога
	Владивосток - Тяньцзинь - Владивосток	13020		р		1		?		?
	Пекина - Пиньяо	20		д		1		?		?
	Пиньяо - Датон	22		д		1		?		?
	Датон - Пекин	12		д		1		?		?
Гостиница
	Пекин - Downtown Backpackers	50		ю		3		?		?
	Пиньяо - CHANGXINGLONG	25		д		3		?		?
	Датон - Star Rating	100		ю		3		?		?
Питание
	В расчете на один день	70		ю		9		?		?
Экскурсии
	Парк Бейхай	10		ю		1		?		?
	Монастырь Юнхэгун	50		ю		1		?		?
	Летний дворец	30		ю		1		?		?
	Барабанная башня	10		ю		1		?		?
	Пиньяо - единый билет	120		ю		1		?		?
	Висячий монастырь Сюанькунсы	60		ю		1		?		?
	Каменные пещеры Юньган	60		ю		1		?		?
	Итого:	0,00р.				$0,00

Занятие 15 (Анализ данных и составление отчетов)

«Сезонность продаж»

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

Откройте файл с данными о продажах:

При помощи сводной таблицыможно высчитать нужные коэффициенты сезонности. Для построения сводной таблицы установите активную ячейку в список с данными и откройте менюВставка - Сводная таблица. В открывшемся окне можно сразу нажать кнопкуГотовои перейти к конструированию сводной таблицы с помощью макета:

Перетащите поле Датав область строк, а полеСтоимостьпродажи в область элементов данных. Excel просуммирует все стоимости продаж по дням:

Теперь необходимо отобрать данные только за один последний год и сгруппировать их по месяцам. Для этого щелкаем правой кнопкой мыши по серому полю Дата в сводной таблице и выбираем из контекстного менюГруппа и структура - Группировать:

 В открывшемся окне вводим начальную и конечные даты для отбора и шаг группировки:

Теперь в сводной таблице будут вычисляться суммы продаж за каждый месяц 2006 года + отдельной строкой будут показаны все продажи до 2006 года:

Чтобы убрать лишнюю строку можно щелкнуть по разворачивающему черному треугольнику в заголовке столбца Датаи снять лишний флажок:

Превратим долларовые помесячные суммы в долевые коэффициенты. Для этого щелкнем правой кнопкой мыши по любому значению в столбце Итоги выберем командуПараметры поля.

В открывшемся окне нажмем кнопку Дополнительнои выберем из выпадающего спискаДополнительные вычислениявариантДоля от общей суммы:

Можно еще щелкнуть правой кнопкой мыши по сводной таблице и построить сводную диаграмму:

САМОСТОЯТЕЛЬНО «Отчетная ведомость по магазинам».

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

В столбцах «Магазин», «Июнь», «Июль», «Август» внесите исходные данные.

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

Для нахождения места магазина по объему продаж спользуйте функцию РАНГ.

С помощью функции ЧАСТОТАподсчитаем для данного множества суммарных выручек магазинов, сколько значений попадает в интервалы от 0 до 1000, от 1001 до 1100, от 1101 до 1200 и свыше 1201 млн. руб. С этой целью в диапазон ячеек 14:16 введем верхние границы этих интервалов: 1000, 1100 и 1200, соответственно, а в диапазон ячеек J4:J7 введем формулу:

{=ЧАСТОТА(Е4:E9;I4:I6)}

Фигурные скобочки не вводите вручную. После того как Вы наберете функцию нажмите одновременно три клавиши: Ctrl+Shift+Enter и скобки появяться автоматически. Если в последующем Вы решите внести изменения в функцию, то после обязательно, нажмите эти же клавиши, иначе у Вас появиться сообщение об ошибке.

Функция ЧАСТОТАвозвращает распределение частот в виде вертикального массива. Для данного множества значений и данного множества карманов (интервалов, в математическом смысле) частотное распределение подсчитывает, сколько исходных значений попадает в каждый интервал.

Синтаксис: ЧАСТОТА(массив_данных; массив_карманов).

Аргументы: массив_данных – массив или ссылка на множество данных, для которых вычисляются частоты; если массив_данных не содержит значений, то функция ЧАСТОТА возвращает массив нулей; массив_карманов – массив или ссылка на множество интервалов, в которые группируются значения аргумента массив_данных; если массив_карманов не содержит значений, то функция ЧАСТОТА возвращает количество элементов в аргументе массив данных.

Частоты можно также вычислить, воспользовавшись диалоговым окном Анализ данных,которое открывается командойСервис / Анализ данных.Средство анализа данных является одной из надстроек Excel. Если в меню Сервис отсутствует команда Анализ данных, то для ее установки нужно выполнить команду Сервис / Надстройки.

После выбора пункта Гистограмма в диалоговом окне Анализ данных откроется диалоговое окно Гистограмма.

В поле Входной интервал введем диапазон Е4:Е9, по которому строим гистограмму. В поле Интервал карманов введем диапазон I4:I6 со значениями верхних границ интервалов. Гистограмма строится на новом рабочем листе или на текущем листе с указанием диапазона ячеек для результата. В данном случае в поле ввода Выходной интервал введем диапазон L4:L7.

САМОСТОЯТЕЛЬНО «Календарные графики в Excel (диаграмма Ганта)»

Способ 1.Используем условное форматирование

При помощи условного форматирования мы можем заставить Excel заливать ячейку любым выбранным цветом, если она по дате попадает между началом и концом этапа. Проще всего для этого использовать логическую функцию И, которая в данном случае проверяет обязательное выполнение обоих условий (5 января позже, чем 4-е и раньше, чем 8-е):

Способ 2.Используем диаграмму

Имеем таблицу с перечислением этапов проекта, датами начала и конца и длительносями каждого этапа:

Задача - построить стандартными средствами диаграмму-календарный график, как на рисунке:

Выделим исходные данные для диаграммы - диапазон A2:B13и выберем в менюВставка - Диаграмма, тип -Линейчатаяс накоплением:

Нажимаем на кнопку Далееи уходим на 2-й шаг Мастера диаграмм. На вкладкеДиапазон данныхвыбираемРяды в столбцах:

На вкладке Ряднажмем кнопкуДобавить, устанавливаем курсор в полеЗначенияи выделяем ячейки с длительностями этапов (C2:C13):

На третьем шаге Мастера на вкладке Легендаснимаем флажокДобавить легенду. Все - жмемГотово. Должно получиться примерно следующее:

Щелкаем правой кнопкой мыши по вертикальной оси с названиями этапов и выбираем в контекстном меню Формат оси:

На вкладке Шкалав открывшемся окне ставим две "галочки" -Обратный порядок категорийиПересечение с осью Y в максимальной категории. Теперь избавимся от синих столбцов. Сделайте двойной щелчок по любому из них и в открывшемся окне выберите невидимую рамку и прозрачную заливку. Должно получиться следующее:

Осталось правильно настроить диапазон отображаемых на диаграмме данных. Для этого необходимо узнать реальное содержимое ячеек с которых начинается и на которых заканчивается временная шкала. Дело в том, что Excel только отображает в ячейке дату как день-месяц-год, а на самом деле любую дату хранит в ячейке как количество дней, прошедших с 1.1.1900 до текущей даты. Выделите нужные ячейки и по-очереди попробуйте установить для них Общий формат (меню Формат - Ячейки). Получится 38350 и 38427, соответственно. Накинем на дату окончания еще денька три - получим 38340. Запомните эти числа.

Осталось щелкнуть правой кнопкой мыши по горизонтальной оси времени и выбрать Формат осии ввести эти числа на вкладкуШкала:

После нажатия ОКдиаграмма примет требуемый вид:

Осталось настроить цвета, шрифты, подписи осей и прочее.

Занятие 16 Консолидация (объединение) данных из нескольких таблиц в одну

Задание 1 Если таблицы одинаковые

Имеем несколько однотипных таблиц на разных листах. Например, вот такие:

 

Необходимо объединить их все в одну общую таблицу, просуммировав совпадающие значения по кварталам и наименованиям.

Способ 1.Простые формулы

Самый простой способ. Ввести в ячейку чистого листа формулу

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

и скопировать ее на четыре ячейки вправо и на четыре вниз

Способ 2.Трехмерные формулы

 Чуть более изящный способ, чем предыдущий. Формула может выглядеть немного иначе:

=СУММ('2001 год:2003 год'!B3)

Фактически - это суммирование всех ячеек B3 на листах с 2001 по 2003. То есть, в будущем, возможно поместить между этими листами дополнительные листы с данными, которые также станут учитываться при суммировании.

Задание 2 Если имеем три разных файла(John.xls, Rita.xls и Stiven.xls) с тремя таблицами:

Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.

Для того, чтобы запустить консолидиацию, сначала заранее откройте файлы John.xls, Rita.xls и Stiven.xls. Затем создайте новую пустую книгу, установите в нее активную ячейку и выберите в меню Данные - Консолидация. Откроется окно:

Установите курсор в строку Ссылка и, переключившись в файл John.xls через меню Окно, выделите таблицу Джона, затем нажмите кнопку Добавить в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов. Теперь повторите эти же действия для файлов Риты и Стивена. В итоге в списке должны оказаться три диапазона наших трех сотрудников:

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

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

После нажатия на ОК видим такую картину:

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