Тема 1. Основные приемы работы в MS еxcel. Создание шаблонов

Тема 1. ОСНОВНЫЕ ПРИЕМЫ РАБОТЫ В MS ЕXCEL. СОЗДАНИЕ ШАБЛОНОВ

 

Электронные таблицы MS Excel предназначены для обработки таблично организованной информации. Особенностью электронных таблиц является структу­рирование информации непосредственно на этапе ввода данных — данные и формулы хранятся в ячейках рабочего листа (рис. 1.1). Рабочий лист Excel 2007 состоит из 1 048 576 строк и 16 384 столбцов, т.е. из более 17 миллиардов ячеек. Совокупность листов составляет рабочую книгу, которая сохраняется как целостный объект в одном файле с расширением xlsx.

Рис. 1.1. Слои ячейки

Настройки окна MS Excel

Рис. 1.2. Основные элементы окна MS Excel

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

Настройки окна Excel производятся с помощью кнопок на закладке Вид, а также при изменении параметров Excel. Для этого нужно нажать кнопку Office и щелкнуть по кнопке .Параметры Excel разделены на 9 категорий (рис. 1.3). В этом окне можно изменить количество листов в книге, стандартный размер и шрифт в ячейках (категория Основные), способ пересчета формул и стиль ссылок (категория Формулы), установить режим показа формул в ячейках листа, показать/убрать линии сетки, заголовки строк и столбцов, полосы прокрутки (категория Дополнительно), изменить параметры автосохранения (категория Сохранение) и др. С помощью кнопки (категория Основные) можно открыть диалог для создания пользовательского списка. Для этого нужно в левой части этого диалогового окна выбрать строку НОВЫЙ СПИСОК, а в правой части ввести элементы списка, нажимая после каждого элемента Enter.

 

Рис. 1.3. Параметры настроек Excel

Ввод и редактирование данных

Для ввода информации в ячейку листа нужно сделать ее активной, набрать с клавиатуры необходимые данные и нажать Enter. Можно вводить числа, текст, даты, время, формулы.

Ввод символов в Excel воспринимается как текстовая информация, цифр — как числовая. Для ввода чисел или формул как текста набор начинают с символа одинарной кавычки. Если, например, ввести ‘ 222, то это значение будет является текстовой переменной, но одинарная кавычка в ячейке отображаться не будет. Ввод дат и времени осуществляется в соответствии с установками региональных стандартов в среде Windows. Для русской версии элементы даты (день, месяц, год) разделяются точкой, а элементы времени (часы, минуты, секунды) — двоеточием. Например, ввод 2.12.98 соответствует дате 2 декабря 1998 года, ввод 13:45 соответствует 13 часам 45 минутам.

Числовые данные можно вводить в различных форматах: в виде десятичной (2,34) или простой дроби ( à 0 1/2), в экспоненциальном формате ( à 3Е+8), с добавлением денежной единицы (25р.) или знака процента (45%).

Редактировать данныев активной ячейке можно в строке формул или непосредственно в ячейке, дважды щелкнув по ней мышью или нажав функциональную клавишу F2.

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

- начать новый ввод данных;

- нажать клавишу Delete на клавиатуре;

- нажать кнопку Очистить на вкладке Главная ленты инструментов.

Форматирование ячеек

Осуществляется с помощью кнопок на закладке Главная ленты инструментов или диалогового окна Формат ячеек, открываемогоодноименной командой при нажатии кнопки .

Диалог Формат ячеек (рис. 1.4) состоит из шести закладок:

Рис. 1.4. Диалоговое окно Формат ячеек

ü Число — предназначена для форматирования значений ячеек;

ü Выравнивание — позволяет:

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

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

- объединить несколько предварительно выделенных ячеек в одну (флажок Объединение ячеек);

ü Шрифт — дает возможность изменять шрифт, размер и цвет шрифта, начертание, подчеркивание, эффекты (верхний и нижний индекс, зачеркивание);

ü Граница — позволяет задавать обрамление ячеек, цвет и тип линии обрамления;

ü Заливка — можно изменять фон и узор ячеек;

ü Защита — используется при создании шаблонов.

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

Создание шаблонов

Шаблон — это книга, разработанная как прототип для создания сходных книг и содержащая неизменный текст, формулы, макросы, а также ячейки, в которые вносится переменная информация. Использование шаблонов позволяет автоматизировать заполнение и расчет стандартных таблиц, бланков, отчетов, счетов и т.п.

Чтобы создать шаблон, нужно:

– внести в ячейки неизменную информацию, формулы, произвести необходимое форматирование;

– выделить ячейки, в которые будет вноситься переменная информация, и снять с них защиту (в диалоге Формат ячеек на закладке Защита снять флажок Защищаемая ячейка);

– если нужно скрыть формулы, выделить ячейки с формулами и в диалоге Формат ячеек на закладке Защита установить флажок Скрыть формулы;

– установить защиту, нажав кнопку Защитить лист на закладке Рецензирование панели инструментов. Можно также указать пароль;

– нажать кнопку Office , щелкнуть по команде Сохранить как и выбрать Тип файла: Шаблон Excel (имеет расширение xltx) или Шаблон Excel с поддержкой макросов (имеет расширение xltm). Обратить внимание, в какой папке будет сохранен шаблон, чтобы при необходимости его можно было открыть для редактирования;

– закрыть созданный шаблон.

Чтобы воспользоваться шаблоном, нужно нажать кнопку Office , щелкнуть по команде Создать. В появившемся окне Создание книги выбрать категорию Мои шаблоны, найти нужный шаблон и дважды щелкнуть по его пиктограмме. При этом на основе шаблона создается новый файл, а шаблон остается в своей папке неизменным.

 

Лабораторная работа № 1

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

Задания:

I. Ввод данных и формул.

1. В ячейки столбца А ввести следующие данные:

1.1. Число в виде десятичной дроби.

1.2. Число в виде простой дроби.

1.3. Число 7000000000000 (ширину столбца не увеличивать). Просмотреть содержимое ячеек с введенными данными в строке формул.

1.4. Отрицательное число.

1.5. Число 30.

1.6. Число 30%.

1.7. Время окончания пары.

1.8. Дату своего рождения.

1.9. Свою фамилию.

1.10. Слово истина.

2. Увеличить ширину столбца A. Сделать вывод о типах введенных данных и их выравнивании в ячейке.

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

3.1. =2*30

3.2. =2*30%

3.3. =2*А5, где А5 — это ссылка на ячейку.

3.4. =2*A6, где А6 — это ссылка на ячейку.

3.5. ‘ =2*А5

4. Сравнить введенные формулы и полученные результаты.

III. Автозаполнение.

1. На новом листе в ячейки А1:Е1 ввести: любое число, дату, время, текст, формулу =В1+7.

2. С помощью маркера заполнения протянуть вниз: число на 10 строк, дату и время на 8, текст на 5, формулу на 8. Сделать вывод о правилах работы маркера с разными типами данных и формулами.

3. В ячейки А11:А17 ввести данные: январь, вторник, фев, чт, 1 кв, 2 квартал, 1 кв. 2004.

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

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

6. По аналогии с пунктами 3, 4 заполнить ячейки списком фамилий.

IV. Форматирование.

1. На новом листе в ячейку В2 ввести какое-либо слово. С помощью маркера скопировать его в ячейки С2:Е2, а затем в ячейки B3:D5.

2. Используя кнопки на закладке Главная и диалог Формат я чеек, оформить таблицу по образцу.

 

VIII. Создание шаблона.

1. На листе «Треугольник» очистить содержимое ячеек B2, D2, F2 и снять с них защиту.

2. Скрыть все формулы.

3. Установить защиту листа.

4. Сохранить книгу как шаблон.

5. Закрыть шаблон. Создать файл на основе шаблона и заполнить его.

 

Контрольные вопросы к теме

1. Назовите правила ввода даты и времени в ячейки рабочего листа Excel.

2. Как добавить новый лист в конец рабочей книги, как переместить лист?

3. Как можно управлять видом окна Excel?

4. Как настраивать ширину столбцов и высоту строк?

5. Как ввести число в виде денежной единицы, в виде натуральной дроби?

6. Как вводятся формулы, каково назначение строки формул?

7. Как изменить стиль ссылок с обычного на R1C1 и обратно?

8. Что означает появление символов ##### в ячейке рабочего листа?

9. Перечислите возможные варианты копирования и перемещения ячеек рабочего листа?

10. Как очистить весь рабочий лист?

11. Как завершить ввод данных в ячейку? Каким образом можно редактировать содержимое ячеек?

12. Как переименовать, скопировать и удалить рабочий лист?

13. Перечислите возможности форматирования ячеек в Excel.

14. Как создать пользовательский список?

15. Как создать ряд для поквартального отчета (1 квартал 1999 и т.д.)?

16. Как вставить в колонтитул текущую дату; текущее время; имя файла; имя листа; номер страницы; количество страниц?

17. Каким образом можно разместить большую таблицу на одной странице?

18. Как можно задать печать сетки и заголовков строк и столбцов?

19. Назовите арифметические операторы, применяемые в Excel, и порядок их обработки.

20. Как можно вставить в формулу ссылку на ячейку?

21. Для чего применяются шаблоны, где они хранятся и какое имеют расширение?

22. Как осуществляется защита шаблона?

Лабораторная работа № 2

Цель работы: научиться разрабатывать многосекционные числовые форматы.

Вариант 1:

1.1. Создать файл на основе шаблона «Excel_lab_2_1». Скопировать исходные данные в соседние столбцы и назначить соответствующие форматы:

Примечание. В формате 1.6 отрицательные и нулевые значения должны быть красного цвета.

1.2. Создать форматы с условиями:

Примечание. В формате 1.13 текст «ош. знач» должен быть синего цвета.

 

Вариант 2:

2.1. Создать файл на основе шаблона «Excel_lab_2_2». Скопировать исходные данные в соседние столбцы и назначить соответствующие форматы:

Примечание. В формате 2.6 отрицательные числа со словом «Долг» должны быть синего цвета.

2.2. Создать форматы с условиями:

Примечание. В формате 2.12 числа > 5000 с текстом «тыс. $» должны быть красного цвета.

Контрольные вопросы к теме

1. Как в MS Excel хранятся даты и время? Какие символы-заменители применяются для форматирования дат и времени?

2. Как с помощью числового формата можно:
– масштабировать числа в тысячу раз;
– выравнивать числа по левому краю;
– выравнивать числа по десятичной запятой;
– окрашивать числа в какой-либо цвет;
– выводить дату и время во внутреннем формате;
– скрывать содержимое ячейки;
– заполнять всю ширину ячейки каким-либо символом;
– дописывать к числу текст?

3. Как в MS Excel будут интерпретированы значения ¾ и 5:?

4. Каким образом можно в одном формате по-разному отображать положительные и отрицательные числа?

5. Каковы правила написания формата с условиями?

Адресация в Excel

В формулах Excel применяются относительные, абсолютные и смешанные ссылки.

При копировании формулы, содержащей относительные ссылки, они изменяются относительно расположения ячейки, содержащей формулу. Например, ячейка С1 содержит формулу =A1+В1. При копировании формулы в С2 ссылки изменяются (=А2+В2). Если необходимо, чтобы ссылки не изменялись при копировании формулы, нужно использовать абсолютные ссылки. Например, нужно к числам в В1:В5 прибавить значение из А1, для этого в формуле =А1+В1 нужно использовать абсолютную ссылку на А1. Для обозначения абсолютных ссылок используется знак $. Следовательно, формула в С1 должна иметь вид =$A$1+В1, а при копировании в С2 изменится только относительный адрес (=$A$1+B2). Ссылка называется смешанной, если одна часть адреса относительная, другая — абсолютная. Например, в ссылке $A1 при копировании формулы будет меняться только строка, в ссылке C$5 при копировании формулы будет меняться только столбец.

Для циклического изменения типа ссылки используется клавиша F4.

Для копирования формулы в смежные ячейки используется маркер заполнения.

Связывание листов

Чтобы использовать в формуле данные, расположенные на другом рабочем листе, удобно открыть новое окно (кнопка Новое окно на закладке Вид ленты инструментов) и расположить их рядом (кнопка Упорядочить все). В одном окне открыть лист с формулой, в другом — с данными и ссылаться на ячейки с помощью мыши. При этом автоматически прописанная ссылка будет содержать имя листа, например: =Лист2! А3. Для разделения имени листа и адреса ячейки используется восклицательный знак.

Таким же образом можно ссылаться на данные другой книги, в этом случае ссылка будет содержать имя файла, которое заключается в квадратные скобки, например: =[kurs.xls]Итоги! $D$4.

Применение имен

Имена используются для упрощения просмотра и запоминания формул. Ссылки в формулах на имена являются абсолютными ссылками.

Чтобы присвоить имя, можно использовать один из способов:

– выделить ячейку или диапазон, активизировать Поле имени, ввести имя и нажать Enter;

– выделить ячейку или диапазон, нажать кнопку Присвоить имя на закладке Формулы ленты инструментов, в строке ввода Имя ввести имя и нажать OK;

– выделить диапазон ячеек вместе с подписью, например, диапазон E2:E6 в задании II.2, нажать кнопку Создать из выделенного фрагмента на закладке Формулы ленты инструментов, установить флажок в строке выше и нажать OK, при этом диапазон Е3:Е6 получит имя Премия. Аналогично можно использовать заголовки строк.

Чтобы применить имя в формуле, можно ввести его с клавиатуры или нажать кнопку Использовать в формуле. Чтобы удалить ненужное или неверно заданное имя, следует нажать кнопку Диспетчер имен, выбрать его в списке имен и нажать кнопку Удалить.

Вставка функций

 

 

Рис. 3.1. Диалоговое окно Аргументы функции

1 — строка формул

2 — редактируемая формула

3 — описание выделенной функции (ОКРУГЛ)

4 — поля для заполнения аргументов выделенной функции

5 — кнопка для сворачивания диалогового окна при заполнении аргумента

6 — описание выделенного аргумента

7 — значения аргументов (в А1 à 32)

8 — результат вычисления выделенной функции

9 — результат вычисления всей формулы

10 — раскрывающийся список для выбора вложенной функции

11 — кнопка Вставка функции

 

Для вставки функции применяются кнопки на закладке Формулы ленты инструментов или кнопка в строке формул. Затем в окне Мастер функций следует из соответствующей категории выбрать необходимую функцию, нажать ОК и заполнить диалоговое окно Аргументы функции (рис. 3.1). Функции могут использоваться как аргументы в других функциях. Можно использовать до 64 уровней вложения функций. Чтобы редактировать формулу, содержащую функции, следует нажать кнопку (рис. 3.1). На экране отобразится диалоговое окно с аргументами первой функции формулы. Изменение какой-либо вложенной функции происходит путем щелчка мышью по имени этой функции в строке формул.

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

Функции округления

ОКРУГЛ — округляет число до указанного количества разрядов по общим правилам.

ОКРУГЛВВЕРХ — округляет число до указанного количества разрядов в большую (по модулю) сторону.

ОКРУГЛВНИЗ и ОТБР — округляют число до указанного количества разрядов в меньшую (по модулю) сторону.

Табличные формулы или формулы массивов применяются, когда требуется выполнить действия над массивами, а затем вернуть одно или массив значений. Чтобы ввести табличную формулу:

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

2. Наберите формулу.

3. Нажмите клавиши Ctrl+Shift+Enter. Табличная формула автоматически заключается в фигурные скобки { }.

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

– весь массив заключается в фигурные скобки { }

– значения строк разделяются точками с запятой;

– значения столбцов разделяются двоеточием:

Чтобы изменить табличную формулу, которая возвращает массив, необходимо выделить весь массив и отредактировать формулу в строке формул. Завершать редактирование табличной формулы также нужно комбинацией клавиш Ctrl+Shift+Enter.

Функция МОБР (массив) возвращает обратную матрицу.

Функция МУМНОЖ (массив1;массив2) возвращает произведение матриц. Результатом является массив с таким же числом строк, как массив1, и с таким же числом столбцов, как массив2.

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

Таблица 3.1

Математическое выражение	Формула в MS Excel
	=ABS(A1)
	=LN(A1)
	=EXP(A1)
	=КОРЕНЬ(А1)
	=COS(ПИ()*А1)
	=SIN(A1)^2
	=СТЕПЕНЬ((EXP(-3*A1)+A1)/ABS(SIN(A1)–7*A1)+TAN(A1);1/3)

 

Пример 3.1. В таблице (рис. 3.2) приведен стаж и заработная плата сотрудников.

Определить:

– какой удельный вес составляет заработная плата каждого сотрудника в общем объеме заработной платы;

– сумму заработной платы сотрудников, имеющих стаж более 5 лет.

Выполнение:

1. Вычислить сумму заработной платы всех сотрудников (ячейка С11)
   =СУММ(С2:С10)
2. Вычислить удельный вес заработной платы первого сотрудника (ячейка D2)
   =C2/$C$11. В данной формуле используется абсолютная ссылка на ячейку С11, т.к. она не должна изменяться при копировании формулы.
3. Протянуть маркером формулу, созданную в D2, для остальных сотрудников. Ячейкам D2:D10 назначить процентный формат с двумя десятичными знаками.

Рис. 3.2 Рис. 3.3

1. Для нахождения суммы заработной платы сотрудников, имеющих стаж более 5 лет (ячейка С12), следует использовать функцию СУММЕСЛИ. В ней нужно указать диапазон В2:В10, по которому задается условие на стаж — ">5", и диапазон С2:С10, по которому производится суммирование. Таким образом, формула будет иметь вид à =СУММЕСЛИ(B2:B10;">5";C2:C10)

Пример 3.2. В ячейках А3:А13 (рис. 3.3) даны числа, а в ячейках B2:D2 — значения разрядов, до которых нужно округлить данные числа. В ячейке B3 написать формулу для округления и скопировать ее для заполнения всей таблицы.

Выполнение:

1. Числа в Excel можно округлять с избытком, с недостатком или по общим правилам. Так как в условии задачи не указан способ округления, будем использовать функцию ОКРУГЛ.
2. Первым аргументом данной функции указывается округляемое число, расположенное в ячейке А3. Для правильного копирования формулы в столбцы С и D в ссылке на А3 должен быть абсолютный столбец ($A3).
3. Вторым аргументом указывается число разрядов округления — ячейка В2. Для правильного копирования формулы в строки 4—13 в ссылке на В2 должна быть абсолютная строка (В$2).
4. Таким образом, формула в В3 будет иметь вид à =ОКРУГЛ($А3;В$2), которую можно скопировать для заполнения всей таблицы.

Пример 3.3. Даны матрицы А и В (рис. 3.4). Найти матрицу С = А – В.

Рис. 3.4

Выполнение:

1. Матрица С имеет такую же размерность как матрицы А и В, поэтому нужно выделить диапазон из 3 строк и 2 столбцов (G2:H4).
2. В строке формул написать формулу à =A2:B4 – D2:E4
3. Завершить ввод формулы комбинацией клавиш Ctrl+Shift+Enter. Формула будет автоматически заключена в фигурные скобки и будет одинаковой для всех ячеек диапазона G2:H4.

 

Лабораторная работа № 3

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

Задания:

Справочные формулы

Доход от оборота = Цена единицы продукции * Объем сбыта
Себестоимость = Себестоимость единицы продукции * Объем сбыта
Затраты на рекламу = Доход от оборота * % Рекламы
Накладные расходы = Доход от оборота * % Накладных расходов
Прибыль = Доход от оборота – (Себестоимость + Затраты на рекламу + Накладные расходы)

 

Внимание!!!

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

3. На листе «Продукция 2» рассчитать аналогичную таблицу для продукции 2.

4. На листе «Годовой отчет» в ячейку В3 ввести формулу для вычисления суммарного объема сбыта продукции 1 и продукции 2 за весь год (использовать функцию СУММ). Скопировать формулу для нахождения остальных показателей:

5. Изменить какие-либо значения на листе «Исходные данные» и проследить изменения на других листах.

 

II. Применение имен.

1. На листе «Зарплата» присвоить имена областям B3:B6, C3:C6, D3:D6. Создать имена областям E3:E6, F3:F6, G3:G6, H3:H6. Отдельным ячейкам А9, B9, C9, D9, E9 задать имена, используя поле имени.

2. Рассчитать таблицу, используя только созданные имена.

Справочные формулы
Оклад =Тариф * Базовая величина	Начислено = Оклад + Надбавка + Премия
Премия =% премии * Оклад	Подоходный налог = % налога * Начислено
Надбавка = % надбавки * Оклад	Профсоюз = % взноса * Начислено
К выдаче = Начислено – (Подоходный налог +Профсоюз)

Функции даты и времени

СЕГОДНЯ — возвращает текущую дату. Данная функция не имеет аргументов.

ГОД — возвращает год, соответствующий аргументу дата_в_чис­ловом_формате. Год определяется как целое в интервале 1900-9999.

МЕСЯЦ — возвращает месяц, соответствующий аргументу дата_в_чис­ловом_формате. Месяц определяется как целое в интервале от 1 (Январь) до 12 (Декабрь).

ДЕНЬ — возвращает номер дня в месяце для аргумента дата_в_число­вом_формате. День возвращается как целое число диапазоне от 1 до 31.

ДАТА — возвращает дату для заданных года, месяца и дня.

Синтаксис: ДАТА(год; месяц; день)

КОНМЕСЯЦА — возвращает последний день месяца, отстоящего на указанное количество месяцев от даты нач_дата.

Синтаксис: КОНМЕСЯЦА(нач_дата; число_месяцев)

ДАТАМЕС — возвращает дату в числовом формате, отстоящую на заданное количество месяцев вперед или назад от заданной даты.

Синтаксис: ДАТАМЕС(нач_дата; число_месяцев)

ДОЛЯГОДА — возвращает долю года, которую составляет количество дней между двумя датами (начальной и конечной).

Синтаксис: ДОЛЯГОДА(нач_дата; кон_дата;базис)

ЧИСТРАБДНИ — возвращает количество рабочих дней между нач_дата и кон_дата. Рабочими днями считаются все дни, за исключением сб, вс и праздников.

Синтаксис: ЧИСТРАБДНИ(нач_дата;кон_дата;праздники)

РАБДЕНЬ — возвращает дату, отстоящую на заданное количество рабочих дней вперед или назад от нач_дата. Рабочими днями считаются все дни, за исключением сб, вс и праздников.

Синтаксис: РАБДЕНЬ(нач_дата;количество_дней;праздники)

Лабораторная работа № 4

Цель работы: научиться применять встроенные функции при решении задач.

Вариант 1:

Создать файл на основе шаблона «Excel_Lab_4_1».

I. Функции даты и времени

1.1. Получить даты праздников 1 января, 7 января, 8 марта, 1 мая, 9 мая, 3 июля, 7 ноября, 25 декабря для текущего года:

Текущая дата:
Текущий год:
День	Месяц	Дата

1.2. Используя данные первой задачи, разработать формулу для определения количества рабочих дней оставшихся до конца I полугодия:

Последний день полугодия:
Число рабочих дней:

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

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

Фамилия	Дата поступления	Стаж (полных лет)
Гончаров	03.05.78
Новицкая	15.08.85
Петренко	01.03.99
Шевцов	30.10.95
Федорова	08.01.67

I. Функции даты и времени

2.1. Получить даты праздников 1 января, 7 января, 8 марта, 1 мая, 9 мая, 3 июля, 7 ноября, 25 декабря для текущего года:

Текущая дата:
Текущий год:
День	Месяц	Дата

2.2. Используя данные первой задачи, разработать формулу для определения количества рабочих дней, прошедших с начала года:

Первый день года:
Число рабочих дней:

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

2.4. Разработать формулу, вычисляющую возраст (полных лет) работников предприятия на текущую дату:

Фамилия	Дата рождения	Возраст (полных лет)
Гончаров	19.12.1961
Новицкая	26.10.1968
Петренко	03.02.1948
Шевцов	11.01.1955
Федорова	05.11.1975

Рис. 5.2. Закладки для работы с диаграммами

Диаграмма состоит из ряда элементов (рис. 5.2), к которым относятся: оси, легенда, маркеры, ряды данных, область построения диаграммы, подписи, линии сетки. Созданную диаграмму можно перемещать, форматировать, изменять ее размеры. Форматирование диаграммы и ее отдельных элементов осуществляется с помощью команд контекстного меню либо кнопок на закладках Конструктор, Макет, Формат ленты инструментов. Создание диаграммы и форматирование ее элементов рассмотрим более подробно на примерах.

Пример 5.1. Построить график функции для значений x Î [‑1,2; 1,4] с шагом 0,2. Отформатировать построенную диаграмму так, чтобы оси пересекались в нуле, цена деления по оси значений — 1,5, маркеры ряда данных — в виде треугольника. Продлить построенный график для значений x Î ]1,4; 2,2].

Выполнение:

1. Ввести подписи: в А1 — х, в В1 — y (рис. 5.4).

Рис. 5.3. Диалоговое окно для изменения источника данных диаграммы

1. В А2 ввести -1,2, в А3 — -1. Выделить А2:А3 и протянуть маркером до А15, при этом ячейки заполнятся значениями х с шагом 0,2.
2. В ячейку В2 ввести формулу для y и протянуть маркером до В15 =ЕСЛИ(A2<=0;(1+A2^3)/(4+COS(A2));2*A2^2*SIN(A2)^3)
3. Выделить диапазон В1:В15, перейти на закладку Вставка, раскрыть кнопку График и выбрать тип диаграммы — график с маркерами (рис. 5.1).
4. Нажать кнопку Выбрать данные на закладке Макет ленты инструментов и в открывшемся диалоге (рис. 5.3) нажать кнопку Изменить подписи горизонтальной оси. В появившемся окне указать диапазон подписей оси (выделить мышью). После этого на графике вместо порядковых номеров на оси х будут отображаться его значения.

Рис. 5.4. График функции y = f (x) (пример 5.1)

1. В построенном графике нужно отформатировать оси и ряд данных. Для этого:

- в списке элементов диаграммы на закладке Макет (рис. 5.2) выбрать элемент Горизонтальная ось (категорий) и затем нажать кнопку ;

- в открывшемся диалоге активизировать опции, как показано на рис. 5.5 слева, чтобы оси пересекались в нуле;

Рис 5.5. Форматирование оси категорий (слева) и оси значений (справа)

- затем выбрать элемент Вертикальная ось (значений) и отформатировать его, как показано на рис. 5.5 справа;

- после выбора элемента Ряд “y” отформатировать тип, размер и заливку маркера, цвет и тип линии ряда (рис. 5.4, 5.6)

Рис 5.6. Форматирование ряда данных для графика

1. Для продления графика заполнить ячейки А16:А19 значениями х и протянуть формулу для y до В19.
2. Выделить диаграмму щелчком мыши. Диапазоны ячеек, которые были использованы для построения диаграммы, будут выделены цветными рамками. Протянуть левой кнопкой мыши за маркер (правый нижний угол ячейки В15) до ячейки В19, после чего новые данные будут отображены на графике.

Пример 5.2. Построить круговую диаграмму по данным таблицы А1:В10 (рис. 5.8).

Выполнение:

1. Имеющиеся данные будут отображены более наглядно на вторичной круговой диаграмме à , так как продажи за май—сентябрь значительно меньше, чем в первые месяцы.

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

Рис. 5.7. Форматирование ряда данных для круговой диаграммы

Рис. 5.8. Вторичная круговая диаграмма (пример 5.2)

3. Для вставки подписей данных нужно раскрыть кнопку Подписи данных на закладке Макет, выбрать строку Дополнительные параметры подписей данных и изменить подписи, как показано на рис. 5.9. Можно также выдвинуть мышкой один сектор, при этом должен быть выделен только этот сектор.

Рис. 5.9. Форматирование подписей данных

Пример 5.3. Построить гистограмму по данным таблицы А2:D6 (рис. 5.10).

Выполнение:

1. Выделить А2:D6 и вставить гистограмму с группировкой.

Рис. 5.10. Гистограмма со вспомогательной осью (пример 5.3)

2. Объем производства товара С на два порядка меньше, чем товаров А и В, поэтому данные по товару С практически не будут видны на гистограмме. Чтобы этого избежать, нужно выбрать элемент диаграммы Ряд “Товар С” на закладке Макет, открыть его формат и указать опцию по вспомогательной оси, а также боковой зазор 300% (рис. 5.11). Не отменяя выделение ряда “Товар С” добавить для него подписи значений с ключом легенды (см. пример 5.2).