СТАТИСТИКА ПРОДАЖИ

5. Установить курсор в ячейку Е3 и нажать кнопку Автосумма (S) на панели инструментов. Программа вставит функцию =СУММ и адрес диапазона левее выделенной ячейки В3:D3.

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

7. Нажать кнопку Ввод или клавишу Enter . Программа вычислит сумму значений в выделенных ячейках.

8. Скопировать формулу ячейки Е3 в ячейки Е4:Е7.

9. Установить курсор в ячейку В8 и нажать кнопку Автосумма (S) на панели инструментов. Программа вставит функцию =СУММ и адрес диапазона выше выделенной ячейки В3:В7.

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

11. Нажать кнопку Ввод или клавишу Enter . Программа вычислит сумму значений в выделенных ячейках.

12. Скопировать формулу В8 в ячейки С8:F8.

13. Закрыть файл с сохранением с помощью команды меню Файл -> Закрыть.

Пример 12. Встроенные функции Microsoft Excel.
Категория «Ссылки и массивы»

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

– номер строки, номер столбца – R1C1;
– имя столбца, номер строки – А1.

Столбцы обозначаются буквами от А до IV (256 столбцов максимально) или цифрами 1-256, строки – числами от 1 до 65 536. Ссылка на диапазон ячеек задается как ссылка на верхний левый угол диапазона, далее ставится знак двоеточия (:), указывается ссылка на правый нижний угол диапазона.

Примеры использования некоторых функций из категории «Ссылки и массивы»:

1. Открыть файл Примеры.XLS с помощью команды меню Файл -> Открыть.

2. Вставить, если необходимо, новый лист с помощью команды меню Вставка ->Лист.

3. Выполнить переименование листа с помощью команды меню Формат -> Лист -> Переименовать, имя листа – Пример 12.

4. Заполнить значения ячеек в диапазоне С2:Е5.

5. Создать именованный блок для диапазона ячеек С2:Е5 с именем Блок с помощью команды меню Вставка -> Имя -> Присвоить.

6. Функция АДРЕС – адрес ячеек или диапазонов ячеек.

Адрес ссылки выдается в виде текста в двойных кавычках. Ссылка представляется в виде номера строки и столбца, на пересечении которых находится ячейка. Ссылка может быть относительной или абсолютной в определенном стиле (А1 или R1C1), включать имя листа рабочей книги.

Формула в ячейке А1 =АДРЕС(4;5;1;2;"Пример 11")дает ссылку на ячейку 'Пример 11'!$Е$4. В формуле использованы параметры (слева направо): 4 – номер строки, 5 – номер столбца, 1 – абсолютная ссылка, 1 – формат ссылки А1, Пример 11 – имя листа.

7. Функция ДВССЫЛ – значение из ссылки.

Ссылка задается в виде текстовой строки. Например, в ячейке А2 содержится формула вида =ДВССЫЛ (АДРЕС(4; 5; 1; 1); 1). Эта формула дает результат – значение из ячейки Е4 (если лист не указан, используется текущий).

8. Функция ЧСТРОК – определение числа строк в заданном диапазоне ячеек. Например, формула в ячейке A3 =ЧСТРОК(С2: Е5) дает значение 4.

9. Функция ЧИСЛСТОЛБ – определение числа столбцов в заданном диапазоне ячеек. Например, формула в ячейке А4 =ЧИСПСТОЛБ (С2: Е5) дает значение 3.

10. Функция СТОЛБЕЦ – определение начального номера столбца ссылки (диапазона ячеек или именованного блока ячеек). Например, формула в ячейке А5 =СТОЛБЕЦ(С2:Е5) дает значение 3.

11. Функция СТРОКА – определение начального номера строки ссылки (диапазона ячеек или именованного блока ячеек). Например, формула в ячейке А6 =СТРОКА(С2:Е5) дает значение 2.

15. Функция ПРОСМОТР – просмотр данных в блоках ячеек. Функция ПРОСМОТР обеспечивает различные режимы поиска: проверка наличия искомого значения в массиве (если значение существует, выводится само значение, в противном случае – сообщение об ошибке #Н/Д – нет данных); поиск искомого значения в векторе просмотра и вывод соответствующего ему значения из вектора результата. Перед выполнением функции ПРОСМОТР диапазон ячеек сортируется в порядке возрастания значений вектора просмотра.

Например, требуется по заданному значению кода материала – ячейка В1 определить соответствующее этому коду название материала. Коды материалов представлены в ячейках С2:С5, названия – в ячейках D2:D5. В ячейке В6 введена формула =ПРОСМОТР(В1;С2:С5;D2:D5), которая возвращает значение из вектора результата (наименование материала) для найденного в векторе просмотра (код материала) значения, в данном случае – Асбест.

19. Функция ТРАНСП – транспонирование диапазона ячеек. Для «разворота» на 90° блока ячеек используется функция ТРАНСП, которая вводится как массив формул. Например, требуется транспонировать исходный блок ячеек С2:Е5 в ячейки A10:D12. В ячейки A10:D12вводится формула массива {=ТРАНСП(С2:Е5)}. Для этого надо выделить ячейки A10:D12, ввести формулу и нажать CTRL + SHIFT + ENTER .

20. Закрыть файл с сохранением с помощью команды меню Файл -> Закрыть.

Пример 13. Встроенные функции Microsoft Excel.
Категория «Текстовые функции»

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

1. Открыть файл Примеры.XLS с помощью команды меню Файл -> Открыть.

2. Вставить, если необходимо, новый лист с помощью команды меню Вставка ->Лист.

3. Выполнить переименование листа с помощью команды меню Формат -> Лист -> Переименовать, имя листа – Пример 13.

4. Заполнить ячейки A1:D1, начиная с A1, значениями:

5. В ячейки A2:D2, начиная с A2, ввести формулы для преобразования числа или даты в текст:

6. В ячейку A3 ввести формулу преобразования текста в число: =ЗНАЧЕН("0,23р."). Формула дает результат 0,23. Это обратное преобразование функции ТЕКСТ.

7. В ячейку B3 ввести формулу сцепления текстовых строк:

=СЦЕПИТЬ("ПРИМЕР";" "; "СЦЕПЛЕНИЯ СТРОК ТЕКСТА";
" ";"ДЛЯ ВЫВОДА В ОДНОЙ ЯЧЕЙКЕ"). В результате в ячейке B3 выводится строка текста «ПРИМЕР СЦЕПЛЕНИЯ СТРОК ТЕКСТА ДЛЯ ВЫВОДА В ОДНОЙ ЯЧЕЙКЕ».

8. В ячейку СЗ ввести формулу для определения длины текста в ячейке B3: =ДЛСТР(B3).

9. В ячейку D3 ввести формулу для определения первого вхождения в строку текста в ячейке B3 сочетания букв «СТ» с учетом регистра, поиск вести с начала строки текста: =НАЙТИ("СТ";B3;1). При поиске без учета регистра используется функция ПОИСК.

10. Закрыть файл с сохранением с помощью команды меню Файл -> Закрыть.

Пример 14. Встроенные функции Microsoft Excel.
Категория «Дата и время»

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

1. Открыть файл Примеры.XLS с помощью команды меню Файл -> Открыть.

2. Вставить, если необходимо, новый лист с помощью команды меню Вставка ->Лист.

3. Выполнить переименование листа с помощью команды меню Формат -> Лист -> Переименовать, имя листа – Пример 14.

4. Ввести в столбец А, начиная с ячейки A1, формулы для вычисления:

– текущей даты: =СЕГОДНЯ();

– текущей даты и времени: =ТДАТА();

– даты в числовом формате, аргументы функции задаются по частям (год, месяц, день): =ДАТА (2002; 4; 12);

– перевод даты из текстового формата в числовой формат, аргумент задается как строка текста: =ДАТАЗНАЧ ("12.04.2002");

– определение номера года, месяца и дня для даты, заданной в числовом формате:
=ГОД(37430) дает год 2002,
=МЕСЯЦ(37430) дает месяц 6,
=ДЕНЬ(37430) дает число 23.

5. Закрыть файл с сохранением с помощью команды меню Файл -> Закрыть.

Пример 15. Встроенные функции Microsoft Excel.
Категория «Математические»

Это наиболее популярная категория встроенных функций, обеспечивающая разнообразные вычисления.

1. Открыть файл Примеры.XLS с помощью команды меню Файл -> Открыть.

2. Вставить, если необходимо, новый лист с помощью команды меню Вставка ->Лист.

3. Выполнить переименование листа с помощью команды меню Формат -> Лист -> Переименовать, имя листа – Пример 15.

4. Ввести в столбец А, начиная с ячейки A1, формулы:

– определение знака выражения: =ЗНАК(-1234) дает -1, так как число отрицательное;

– округление числа до ближайшего целого нечетного числа: =НЕЧЁТ(166,6667) дает 167;

– округление числа до ближайшего целого четного числа: =ЧЁТН(167,6667) дает 168;

– округление числа до ближайшего меньшего целого числа: =ЦЕЛОЕ(166,6667) дает 166;

– отбрасывание дробной части числа: =ОТБР( 166,6667;0) дает 166;

– округление числа до ближайшего целого или до ближайшего кратного указанному значению: =ОКРВВЕРХ(166,6667; 10) вычисляет 170;

– округление числа до ближайшего меньшего подмодулю целого числа: =ОКРВНИЗ(20/120*1000;10) вычисляет 160;

– округление числа до указанного количества десятичных разрядов: =ОКРУГЛ(166,6667;3) вычисляет 166,667;

– округление числа до ближайшего по модулю большего целого: =ОКРУГЛВВЕРХ(166,666667;3) вычисляет 166,667;

– округление числа до ближайшего меньшего по модулю целого: =ОКРУГЛВНИЗ(166,66667;3) вычисляет 166,666.

5. Закрыть файл с сохранением с помощью команды меню Файл -> Закрыть.

Пример 16. Создание списков

Список – это рабочий лист Microsoft Excel со столбцами и строками, с текстом и числами, сформированный по определенным правилам. Если правила соблюдены, то программа будет воспринимать список как двухмернуюбазу данных. Рассмотрим пример на создание списка:

1. Открыть файл Примеры.XLS с помощью команды меню Файл -> Открыть.

2. Вставить, если необходимо, новый лист с помощью команды меню Вставка ->Лист.

3. Выполнить переименование листа с помощью команды меню Формат -> Лист -> Переименовать, имя листа – Объем и сбыт.

4. Набрать список следующего содержания ( шапка таблицы набирается один раз):

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

6. Закрыть файл с сохранением с помощью команды меню Файл -> Закрыть.

Пример 17. Использование формы для работы со списками

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

1. Открыть файл Примеры.XLS с помощью команды меню Файл -> Открыть.

2. Выбрать лист Тарифы (см. пример 2).

3. Снять защиту с ячейки Е2 (ставка 1-го разряда) с помощью команды меню Формат -> Ячейки, на вкладке Защита сбросить Защищаемая ячейка.

4. Заполнить и выделить блок ячеек Ставка.

5. Скрыть формулы в ячейках блока Ставка с помощью команды меню Формат -> Ячейки, на вкладке Защита установить Защищаемая ячейка и Скрыть формулы.

6. Защитить лист Тарифы от изменений с помощью команды меню Сервис -> Защита -> Защитить лист, указать защиту содержимого листа.

7. Выбрать лист Картотека (см. пример 2).

8. Установить курсор в ячейку первой строки столбца Тариф и ввести формулу тарифа в ячейку Е2: =ПРОСМОТР(D2;Разряд;Ставка).

9. Скопировать формулу ячейки Е2 в ячейки столбца Тариф (для заполнения строк списка).

10. Установить курсор в область списка и выполнить команду Данные -> Форма.

11. Ввести следующие новые записи в список с помощью кнопки Добавить:

12. Выполнить отбор записей по условию: профессия Грузчик, разряд работающего выше 3. Нажать кнопку Критерии и заполнить поля формы: поле Профессия – Грузчик; поле Разряд работающего – 3.

13. Нажать кнопку Далее и просмотреть записи, удовлетворяющие запросу.

14. Нажать кнопку Очистить.

15. Выполнить отбор записей по условию: разряд работающего до 4 и льготы больше 2. Нажать кнопку Критерии и заполнить поля формы: поле Разряд работающего – <4; поле Льготы – 2.

16. Нажать кнопку Далее и просмотреть записи, удовлетворяющие запросу.

17. Закрыть файл с сохранением с помощью команды меню Файл -> Закрыть.

Пример 18. Фильтрация списков. Автофильтр

Различают два способа фильтрации (отбора) записей списков в команде меню Данные -> Фильтр: Автофильтр и Расширенный фильтр.

После применения команды Автофильтр в строку таблицы с наименованиями полей будут помещены кнопки раскрывающихся списков (кнопки со стрелками). Каждый список содержит все значения, встречающиеся в этом поле.

1. Открыть файл Примеры.ХLS с помощью команды меню Файл -> Открыть.

2. Выбрать лист Объем и сбыт.

3. Установить курсор в любую ячейку списка, войти в меню Данные-> Фильтр: Автофильтр.

4. Щелкнуть левой клавишей мышки по появившейся стрелке в поле Год.

5. Из списка значений выбрать 1996.

6. Полученную таблицу скопировать на свободное место открытого листа.

7. 4. Щелкнуть левой клавишей мышки по появившейся стрелке в поле Сбыт.

8. Из списка значений выбрать Первые 10.

9. В открывшемся окне Наложения условий по списку установить первые 5 наибольших значений элементов списка.

10. Полученную таблицу скопировать на свободное место открытого листа.

11. Щелкнуть левой клавишей мышки по появившейся стрелке в поле Объем.

12. Из списка значений выбрать Условие.

13. В открывшемся окне выбрать условие больше или равно 3000.

14. Полученную таблицу скопировать на свободное место открытого листа.

15. Закрыть файл с сохранением с помощью команды меню Файл -> Закрыть.

Пример 19. Фильтрация списков. Расширенный фильтр

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

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

1. Открыть файл Примеры.ХLS с помощью команды меню Файл -> Открыть.

2. Выбрать лист Картотека.

3. Создать именованный блок БазаДанных для списка Картотека – команда меню Вставка -> Имя -> Присвоить. В блок входят заголовки столбцов и все заполненные записи списка Microsoft Excel.

4. Вставить новый лист с помощью команды меню Вставка -> Лист.

5. Выполнить переименование листа с помощью команды меню Формат -> Лист -> Переименовать, лист – Критерии.

6. Подготовить диапазон условий для расширенного фильтра на листе Критерии:

Имена полей списка в область критериев целесообразно перенести путем копирования, так как при фильтрации требуется полное совпадение имен полей в списке Microsoft Excel и диапазоне условий.

7. Вставить новый лист с помощью команды меню Вставка -> Лист.

8. Выполнить переименование листа с помощью команды меню Формат -> Лист -> Переименовать, лист – Результат.

9. Установить курсор в ячейку А1 листа Результат.

10. Выполнить команду меню Данные -> Фильтр -> Расширенный фильтр.

11. В диалоговом окне Расширенный фильтр указать:

– Скопировать результат в другое место;

– исходный диапазон – блок БазаДанных;

– диапазон условий – Критерии!$А$1:$D$3;

– поместить результат в диапазон – Результат!$А$1;

– нажать кнопку ОК.

12. Закрыть файл с сохранением с помощью команды меню Файл -> Закрыть.

Пример 20. Промежуточные итоги

Для формирования промежуточных итогов требуется предварительная сортировка списка по полям группировки записей, например, по полям Поле1, Поле2, Поле3. Команда меню Данные -> Итоги позволяет сформировать итоги определенного вида по указанным полям при каждом изменении поля группировки – Поле1 (или Поле2 или Поле3). По отдельному полю группировки за одно выполнение команды можно сформировать итоги по нескольким полям. В Microsoft Excel определены следующие встроенные операции и функции для агрегированных итогов:

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

1. Открыть файл Примеры.XLS с помощью команды меню Файл -> Открыть.

2. Выбрать лист Картотека.

3. Установить курсор в область списка на листе Картотека.

4. Отсортировать список по полю Профессия с помощью команды меню Данные -> Сортировка.

5. Установить курсор в область списка на листе Картотека.

6. Выполнить команду меню Данные -> Итоги:

– При каждом изменении – Профессия.

– Операция – Среднее.

– Добавить итоги по – Разряд работающего, Тариф.

– Установить Итоги под данными.

– Нажать на кнопку ОК.

7. Установить курсор в область списка на листе Картотека.

8. Выполнить команду меню Данные -> Итоги:

– При каждом изменении – Профессия.

– Операция – Кол-во значений.

– Добавить итоги по – Табельный номер.

– Установить Итоги под данными.

– Нажать на кнопку ОК.

9. Проанализировать получившуюся таблицу.

10. Закрыть файл с сохранением – команда меню Файл -> Закрыть.

Пример 21. Создание сводных таблиц

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

Установлена премия по разрядам работающих: 2-й разряд 25%, 3-й разряд 32%, 4-й разряд 50% к тарифу. Вычеты из всех видов начислений составляют 13,9%.

1. Открыть файл Примеры.XLS с помощью команды меню Файл -> Открыть.

2. Выбрать лист Картотека.

3. Установить курсор в список на листе Картотека.

4. Выполнить команду меню Данные -> Сводная таблица для вызова Мастера сводных таблиц и диаграмм.

5. На шаге 1 указать тип источника – Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel. Вид создаваемого отчета – Сводная диаграмма (со сводной таблицей).

6. На шаге 2 проверить диапазон ячеек для построения сводной таблицы списка Картотека.

7. На шаге 3 – нажать кнопку Макет, разместить поля в макете сводной таблицы: страница – Профессия, строка – Разряд работающего, столбец – ФИО, данные – Тариф.

8. Задать параметры сводной таблицы с помощью кнопки Параметры: Общая сумма по столбцам; Автоформат; Сохранять форматирование. Для пустых ячеек отображать – Пробел; Поместить таблицу – в Новый лист.

9. Нажать на кнопку Готово. Сводная таблица представлена на следующем рисунке:

10. Установить курсор в область сводной таблицы.

11. На панели инструментов Сводные таблицы нажать кнопку Сводная таблица, выбрать команду контекстного меню Формулы -> Вычисляемое поле. Создать новое вычисляемое поле: Имя поля – Зарплата, формула: =Тариф*168. Кнопка Добавить. Закрыть окно – кнопка ОК.

12. Установить курсор в область сводной таблицы.

13. Ha панели инструментов Сводные таблицы нажать кнопку Мастер сводных лиц. В окне мастера сводных таблиц на 3-м шаге нажать кнопку Макет: убрать из области Данные поле Сумма по полю Тариф. Нажать кнопку ОК. Нажать кнопку Готово.

14. Установить курсор в области сводной таблицы на поле Сумма по полю Зарплата.

15. На панели инструментов Сводная таблица нажать кнопку Параметры поля: изменить имя поля – Месячная зарплата. Выбрать формат поля – Денежный. Нажать кнопку ОК.

16. Установить курсор в области сводной таблицы на поле ФИО.

17. На панели инструментов Сводные таблицы нажать кнопку Сводная таблица.

18. Выполнить команду контекстного меню Формулы -> Вычисляемый объект:

– ввести имя поля – Премия;

– выбрать поле Разряд работающего, раскрыть список элементов (двойной щелчок левой кнопкой мыши на поле);

– в окно формулы ввести формулу: ='2'*0,25+'3'*0,32+'4'*0,5;

– нажать кнопку Добавить;

– ввести имя поля – Вычеты;

– выбрать поле Разряд работающего, раскрыть список элементов (двойной щелчок левой кнопкой мыши на поле);

– ввести формулу: = -0,13('2'*1,25+'3'*1.32+'4'*1,5);

– нажать кнопку Добавить;

– нажать кнопку ОК.

19. Для просмотра выражения вычисляемого поля и вычисляемых объектов панели инструментов Сводные таблицы нажать одноименную кнопку, выбрать команду контекстного меню Формулы -> Вывести формулы.

20. Установить курсор в сводную таблицу.

21. На панели инструментов Сводные таблицы нажать кнопку Мастер сводных таблиц. В окне третьего шага кнопка Макет изменить положение полей : строка – ФИО, столбец – Разряд работающего. Нажать кнопку ОК. Нажать кнопку Готово.

22. Закрыть рабочую книгу с сохранением с помощью команды меню Файл -> Закрыть.

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

Построить диаграммы, отражающие основные средства и иные внеоборотные активы баланса на начало и конец года.