Сводные показатели работы фирмы за первый квартал
| Наименование продукции | Объем продаж, ед. | Выручка от продажи, руб. |
| Видеомагнитофоны | ||
| Видеокамеры | ||
| Видеокассеты | ||
| Итого: |
Разместите эту таблицу на рабочем листе 4. Задайте ему новое имя «Квартал». Для заполнения таблицы выполните следующие действия:
· расположите листы «Январь», «Февраль», «Март» и «Квартал» на одном экране в разных окнах;
· активизируйте ячейку листа «Квартал», в которой будут суммироваться данные об объеме продаж по месяцам;
· выполните щелчок левой кнопкой мыши по кнопке Автосумма;
· перейдите на рабочий лист «Январь» и активизируйте ячейку, в которой будут суммироваться данные об объеме продаж за месяц, введитевручную символ точка с запятой (;) в качестве разделителя списков при вводе несмежных ячеек;
· перейдите на рабочий лист «Февраль» и активизируйте ячейку, в которой будут суммироваться данные об объеме продаж за месяц, введитевручную символ точка с запятой (;) в качестве разделителя списков при вводе несмежных ячеек;
· выполните аналогичные действия для листа «Март» и нажмите клавишу [Enter], в результате в ячейке «Итого» рабочего листа «квартал» появится число, равное сумме продаж товаров за три месяца первого квартала;
· полученную формулу можно скопировать в ячейки этого столбца, расположенные выше.
Таким образом, при изменении данных о продажи товаров в каком-либо месяце автоматически изменится итоговая сумма продаж за квартал.
При связывании листов лучше использовать в формулах абсолютную адресацию, так как использование относительных адресов может привести к ошибкам. Еще удобнее использовать не адрес, а имя ячейки.
Объединение рабочих листов можно выполнить методом консолидации. Этот метод будет рассмотрен ниже.
Контрольные вопросы.
1. Какие преимущества дает использование рабочих листов при вычислениях?
2. Какие процедуры известны Вам при работе с рабочими листами?
3. Как вызвать контекстно-зависимое меню для ярлыков рабочих листов?
4. Какие преимущества в вычислениях дает связывание рабочих листов?
5. Почему при связывании рабочих листов лучше использовать абсолютную адресацию в формулах?
АНАЛИЗ ДАННЫХ
Фильтрация данных
Фильтры, интегрированные в Excel, упрощают процесс ввода и удаления записей из списка, а также процесс поиска информации.
Результат запроса можно скопировать в отдельную область таблицы и использовать в вычислениях.
При фильтрации записи, которые не соответствуют заданному критерию, скрываются, однако порядок их расположения остается прежним.
Для фильтрации данных рассмотрим Расширенный фильтр.
Основой расширенного фильтра является множество критериев, для задания которых следует скопировать заголовки столбцов в пустую строку текущего рабочего листа. Теперь в ячейку под соответствующим заголовком столбца можно вставить критерий.
Критерии представляют собой набор условий, используемых для поиска данных в указанном диапазоне ячеек. В качестве условий могут выступать как искомый набор символов, так и выражения.
Строчные и прописные буквы при фильтрации данных не различаются.
В критериях можно применять символы подстановки: звездочку для замены произвольного количества символов и знак вопроса для замены одного неизвестного символа. Допускается неоднократное употребление вопросительного знака в критерии.
На отдельном листе создадим список сотрудников и скопируем заголовки столбцов «Фамилии» и «Стаж работы» в пустые ячейки, расположенные справа:
| Фамилии | Стаж работы | Фамилии | Стаж работы | |||
 Иванов М.С.
| ||||||
| ||||||
| Карасев К.И. | ||||||
| Краснов С.С. | ||||||
| Смирнов Н.Н. |
Так, например, чтобы найти данные о сотрудниках, фамилии которых начинаются на букву «К», в качестве критерия поиска под заголовком столбца «Фамилии» следует ввести «К*».
| Фамилии |
| К* |
Чтобы отобрать строки с ячейками, имеющими значения в заданных границах, следует использовать оператор сравнения (>; <; <=; >=;<>). Условие отбора с оператором сравнения следует ввести в ячейку ниже заголовка столбца.
Критерии, введенные в одной строке, объединяются условным оператором И, а в разных строках – оператором ИЛИ. При задании критериев знак равенства не используется.
Рассмотрим еще один пример. Допустим, чтобы отобрать сотрудников, имеющих стаж работы больше 5 лет и меньше 10 лет, добавьте еще один заголовок «Стаж работы» и введите условия отбора следующим образом:
| Стаж работы | Стаж работы |
| >5 | <10 |
Если же необходимо отобрать сотрудников, имеющих стаж меньше 5 лет или больше 10 лет, то критерии задаются так:
| Стаж работы |
| <5 |
| >10 |
Для активизации функции расширенного фильтра выберите команду Данные ®Фильтр®Расширенный фильтр.После этого на экране появится диалоговое окно, в котором следует указать исходный диапазон и диапазон условий.
Чтобы задать диапазон ячеек, содержащих критерии, установите курсор ввода в поле Диапазон условий и отметьте нужные ячейки, включая заголовки столбцов. При указании диапазона критериев следует выделять только заполненные строки, так как пустая строка интерпретируется программой как критерий, связанный с другими критериями условным оператором ИЛИ.
Данные можно фильтровать на месте исходного диапазона или скопировать результат в другое место. Если активизирована опция Скопировать результат в другое место, в поле Поместить результат в диапазон следует указать адрес ячейки, начиная с которой будут располагаться соответствующие критериям записи.
В результате установки опцииТолько уникальные записи из полученного списка будут исключены записи с общими элементами.
Вычисление итогов
В Excel для автоматического вычисления итогов предназначена команда менюДанные ® Итоги. Перед выполнением этой команды данные должны быть представлены в виде списка и отсортированы.
Проанализируем, например, значения из таблицы, содержащей список договоров, подлежащих оплате. Создадим таблицу и попробуем с помощью функции автоматического вычисления итогов определить сумму выплат по договорам, заключенным каждым заказчиком. Обратите внимание на то, что записи в этой таблице не упорядочены.
| Код заказчика | N договора | Договорная цена, млн. руб. |
| 89-101 | 30.0 | |
| 90-103 | 35.0 | |
| 90-111 | 20.0 | |
| 90-102 | 33.0 | |
| 90-114 | 23.0 | |
| 90-115 | 10.0 |
Отсортируйте сначала данные в таблице по коду заказчика. Установите указатель ячейки в любой ячейке созданной таблицы и активизируйте команду менюДанные ® Сортировка. После нажатия кнопки ОК в диалоговом окне Сортировка диапазона данные в таблице будут отсортированы.
Далее активизируйте одну из ячеек таблицы и выберите в меню Данные команду Итоги. В результате откроется диалоговое окно Промежуточные итоги.
В поле списка «При каждом изменении в» выберите заголовок столбца (например: Код заказчика), для которого необходимо вычислить промежуточные итоги после каждого изменения данных на рабочем листе.
Для вычисления итоговой суммы в поле списка Операция установите функцию Сумма. Укажите также столбец для вычисления итогов – столбец (Договорная цена). Для этого активизируйте в области Добавить итоги по опцию Договорная цена. В завершение щелкните на кнопке ОК.
В результате таблица будет дополнена строками, содержащими итоговые значения для каждого заказчика. В последнюю из вставленных в таблицу строк включается информация об общем итоге для всех заказчиков.
При вычислении итогов таблица структурируется. Создание уровней структуры приводит к повышению наглядности таблицы. Чтобы отобразить на экране только итоговые данные, следует выполнить щелчок на кнопке второго уровня структуры, вследствие чего данные третьего уровня (исходные значения) будут скрыты. Для восстановления отображения исходных значений необходимо выполнить щелчок но кнопке третьего уровня.
Рассмотрим еще одну возможность Excel – комбинирование нескольких итогов.
Добавьте в таблицу еще один показатель – количество договоров, заключенных каждым заказчиком. Для этого повторно откройте диалоговое окно Промежуточные итоги. В поле «При каждом изменении в» выберите элемент Код заказчика. Чтобы определить количество договоров, заключенных каждым заказчиком, установите в поле Операция функцию Количество значений и активизируйте в области Добавить итоги по опцию N договора.
Для того чтобы в таблице отображались все итоги, перед нажатием кнопки ОК следует выключить опцию Заменить текущие итоги.
Для удаления строк с итоговыми значениями предназначена кнопка Убрать все, расположенная в диалоговом окне Промежуточные итоги.
Работа со списками
Список – набор строк таблицы, содержащий связанные данные, например набор адресов, телефоны клиентов и т.д. Данные из списка можно выбирать и подставлять в указанные ячейки.
Для создания списка на текущем листе необходимо:
· ввести в строку или в столбец данные, которые будут использоваться в качестве списка; не включайте в список пустые ячейки (допустим, что список введен в ячейки B2:B10);
· выделить ячейки, в которые потом можно будет подставить выбранные значения из списка (например: F2:F10);
· выбрать команду меню Данные ® Проверка,а затем вкладку Параметры;
· выбрать Список в окне Тип данных;
· ввести ссылку на список данных в поле Источник или выделить с помощью курсора мыши ячейки, содержащие список (в нашем примере – ячейки B2:B10 и тогда в поле Источникподставится формула =$B$2:$B$10);
Если существует вероятность изменения списка данных, необходимо присвоить имя количеству элементов списка и затем ввести это имя в поле Источник. При увеличении или уменьшении количества элементов списка, находящегося на листе, соответствующие изменения будут автоматически внесены в список данных ячейки.
Для присваивания имени ячейке или группе ячеек необходимо:
· выделить ячейку, группу ячеек или несмежный диапазон, которому необходимо присвоить имя;
· указать поле имени, которое расположено слева в строке формул;
· ввести имя ячеек;
· нажать клавишу [Enter].
Для присваивания имени ячейке или группе ячеек можно использовать режим меню Вставка ® Имя ® Присвоить.
Если список данных находится на другом листе или в другой книге, то необходимо:
· выбрать команду меню Данные ® Проверка ® Параметры, определить тип данных Список на активном листе;
· ввести знак «=» и имя списка в поле Источник или активизировать команду меню Вставка ® Имя ® Вставить и выбрать имя списка из окна Вставка имени.
Например, если данные, которые требуется использовать в формуле на втором листе , находятся в ячейках A6:A12 первого листа книги Бюджет.xls, можно определить им на первом листе имя «Данные» как =[Бюджет.xls]Лист1!$A$6:$A$12 и затем на втором листе ввести =Данные в поле Источник.
Консолидация данных
Консолидация выполняется в том случае, если необходимо подытожить данные, расположенные в разных областях таблицы. С помощью функции консолидации значений из несмежных диапазонов можно выполнить те же операции, что и при автоматическом определении промежуточных итогов.
Подлежащие консолидации диапазоны ячеек могут находиться на одном рабочем листе, на разных листах и в разных книгах.
Рассмотрим метод консолидации на примере таблиц, созданных ранее для выполнения операции связывания рабочих листов (см. п.9.5), отражающих анализ работы фирмы за квартал.
Для консолидации данных используйте рабочий лист «Квартал». На этом листе следует активизировать ячейку, которая послужит началом диапазона ячеек с итогами.
Для консолидации данных предназначена команда меню Данные ® Консолидация. Активизируйте эту команду, вследствие чего откроется одноименное окно, в котором нужно указать адреса консолидируемых диапазонов ячеек и выбрать необходимую функцию.
Сначала в поле Функция выберите функцию, которая будет использоваться при объединении данных (в нашем примере - Сумма). Затем перейдите в поле Ссылка, чтобы задать координаты первого диапазона ячеек с данными, подлежащими консолидации.
Адреса консолидируемых диапазонов можно ввести с клавиатуры, однако намного удобнее указать их в поле Ссылка путем выделения диапазонов.
Перейдите на лист с консолидируемыми данными (лист «Январь») и выделите первый диапазон. Во время выделения ячеек окно Консолидациисворачивается до размера поля ввода, освобождая рабочую область листа. После выделения диапазона надлежит щелкнуть по кнопке Добавить, вследствие чего ссылка на указанный диапазон появится в поле Список диапазонов.
Введите в поле Ссылка адреса всех консолидируемых диапазонов. Если консолидируемые диапазоны расположены на разных листах книги одинаково, то после ввода адреса первого диапазона выделять остальные диапазоны не нужно, достаточно прейти на новый лист и нажать кнопку Добавить.
После ввода всех консолидируемых диапазонов следует нажать кнопку ОК.
Чтобы установить связь между консолидированными и исходными данными, следует при выполнении консолидации включить опцию «Создавать связи с исходными данными» в диалоговом окне Консолидация.
Создание сводной таблицы
Сводными называются вспомогательные таблицы, которые содержат часть данных анализируемой таблицы. Они используются для более наглядного представления данных и их анализа, для быстрого подведения итогов или объединения больших объемов данных.
Сводные таблицы создаются на основе области таблицы, целой таблицы или нескольких таблиц. Таблицы должны содержать заголовки строк или столбцов, которые необходимы для создания полей данных.
Рассмотрим возможности использования сводных таблиц на примере. Создадим таблицу.
| Название судна | Проект | Пункт назначения | Операция | Загрузка, т |
| Балтийский-1 | Череповец | Погрузка | ||
| Балтийский-5 | Череповец | Выгрузка | ||
| Волго-Дон-3 | С-Петербург | Погрузка | ||
| Волго-Балт-10 | Петрокрепость | Погрузка | ||
| Волго-Балт-15 | Ростов | Погрузка | ||
| Волго-Балт-25 | Река Свирь | Выгрузка |
На основе этой таблицы создадим сводную таблицу, отражающую сведения о судах, выполняющих определенную операцию («Выгрузку» или «Погрузку»).
Создание и обработка сводных таблиц осуществляются с помощью специального мастера, для запуска которого предназначена команда Сводная таблица из меню Данные. После ее вызова открывается первое диалоговое окно мастера сводных таблиц – Мастер сводных таблиц – шаг 1 из 4.
В первом диалоговом окне мастера сводных таблиц указывается источник данных для сводной таблицы. В области «Создать таблицу на основе данных, находящихся:» по умолчанию активизирован переключатель «в списке или базе данных Microsoft Excel». Поскольку наша таблица размещена на рабочем листе Exсel, не будем изменять эту установку. Нажмите кнопку Далее, чтобы перейти в следующее диалоговое окно мастера.
Во втором окне мастера сводных таблиц определяется диапазон ячеек, данные из которого будут включены в сводную таблицу. Если перед запуском мастера указатель ячейки находился внутри таблицы, то программа автоматически вставит в поле Диапазонадрес всей таблицы. После определения диапазона ячеек перейдите в следующее окно мастера, нажав кнопку Далее.
В третьем окне мастера сводных таблиц определяется структура создаваемой таблицы. В центре этого диалогового окна расположены области строк, столбцов, страниц и данных. Все заголовки полей таблицы отображаются справа от перечисленных областей. Каждый заголовок поля можно переместить в любую область строящейся сводной таблицы.
Переместите поле Операция в область страниц. Таким образом вы зададите размещение данных по всем операциям на «отдельной странице». Операции будут находиться в поле списка. Для отображения на экране данных о какой-либо операции нужно будет выбрать ее в списке. Предположим, что нас интересует в первую очередь загрузка судов. В этом случае ее следует разместить в поле Данные. При этом отдельные значения будут автоматически просуммированы. Поместите поля Проект, Название судна и Пункт назначения в область полей строк.
Четвертое диалоговое окно мастера сводных таблиц служит для выбора местоположения сводной таблицы – на новом листе переключателем новый лист или на текущем листе переключателем существующий лист. При размещении таблицы на текущем рабочем листе необходимо указать позицию ее левого верхнего угла.
Выполнение установок в диалоговых окнах мастера сводных таблиц следует завершить нажатием кнопки Готово. В результате в соответствии с установленными параметрами будет создана сводная таблица.
Параметры сводной таблицы устанавливаются в окне Параметры сводной таблицы (курсор должен находиться в созданной таблице). Это окно открывается в результате активизации команды Параметры из меню Сводная таблица одноименной панели инструментов или при нажатии кнопки Параметры в четвертом диалоговом окне мастера сводных таблиц.
Окно Параметры сводной таблицы состоит из двух областей – Формат и Данные. В первой области устанавливаются параметры форматирования сводной таблицы, а во второй – параметры источника данных сводной таблицы.
Опции общая сумма по столбцам и общая сумма по строкам позволяют задать автоматическое определение промежуточных итогов, а опция автоформат – использование параметров автоформата при форматировании таблицы. Если установлена опция сохранять форматирование, выполненное вами форматирование сводной таблицы сохранится и после перегруппировки данных. В результате активизации опции включать скрытые значения в сводной таблице учитываются значения, находящиеся в скрытых ячейках.
Сводная таблица может быть скопирована на другой рабочий лист. Перемещение таблицы не отражается на ее свойствах.
Для редактирования сводной таблицы предназначена панель инструментов Сводные таблицы, которая открывается автоматически после создания сводной таблицы.
Вернемся к нашему примеру и отобразим на экране данные о судах, выполняющих погрузку. Для этого следует выбрать операцию Погрузка в списке Операция.
Изменение вида сводной таблицы выполняется путем перемещения поля данных в новую позицию. Таким образом, в область полей страницы можно поместить несколько полей, содержимое которых будет использоваться в качестве критерия при «разбиении» данных на отдельные страницы.
Разместите поле данных Проект под полем Операция. Во время перемещения поля появляется специальный символ, указывающий на то, что поле данных будет вставлено в область полей. Если в обоих полях списка выбраны элементы все, в таблице будут представлены значения по всем судам, выполняющим операции: «Выгрузка» и «Погрузка». Выбирая соответствующий элемент в списке Проект, можно задать показ данных по указанному проекту.
При перемещении поля данных за пределы сводной таблицы оно удаляется. Восстановить удаленное поле данных можно в третьем окне мастера сводных таблиц путем перемещения имени поля в одну из областей.
Для каждого значения поля пользователь может задать показ деталей. Чтобы узнать, к какому виду операции относится судно, следует задать показ деталей для определенного пункта назначения.
Выполните щелчок на кнопке Отобразить детали панели инструментов Сводные таблицыили активизируйте команду Группа и структура->Отображать детали контекстного меню сводной таблицы. В результате откроется диалоговое окно, в котором следует выбрать элемент, необходимый для показа сведений, например элемент Операция.
Если в окне Параметры сводной таблицы установлена опция развертывание разрешено, двойной щелчок в области данных приведет к созданию нового листа с подробной информацией о выбранной ячейке. В нашем примере таковой информацией является информация о загрузке судна.
После нажатия кнопки ОК в диалоговом окне Показать детали поле данных будет перемещено из области полей страниц в область полей столбцов.
Контрольные вопросы.
1. Каким образом можно произвести анализ данных в таблицах?
2. В каких случаях применяется фильтрация данных?
3. Как применить расширенный фильтр к таблице?
4. Как задаются критерии? Приведите примеры.
5. С помощью какой команды можно произвести автоматическое вычисление итогов?
6. Что необходимо выполнить перед автоматическим вычислением итогов?
7. Как создать список данных на текущем листе?
8. Как присвоить списку имя?
9. Как использовать список, созданный на другом листе книги?
10. В каких случаях можно использовать консолидацию данных?
11. Где могут находиться данные, подлежащие консолидации?
12. Как выполнить консолидацию данных?
13. Для чего предназначены сводные таблицы?
14. Что может являться основой для построения сводных таблиц?
15. Как осуществить создание сводной таблицы?
16. Как вызвать Мастера сводных таблиц?
17. Как установить параметры сводной таблицы?
18. Какие опции можно задавать в окне Параметры сводной таблицы?
19. Какие операции можно выполнить с помощью панели инструментов Сводные таблицы?
20. Как изменить вид сводной таблицы?
21. Как показать детали сводной таблицы?
Лабораторные работы
Лабораторная работа 1.
Изучение основных режимов работы в среде EXCEL и ввод данных в электронную таблицу
Задание.
1. Загрузите систему.
2. Ознакомьтесь с программным продуктом, выполнив команду меню:
? ® О программе.
3. Изучите структуру рабочего поля электронной таблицы (ЭТ) и назначение кнопок панелей инструментов.
4. Выполните все способы перемещения по таблице в пределах одного рабочего листа и между листами рабочей книги.
5. Выполните выделение отдельной ячейки, трех смежных ячеек, трех несмежных ячеек (с помощью мыши, а также с помощью клавиатуры).
6. Выполните выделение целого ряда, целой колонки и всей таблицы.
7. Введите данные в соответствующие клетки (рис.2). Обратите внимание на особенности ввода данных различного типа (рисунок не является точной копией ЭТ).
8. Изучите режим редактирования данных:
а) отредактируйте данные в клетке А6 (измените фамилию);
б) исправьте остальные ошибки, допущенные при вводе.
9. Запишите созданную ЭТ на диск в каталог учебной группы.
10. Выполните выход из среды Excel.
| А | B | С | D | E | |
| Расчет заработной платы за сентябрь | Дата расчета | (текущая) | |||
| Фамилия | Табельный номер | Оклад, руб. | Отработано дней | Начислено, руб. | |
| Иванов | =с5/d11*d5 | ||||
| Петров | =c6/d11*d6 | ||||
| Сидоров | =c7/d11*d7 | ||||
| Итого | =сумм(e5:e7) | ||||
| Количество рабочих дней в месяце: |
Рис.2. Макет электронной таблицы к лабораторной работе 1
Лабораторная работа 2
Изучение технологии форматирования данных в ЭТ
Задание.
1. Загрузите программу Excel. Откройте файл, созданный в лабораторной работе 1.
2. Увеличьте ширину столбцов для оформления «шапки» таблицы.
3. Отформатируйте текст по центру внутри блока А3:Е3.
4. Выделите «шапку» таблицы цветом и рамкой.
5. Назначьте для вывода числовых данных денежный формат.
6. Оформите название таблицы шрифтом Arial, 14, жирный.
7. Введите дополнительные сведения о созданной таблице (название, автор, ключевые слова).
8. Сохраните этот файл под другим именем.
9. Закройте этот файл.
10. Найдите файл по известным дополнительным сведениям, используя команду поиска файлов.
11. Разделите окно на два экрана.
12. Настройте окна на режим отображения формул.
13. Откажитесь от разделения окна на два экрана.
14. Закончите работу с системой.
Лабораторная работа 3
Изучение режимов редактирования и копирования данных
Задание.
1. Загрузите таблицу, созданную в лабораторной работе 2.
2. Вставьте столбцы для ввода имени, отчества и заполните их.
3. Вставьте пустую строку перед первой фамилией и заполните ее, формулу в столбце «Начислено» скопируйте из второй строки. Объясните полученный результат.
4. Отредактируйте столбец «Начислено»: введите, где необходимо, признак абсолютного адреса.
5. Внесите изменения в формулу для расчета строки «Итого», объясните необходимость этих изменений.
6. Заполните два столбца «Величина налога» и «Сумма к выдаче». Формулы для расчета разработайте самостоятельно. Используйте при заполнении столбцов команду копирования.
7. Отформатируйте данные в добавленных столбцах.
8. Переместите данные из строки, содержащей количество рабочих дней в месяце, на пустую вторую строку.
9. Запишите таблицу на диск под новым именем.
10. Завершите работу с системой.
Лабораторная работа 4
Графическое представление табличных данных
Задание.
1. Загрузите систему.
2. Заполните ЭТ (рис.3), учитывая следующие требования:
a) формулы для расчета итогов внесите в клетки, используя кнопку Автосуммы;
б) введите в клетку E4 формулу и выполните команду копирования в блок E5:E6;
в) введите в клетку B8 формулу и выполните команду копирования в блок С8:E8.
3. Отформатируйте таблицу.
4. Запишите таблицу на диск.
5. Постройте диаграммы (гистограмму, график, круговую), с помощью Мастера диаграмм.
| А | B | С | D | E | |
| Плановые объемы перевозок грузов, тыс.т | |||||
| Вид перевозок | 2 квартал | 3 квартал | 4 квартал | Итого | |
| Плоты | *** | ||||
| Сухогрузы | *** | ||||
| Нефтегрузы | *** | ||||
| Итого | *** | *** | *** | *** | |
Рис.3 Макет ЭТ к лабораторной работе 4
Лабораторная работа 5
Условное форматирование
1. Создайте налоговую карточку согласно представленной таблице на рис 4. Доход может вводиться за любой отчетный месяц. Доход с начала года рассчитывается по формуле (формулу разработайте сами).
2. Рассчитайте итоговые значения за год, формулы разработайте самостоятельно. Выделите значения красным, жирным цветом.
3. Выполните условное форматирование столбца Доход. Если доход равен 0, то установите цвет ячейки - черный и узор – 25%серый.
4. Выполните условное форматирование столбца Доход с начала года. Если Доход с начала года за какой-либо месяц равен последующему значению, то установите цвет ячейки – светло-коричневый и узор - 25%серый, иначе цвет ячейки должен быть – светло-коричневый без узора.
| Налоговая карточка | ||
| Месяц | Доход | Доход с начала года |
| январь | ||
| февраль | ||
| март | ||
| апрель | ||
| май | ||
| июнь | ||
| июль | ||
| август | ||
| сентябрь | ||
| октябрь | ||
| ноябрь | ||
| декабрь | ||
| Итого |
Рис 4. Макет ЭТ к лабораторной работе 5
Лабораторная работа 6а
Изучение функций системы Excel
Задание.
1. Рассчитайте таблицу значений функций:
, где х изменяется от –1 до 3 с шагом 0,1. Постройте графики этих функций на одной координатной плоскости.
| № | Фамилия И.О. | Тест 1 | Тест 2 | Тест 3 | Результат | Оценка | Место |
| Михайлов А.А. | |||||||
| Муравьев А.Н. | |||||||
| Щеглов А.П. | |||||||
| Александров Л.П. | |||||||
| Солодов А.С. | |||||||
| Петров И. А. | |||||||
| Иванов С.С. | |||||||
| Яковлев П. Р. | |||||||
| Осипов А.В. | |||||||
| Сидоров Ф.Т. | |||||||
| Средний балл |
2. Создайте таблицу, внесите в нее данные тестирования и рассчитайте:
· суммарный результат по итогам трех тестов напротив каждого учащегося;
· средний балл по результатам каждого теста;
· оценку по следующему критерию: если результат меньше 18, то оценка 2; если меньше 33, то оценка –3; если результат меньше 49, то результат 4, иначе 5;
· занимаемое место по результату тестирования с помощью функции РАНГ()
· отсортируйте данные таблицы по результатам тестирования.
3. Создайте таблицу, внесите необходимые данные:
· рассчитайте суммарное количество осадков за каждый представленный год и за 3 года;
· найдите минимальное и максимальное значение за каждый год и за 3 года;
· вычислите среднемесячное количество осадков;
· Вычислите стандартное отклонение от среднего количества выпавших осадков;
· определите количество засушливых месяцев в каждом году и за три года, при условии, что количество выпавших осадков должно быть менее 10 мм (функция СЧЁТЕСЛИ());
· определите количество осадков в незасушливые месяцы, при условии, что количество выпавших осадков должно быть больше или равно 10 мм (функция СУММЕСЛИ());
· постройте диаграмму с областями, отражающую количество выпавших осадков за каждый год.
| Обработка данных метеостанции | ||||
| Количество осадков (в мм) | ||||
| Месяц/год | за 3 года | |||
| Январь | 37,2 | 34,5 | ||
| Февраль | 11,4 | 51,3 | 1,2 | |
| Март | 16,5 | 20,5 | 3,8 | |
| Апрель | 19,5 | 26,9 | 11,9 | |
| Май | 11,7 | 45,5 | 66,3 | |
| Июнь | 129,1 | 71,5 | ||
| Июль | 57,1 | 152,9 | 50,6 | |
| Август | 43,8 | 96,6 | 145,2 | |
| Сентябрь | 85,7 | 74,8 | 79,9 | |
| Октябрь | 14,5 | 74,9 | ||
| Ноябрь | 12,5 | 56,6 | ||
| Декабрь | 21,2 | 22,3 | 9,4 | |
| Суммарно | ||||
| Максимум | ||||
| Минимум | ||||
| Среднемесячно | ||||
| Стандартное отклонение от среднего | ||||
| Кол-во засушливых месяцев | ||||
| Осадки в незасушливые месяцы |
4. Создайте макет автоматического календаря:
· Выполните Сервис->Надстройки->Пакет анализа (содержит функции и интерфейсы для анализа научных и финансовых данных)
· Создайте связанные списки в столбцах H и I
· 1– Ячейка для ввода года
· 2– ячейка для ввода номера месяца
· 3– ячейка, содержащая формулу =ПРОСМОТР(G3;H2:H13;I2:I13)
· 4 - =2-ДЕНЬНЕД(ДАТА($B$3;G3;1);2)
· 5- =ЕСЛИ(ЕЧИСЛО(C5);ЕСЛИ(ДЕНЬ(КОНМЕСЯЦА(ДАТА($B$3;$G$3;1);0))<C5+1;"";C5+1);"")
| A | B | C | D | E | F | G | H | I | ||
| Календарь | Январь | |||||||||
| Месяц | Февраль | |||||||||
| Март | ||||||||||
| пн | Апрель | |||||||||
| вт | Май | |||||||||
| ср | Июнь | |||||||||
| чт | Июль | |||||||||
| пт | Август | |||||||||
| сб | Сентябрь | |||||||||
| вс | Октябрь | |||||||||
| Ноябрь | ||||||||||
| Декабрь | ||||||||||
· выполните цветовое оформление таблицы
· вставьте примечания для ввода года и месяца
· спрячьте столбцы H и I
· защитите ячейки с формулами и текстом.
Лабораторная работа 6б
Изучение функций системы Excel
Задание.
5. Стоимость постройки судна - 8 млн. долларов. Судовладелец имеет собственных средств только 50%, поэтому хочет взять кредит на 4 млн. долларов. Срок погашения кредита - 10 лет, ежегодный процент за кредит - 8%. Рассчитать сумму ежемесячного платежа процента за кредит. Однако, судовладелец может выплачивать за кредит не более 90 тыс. долларов в месяц, определите при этих условиях будет желаемый для судовладельца срок погашения кредита.
6. Допустим, вы располагаете информацией о процентах взноса служащих в пенсионный фонд (данные приведены в таблице, расположенной ниже):
· определите на дату расчета возраст (количество полных лет) служащих и количество отработанных ими лет на данном предприятии;
· вычислите сумму взноса для каждого служащего, учитывая следующую схему расчета: если возраст служащего больше 25 лет и время его работы на данном предприятии не менее 2-х лет на дату расчета, то сумма взноса определяется в процентах от основного заработка, в противном случае взнос не определяется. Процент взноса составляет - 3%;
· отсортируйте полученные сведения в таблице, располагая фамилии в алфавитном порядке;
· выполните условное форматирование для столбца с данными о количестве отработанных лет, выделите данные и определите три условия. Если ячейка содержит значения, меньше указанного минимума, то для ее выделения используйте красный цвет, желтый - для значений, находящихся в указанном диапазоне, и зеленый - для значений, превышающих ожидаемый максимум;
· выполните фильтрацию данных и отобразите сотрудников, которые приняты на работу с 1980 года по 1990 год; далее отобразите сотрудников, возраст которых больше 30 лет, стаж работы более 5 лет и отчество начинается с буквы «И».
Исходные данные для расчета взносов в пенсионный фонд:
Дата расчета: (вводится пользователем)
| Фамилия | Имя | Отчество | Дата рождения | Дата приема на работу | Оклад, руб. |
| Иванов | Иван | Иванович | 12.10.1975 | 12.08.1997 | |
| Петров | Петр | Петрович | 10.10.1966 | 15.01.1989 | |
| Цветков | Сергей | Иванович | 14.02.1980 | 01.08.2001 | |
| Акимова | Елена | Сергеевна | 10.12.1956 | 01.06.1979 | |
| Яшин | Алексей | Иванович | 05.12.1983 | 11.01.2002 |
Лабораторная работа 7
Объединение и связывание нескольких рабочих листов
Задание.
1. Загрузите программу Excel. Откройте файл, созданный в лабораторной работе 1.
2. Переименуйте рабочий лист 1, присвойте ему имя «Январь».
3. Скопируйте эту таблицу на рабочий лист 2, присвойте ему имя «Февраль».
4. Скопируйте эту таблицу на рабочий лист 3, присвойте ему имя «Март».
5. Измените в созданных таблицах количество рабочих дней в месяце согласно календарю, а количество отработанных дней каждым человеком - по своему усмотрению.
6. Вставьте, где необходимо, в формулы признак абсолютного адреса. Используйте для этого клавишу F4.
7. На рабочем листе 4 (назовите его «Квартал») создайте таблицу следующего вида:
| А | B | С | D | E | |
| Итоговая ведомость начисления заработной платы за квартал | Дата расчета | (текущая) | |||
| Фамилия | Табельный номер | Оклад, руб. | Отработано, дней | Начислено, руб. | |
| Иванов | |||||
| Петров | |||||
| Сидоров | |||||
| Итого |
8. Заполните столбец D и Е, объединив данные листов «Январь», «Февраль» и «Март» методом консолидации. Создайте связь с исходными данными.
9. Оформите внешний вид (цвет текста, цвет фона, ширина строк и столбцов, форматирование текста) созданной таблицы по своему усмотрению.
10. Запишите созданную ЭТ на диск.
11. Выполните выход из среды Excel.
Лабораторная работа 8
Создание сводных таблиц
Задание.
1. Создайте таблицу на листе 1.