Пример 1

СУММ(3; 2) равняется 5.

Пример 2

СУММ("3"; 2; ИСТИНА) равняется 6, так как текстовые значения преобразуются в числа, а логическое значение ИСТИНА преобразуется в число 1.

Если ячейка A1 содержит "3", а ячейка B1 содержит ИСТИНА, то:

СУММ(A1; B1; 2) равняется 2, так как нечисловые значения в ссылке не преобразуются.

Пример 3

Если ячейки A2:E2 содержат числа 5, 15, 30, 40 и 50, то:

СУММ(A2:C2) равняется 50

СУММ(B2:E2; 15) равняется 150.

2.12.6. Функция СУММЕСЛИ

Суммирует ячейки, специфицированные заданным критерием.

Синтаксис:

СУММЕСЛИ(интервал; критерий;сумм_интервал)

Интервал это интервал вычисляемых ячеек.

Критерий это критерий в форме числа, выражения или текста, который определяет, какая ячейка добавляется. Например, критерий может быть выражен как 32, "32", ">32", "яблоки".

Сумм_интервал это фактические ячейки для суммирования. Ячейки в сумм_интервал суммируются, только если соответствующие им ячейки в аргументе интервал удовлетворяют критерий.   Если сумм_интервал опущен, то суммируются ячейки в аргументе интервал.

Пример

Пусть ячейки A1:A4 содержат следующие величины стоимости для четырех домов: 100000 грн., 200000 грн., 300000 грн., 400000 грн. соответственно. Пусть ячейки B1:B4 содержат следующие величины комиссионных при продаже соответствующих домов: 7000 грн., 14000 грн., 21 000 грн., 28 000 грн.

СУММЕСЛИ(A1:A4;">160000";B1:B4) равняется 63 000 грн.

2.12.7. Функция СЧЕТ

Подсчитывает количество чисел в списке аргументов. Функция СЧЁТ используется для получения количества числовых ячеек в интервалах или массивах ячеек.

Синтаксис:

СЧЁТ(значение1; значение2; ...)

Значение1, значение2, ... - это от 1 до 30 аргументов,  которые могут содержать или ссылаться на данные различных типов, но в подсчете участвуют только числа.

Учитываются аргументы, которые являются числами, пустыми значениями, логическими значениями, датами или текстами, изображающими числа; аргументы, которые являются значениями ошибки или текстами, которые нельзя интерпретировать как числа, игнорируются.

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

Пример

Для таблицы вида

СЧЁТ(A1:A7) возвращает 3

СЧЁТ(A4:A7) возвращает 2

СЧЁТ(A1:A7, 2) возвращает 4

2.12.8. Функция СЧЕТЕСЛИ

Подсчитывает количество ячеек внутри интервала, удовлетворяющих заданному критерию.

Синтаксис:

СЧЁТЕСЛИ(интервал;критерий)

Интервал - это интервал, в котором нужно подсчитать ячейки.

Критерий - это критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки".

Пример 1

Пусть ячейки A3:A6 содержат "яблоки", "апельсины", "персики", "яблоки" соответственно. СЧЁТЕСЛИ(A3:A6;"яблоки") равняется 2.

Пример 2

Пусть ячейки B3:B6 содержат 32, 54, 75, 86 соответственно. СЧЁТЕСЛИ(B3:B6;">55") равняется 2.

Приведем пример использования некоторых из описанных выше функций (рис. 11).

Рис. 11. Пример использования функций

Наберите таблицу, представленную на рис. 11. Надпись «Наименование товара» выполните следующим образом: в ячейке А2 наберите Наименование товара; нажмите <Enter>; выделите ячейки А2:А3; выполните команду Ячейки => Формат; в открывшемся окне Форматирование ячеек выберите вкладку Выравнивание и установите на ней флажки Объединение ячеек и Переносить по словам; нажмите Ok.

Надпись «Реквизиты продавца» выполните следующим образом: выделите ячейки В2:С2; выполните команду Ячейки => Формат; в открывшемся окне Форматирование ячеек выберите вкладку Выравнивание и установите на ней флажок Объединение ячеек; выравнивание по горизонтали – по центру; выравнивание по вертикали – по центру. Аналогично выполняется надпись «Расчеты». Для заданных в таблице данных в ячейках записаны формулы для следующих расчетов:

– в В12 – сколько фактов реализации продавцом Иванов отмечено

=СЧЁТЕСЛИ(B4:B10;"Иванов")

– в D12 – максимальная цена

=МАКС(D4:D10)

– в Е12 – общее количество товара компьютер

=СУММЕСЛИ(A4:A10;"компьютер";Е4:Е10)

2.13. Графические возможности Excel

2.13.1. Построение диаграмм

Диаграммы служат для наглядного представления табличных данных.

Рассмотрим построение диаграммы на примере по данным таблицы рис.11.

Пример

Построить круговую диаграмму по данным столбца «Количество» относительно данных столбца «Наименование товара». Легенда – данные столбца «Наименование». Значения областей диаграммы должны быть выражены в процентах.

Для построения диаграммы необходимо:

1. Выделить диапазон ячеек, по которым будет построена диаграмма. В данном случае необходимо выделить ячейки В3:В10, а затем, удерживая нажатой клавишу <CTRL>, выделить F3:F10.

2. Щелкнуть в панели инструментов Стандартная кнопку или вызвать командуВставка => Диаграмма.

3. В открывшемся окне Мастер диаграмм (шаг 1 из 4) на вкладке Стандартные выбрать тип диаграммы Круговая и понравившийся вид; щелкнуть по кнопке окна Далее> (рис. 12).

4. В открывшемся окне Мастер диаграмм (шаг 2 из 4) проверить, правильно ли указан Диапазон (должен быть В3:В10; F3:F10); переключатель Ряды в: должен находиться в положении столбцах. Щелкнуть по кнопке окна Далее> (рис. 13).

5. В открывшемся окне Мастер диаграмм (шаг 3 из 4):

- на вкладке Заголовки в поле «Название диаграммы» можно ввести название диаграммы (в примере введено «Круговая диаграмма»);

- на вкладке Легенда можно задать параметры легенды (пояснений по обозначениям);

- на вкладке Подписи данных задать параметры подписей данных; в примере в группе Подписи значений установлен переключатель Доля (рис. 14).

Щелкнуть по кнопке окна Далее>.

Рис. 12. Окно Мастер диаграмм (шаг 1 из 4)

Рис. 13. Окно Мастер диаграмм (шаг 2 из 4)

Рис. 14. Окно Мастер диаграмм (шаг 3 из 4)

 

Рис. 15. Окно Мастер диаграмм (шаг 4 из 4)

6. 6. В открывшемся окне Мастер диаграмм (шаг 4 из 4): установить переключатель Поместить диаграмму на листе: в нужное положение и щелкнуть по кнопке окна Готово (рис. 15).

2.13.2. Редактирование диаграммы

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

- либо щелкнуть по ней правой кнопкой мыши (например, на заголовке – рис. 16). В открывшемся контекстном меню щелкнуть левой кнопкой мыши команду Формат... Открывается окно форматирования выбранной области;

- либо дважды щелкнуть левой кнопкой мыши объект, который необходимо отредактировать.

Рис. 16. Форматирование области заголовка

2. ЗАДАНИЯ К ЛАБОРАТОРНОЙ РАБОТЕ

Задание №1 к лабораторной работе

Задача 1

1) Составить таблицу (рис. 17) с указанными ниже параметрами:

- наименование таблицы выполнить методом объединения ячеек по центру;

- шапка таблицы и итоговая строка должны быть выделены жирным обрамлением;

- заголовки Наименование товара, Реквизиты продавца и Расчеты должны быть оформлены методом объединения ячеек с центрированием по горизонтали;

- ячейки под заголовками столбцов Цена, Доход, Расход, Прибыль должны иметь денежный формат с числом знаков после запятой 2 и обозначением грн.;

- ячейки под заголовками столбцов № счета и Кол-во должны иметь числовой формат;

• остальные ячейки должны иметь текстовый формат.

Рис. 17. Вид таблицы

2) Рассчитать ячейки под заголовками столбцов Доход, Расход, Прибыль и итоговую строку:

-   доход рассчитывается как произведение Цены и Количества;

-   Расход равен 42% от Дохода;

-   Прибыль рассчитывается как разность Дохода и Расхода;

- итоговая строка рассчитывается по столбцам Доход, Расход и Прибыль.

3) Построить кольцевую диаграмму по данным столбца Прибыль относительно данных столбца Наименование товара. Легенда – данные столбца Наименование товара. Значения областей диаграммы должны быть выражены в процентах.

Задача 2

Рассчитать заработную плату десяти работников отдела, исходя из следующих данных: почасовая ставка для работников в зависимости от категорий следующая – 3-я категория – 5,9 грн./час; 2-я категория – 12,4 грн./час; 1-я категория – 25,5 грн./час.

В отделе работают пять работников третьей категории, три работника второй категории  и два работника первой категории. Один из работников первой категории (Павлов В.В.) отработал 80 часов, а другой (Заморин А.Р.) – 100 часов. Два работника второй категории (Иванов Ю.П. и Петров А.А.) отработали в месяц 120 часов каждый, а третий – Ступин А.В. – 50 часов. Работники третьей категории отработали соответственно: Сидоров П.Л. – 140, Дюков А.А. – 105, Панин Р.А. – 105, Козлов С.П. – 80, Салин Е.Л. – 110 часов.

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

Задача 3

Рассчитать среднюю плату за пребывание ребенка в детском саду в 2001, 2002, 2003 гг., учитывая, что по месяцам она составила: 2001 г. – с 1.01 по 30.04 – 8 грн. за месяц, с 1.05 по 31.08 – 15 грн. за месяц, с 1.09 по 31.12 – 40 грн. за месяц; 2002 г. – с 1.01 по 31.08 – 55 грн. за месяц, с 1.09 по 31.12 – 70 грн. за месяц; 2003 г. – с 1.01 по 31.07 – 90 грн. за месяц; с 1.08 по 31.12 – 125 грн. за месяц.

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

Задача 4

Рассчитать плату за электроэнергию в 2003 и в 2004 годах, если норма в 2003 г. составила 0,11 грн. за киловатт, а в 2004 г. – 0,156 грн. за киловатт.

Расход электроэнергии жильцами восьмиквартирного дома составил: квартира 1 – 120 Квт/мес., квартира 2 – 80 Квт/мес., квартира 3 – 150 Квт/мес., квартира 4 – 200 Квт/мес., квартира 5 – 120 Квт/мес., квартира 6 – 75 Квт/мес., квартира 7 – 160 Квт/мес., квартира 8 – 270 Квт/мес.

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

Задача 5

Рассчитать зарплату работников предприятия в зависимости от отработанного объема часов, если общая сумма выделенная на зарплату составила 25 тысяч грн., подоходный налог составляет 13% от начисленной суммы, 1% составляют выплаты в пенсионный фонд, 2% составляют прочие выплаты.

В отделе 7 работников, отработавшие соответственно: Петров А.Л., – 180 часов, Петров Л.Д. – 20 часов, Сидоров Е.Н. – 80 часов, Павлов А.А. – 120 часов, Лаптев П.Л. – 90 часов, Ванин П.П. – 120 часов, Калмыков М.И. – 100 часов.

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

Задача 6

По приведенным в таблице нормативным показателям составить расчет затрат на создание ВЦ и его эксплуатацию (исходные данные для расчета: стоимость вычислительной техники, размер основной зарплаты и т.п. взять самостоятельно):

№ п/п	Статьи затрат	Примечание
1	Стоимость вычислительной техники
2	Стоимость прочего оборудования	10% от п.1
3	Монтаж и наладка ВТ	10% от п.1
4	Монтаж и пусконаладочные работы вспомогательного оборудования	5% от п.1
5	Среднегодовая заработная плата,
	в том числе: основная
	дополнительная	8% от осн. з/п
6	Амортизацонные отчисления на ВТ	12% от п.1
7	Стоимость ремонта оборудования	2,5% от п.1
8	Возмещение износа быстроизнашиваемых предметов	2% от п.1
9	Стоимость электроэнергии
10	Транспортные расходы	4% от п.1
11	Прочие расходы	2% от п.1

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

Задача 7

Составить таблицу, определяющую размер прибыли предприятия в зависимости от объема реализованной партии товаров. Объем можно задать от 50 единиц через равные интервалы. Отпускаемая цена за единицу изделия стабильна, имеются фиксированные издержки, не зависящие от размера партии товаров, и переменные издержки за единицу реализованной продукции, которые зависят от объема партии.

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

Форма таблицы

ПРИБЫЛЬ В ЗАВИСИМОСТИ ОТ ОБЪЕМА РЕАЛИЗАЦИИ

Отпускная цена за единицу товара   $7,5

Переменные издержки на единицу продукции   $2,5

Размер партии, щтук	Постоянные издержки, $US	Переменные издержки, $US	Общие издержки, $US	Доход от продаж, $US	Прибыли и убытки, $US
50	1250
100	1250
150	1250

Задача 8

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

Таблица должна рассчитывать итоговые издержки за месяц и средний пробег (в км/литр).

Форма таблицы

РАСХОДЫ НА СЛУЖЕБНЫЕ МАШИНЫ

Месяц июль

Число месяца	Отсчет спидометра, км	Куплено бензина, л	Цена за 1 л	Стоимость бензина	Допол. расходы	Общая стоимость	Пробег, км
1	39800	40	9,8
5	40100	20	9,8		50000
10	40300	0	9,8
15	40420	40	9,8		70000
20	40530	20	9,8
25	40680	40	9,8
30	40700	0	9,8

 

Итого к оплате:___________

Средний пробег, км/л:_____

Задача 9

Составить таблицу прейскурантных цен. Она должна содержать сведения о коде изделия, цене нетто, НДС (рассчитывается по ставке НДС), цене с НДС, цене изделия с учетом 10% и 15% скидок постоянным клиентам фирмы.

 Форма таблицы

ПРЕЙСКУРАНТНЫЕ ЦЕНЫ С УЧЕТОМ НДС И СКИДОК

ПОСТОЯННЫМ КЛИЕНТАМ ФИРМЫ

Ставка НДС 20%

Код изделия	Цена нетто	Общая стоимость	Скидка 10%	Цена со скидкой 10%	Скидка 15%	Цена со скидкой 15%
Y340	$20
Y341	$31
Y342	$14
Y343	$22
Y344	$25
Y345	$31
Y346	$39
Y347	$28

Задание №2 к лабораторной работе

1. Составить таблицу (приложение 1), параметры таблицы должны быть максимально приближены к указанным на рисунке.

2. Рассчитать сумму прихода, сумму продажи, прибыль и рентабельность по всем строкам.

3. Вычислить среднюю рентабельность и среднюю прибыль.

4. Вычислить максимальную и минимальную рентабельность.

5. Сколько фактов реализации сыпучих материалов отмечено?

6. На какую сумму реализовано цемента?

7. На какую сумму реализовано цемента белого?

8. Какова суммарная прибыль товаров,  продававшихся по цене >200грн. за единицу?

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

10. Построить график по результатам выполнения задания. Вид графика – гистограмма. Оформление графика выбрать самостоятельно.

11. Рассчитать среднюю рентабельность, только тогда, когда сумма продажи была больше 100 грн.

12. Рассчитать количество фактов реализации товаров, пришедших в количестве более 100 единиц.

13. Вычислить суммарную прибыль товаров, продававшихся в ноябре.

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