Материалы представленные на сайте исключительно с целью ознакомления пользователям Интернета и не преследуют коммерческих целей или нарушение авторских прав.
© 2018 - 2024
Цель работы: создание и сохранение электронной таблицы (рабочей книги); изучение способов работы с данными в ячейке (форматирование содержимого ячеек, выбор диапазона ячеек и работа с ними, редактирование содержимого ячеек); изучение возможностей автозаполнения.
Методика выполнения работы
1. Создайте новую рабочую книгу (кнопка Создать на стандартной панели инструментов или меню Файл –> команда Создать).
2. Переименуйте текущий рабочий лист (дважды щелкните на ярлыке текущего рабочего листа и введите его новое название «Ведомость»).
3. Добавьте еще один рабочий лист в рабочую книгу (щелкните правой кнопкой мыши на ярлыке (например, Лист 1) внизу листа и в контекстном меню выберите команду Добавить).
4. Сохраните созданный вами документ в файле Book.xls в своем каталоге (команда Сохранить в меню Файл).
5. Создайте таблицу по предложенному образцу (см. табл. 1). Для этого выполните следующие действия:
в ячейку А1 введите заголовок таблицы «Экзаменационная ведомость»;
в ячейку А3 введите «№ п/п»;
в ячейку ВЗ введите «Фамилия, имя, отчество»;
в ячейку СЗ введите «Номер зачетной книжки»;
в ячейку DЗ введите «Оценка»;
в ячейку ЕЗ введите «Фамилия экзаменатора».
Пример выполнения пятого пункта задания
Таблица 1
Экзаменационная ведомость
| № п\п | Фамилия, имя, отчество | Номер зачетной книжки | Оценка | Фамилия экзаменатора |
| 1 | Иванов И. И. | 120 | Иващенко И.И. | |
| 2 | Петров В.В. | 131 | Иващенко И.И. | |
| 3 | Сидоров С. С. | 145 | Иващенко И.И. | |
| 4 | Федоров Ф. Ф. | 119 | Иващенко И.И. | |
| 5 | Фролов Е. Е. | 149 | Иващенко И.И. | |
| 6 | Демидов Д. Д. | 121 | Иващенко И.И. |
6. Отформатируйте ячейки в головке таблицы:
– выделите блок ячеек (А3 : Е3);
– выполните в меню Формат команду Ячейки и перейдите в окно Выравнивание;
– в диалоговом окне Выравнивание выберите опции: Горизонтальное – по центру; Вертикальное – по верхнему краю; переключатель – Переносить по словам; используя вкладку Шрифт, измените начертание букв и размер шрифта.
7. Измените ширину столбцов, в которые не поместились введенные данные. Для этого можно перетащить границы между строками и столбцами или навести указатель мыши на границу между заголовками столбцов, дважды щелкнуть основной кнопкой мыши. Для более точной настройки надо выбрать команду Строка (Столбец) из меню Формат и активизировать подходящую команду из открывающегося меню.
8. Выполните команду Обрамление для таблицы (Панель инструментов à кнопка Обрамление).
9. Присвойте каждому студенту свой порядковый номер, используя маркер заполнения. Для этого:
– сделайте текущей первую ячейку столбца «№ п/п» и введите в нее цифру 1;
– затем введите цифру 2 в следующую ячейку этого столбца;
– выделите блок, состоящий из двух заполненных ячеек;
– установите указатель мыши на правый нижний угол выделенного блока, при этом указатель мыши приобретет вид черного крестика (маркер заполнения). Перетащите маркер заполнения при нажатой правой кнопке мыши вниз или выберите команду Правка –> Заполнить —> Прогрессия.
10. Заполните столбец (графу) «Фамилия экзаменатора». Воспользуйтесь методом Автозавершение, который состоит в том, что Ехсеl «подбирает» слово, которое собирается вводить пользователь, или заполните ячейки с помощью Маркера заполнения.
11. Скопируйте таблицу на другой рабочий лист при помощи буфера обмена. Для этого:
– выделите таблицу или диапазон ячеек;
– правой кнопкой мыши вызовите контекстное меню;
– выполните команду Копировать;
– затем перейдите на другой лист;
– установите курсор в первую ячейку предполагаемой таблицы;
– выполните команду Вставить из контекстного меню.
12. Добавьте в новую таблицу одну строку и один столбец. Для этого:
– выделите диапазон ячеек в столбце;
– щелкните правой кнопкой мыши и в открывшемся контекстном меню выберите команду Добавить ячейки; те же команды повторите для строки.
13. Внесите в таблицу ряд изменений:
– очистите графу с фамилией экзаменатора;
– введите новый заголовок для этой графы «Подпись экзаменатора».
14. Отсортируйте в новой таблице данные в графах 2 и 3 по возрастанию (меню Данные —> команда Сортировка или на Стандартной панели инструментов кнопка Сортировать по возрастанию (Сортировать по убыванию)).
Задание № 2
Построение диаграмм
Цель работы: изучение методики построения диаграмм.
На основе данных, приведенных в табл. 2, постройте несколько типов диаграмм, отображающих итоги сессии.
Таблица 2
Средний балл по группе
Методика выполнения работы
1. На листе 1 создайте таблицу «Сведения о результатах сдачи сессии на факультете», внесите в нее данные.
2. Постройте диаграмму для всех групп и всех предметов на отдельном листе типа Столбчатая или График. Для этого:
– выделите всю таблицу;
– выполните команду меню Вставка —> Диаграмма или воспользуйтесь кнопкой Мастер диаграмм на стандартной панели инструментов.
3. На третьем шаге построения диаграммы внесите название диаграммы, обозначения осей, добавьте легенду.
4. Постройте диаграммы и сравните результаты сдачи сессии по предметам: информатика, математический анализ и экономика. Для этого выделите графы «Группа», «Информатика», «Математический анализ» и, удерживая клавишу <Ctrl>, выделите графу «Экономика». Выберите тип диаграммы График.
5. Измените результаты сессии и проверьте, как это отразилось на построенных диаграммах.
6. Отчет о работе представьте в виде диаграмм на отдельных листах рабочей книги.
Задание № 3
Формулы в Ехсе l
Цель работы: создание и использование простых формул в Ехсеl.
Задача 1. Торговая фирма имеет в своем ассортименте товар со следующей стоимостью: телевизоры – $300, видеомагнитофоны – $320, музыкальные центры – $550, видеокамеры – $700, видеоплееры – $198, аудиоплееры – $40. В январе было продано 10 телевизоров, 5 видеомагнитофонов, 6 музыкальных центров, 2 видеокамеры, 7 видеоплееров, 4 аудиоплеера.
Используя возможности Ехсеl, определите сумму выручки от продаж в рублях и долларах.
Методика выполнения работы
1. Создайте таблицу (см. табл. 3), введите в нее исходные данные.
2. Для подсчета выручки от продажи в долларах в ячейки столбца внесите соответствующие формулы. В формулах использована относительная адресация ячеек. Формула вводится лишь в одну ячейку, а остальные формулы в столбце получают при помощи автозаполнения (наводят мышь на угол ячейки E3 и растягивают ее до E8, то же повторяют для ячеек F3 : F8).
Таблица 3
| № строки, столбца | B | C | D | E | F | G |
| 2 | Наименование продукции | Цена за ед., долл. | Продано, шт. | Выручка от продажи, долл. | Выручка от продажи, р. | Курс долл. |
| 3 | Телевизоры | 300 | 10 | =С3*D3 | =$E3*$G$3 | 29.1 |
| 4 | Видеомагнитофоны | 320 | 5 | =C4*D4 | =$E4*$G$3 | |
| 5 | Музыкальные центры | 550 | 6 | =C5*D5 | =$E5*$G$3 | |
| 6 | Видеокамеры | 700 | 2 | =C6*D6 | =$E6*$G$3 | |
| 7 | Видеоплееры | 198 | 7 | =C7*D7 | =$E7*$G$3 | |
| 8 | Аудиоплееры | 40 | 4 | =C8*D8 | =$E8*$G$3 | |
| 9 | Итого сума выручки | =СУММ (E3:Е8) | =СУММ (F3:F8) |
3. Подсчитайте выручку от продажи в рублях. В формулах использована смешанная и абсолютная адресация ячеек. Для введения абсолютного и смешанного адреса необходимо после указания ссылки нажать клавишу <F4> и выбрать нужный вариант из предлагаемых.
4. Подсчитайте сумму выручки от продажи всех видов товаров. Выделите столбец, нажмите кнопку Автосумма на стандартной панели инструментов или установите курсор в последней ячейке столбца Е в строке «Итого сумма выручки» и воспользуйтесь кнопкой Вставка функции, расположенной также на стандартной панели. В окне Мастера функций выберите СУММ из категории Математические.
Задача 2. 1. Изучите создание и использование простых формул, используя тематику финансового и банковского менеджмента.
2. Сопоставьте доходность акции по уровню дивидендов за 1999 г. по отдельным эмитентам. Исходные данные представлены в табл. 4: NA – номинал акции; СР – цена продажи; Div – дивиденды, объявленные в расчете на год.
Таблица 4
| Эмитент | Номинал акции, р. | Цена продажи, р. | Дивиденды, объявленные в расчете на год | Доходность по дивидендам | ||
| NA | CP | Div, % | DivR, р. | к номиналу DN | факти-ческая DF | |
| Сибирьгазбанк | 10 000 | 17 780 | 400 | |||
| Инкомбанк | 10 000 | 22 900 | 400 | |||
| Сургутнефтегазбанк | 5 000 | 5 600 | 320 | |||
| Нефтехимбанк | 1 000 | 2 015 | 653 | |||
| Сбербанк | 1 000 | 2 482 | 736 | |||
| КБ Аккобанк | 1 000 | 1 000 | 325 | |||
| СКБ банк | 50 000 | 27 050 | 360 | |||
| Промстройбанк | 1 000 | 1 200 | 1535 | |||
3. Визуально проанализируйте полученные результаты.
Методика выполнения работы
1. В соответствующие столбцы введите формулы для расчета выходных показателей:
DivR (i) = NA (i) * Div (i);
DN (i) = Div (i);
DF (i) = DivR (i) / CP (i),
где i = [1, N], N – число рассматриваемых эмитентов.
2. На основании исходного документа «Доходность акций по отдельным дивидендам» рассчитайте:
– среднюю цену продажи акций по всем эмитентам (для этого нужно выделить столбец «Цена продажи» без заголовка, вызвать из стандартной панели команду Мастер функций –» категория Статистическая –> функция СРЗНАЧ);
– максимальную цену продажи акций по всем эмитентам (для этого следует выделитъ столбец «Цена продажи» без заголовка, на стандартной панели выбрать команду Мастер функций –» категория Статистическая –» функция = МАКС);
– минимальную цену продажи акций (для этого следует выделить столбец «Цена продажи» без заголовка, на стандартной панели выбрать команду Мастер функций –> категория Статистическая –» функция = МИН);
– максимальную фактическую доходность акций по уровню дивидендов (для этого следует выделить столбец «Фактическая доходность» без заголовка, выбрать команду Мастер функций –» категория Статистическая –» функция = МАКС);
– минимальную фактическую доходность акций по уровню дивидендов (для этого следует выделить столбец «Фактическая доходность» без заголовка, выбрать команду Мастер функций –> категория Статистическая –» функция = МИН).
3. Результаты расчетов оформите в виде табл. 5.
Таблица 5
| Расчетная величина | Значение |
| Средняя цена продажи акций | |
| Максимальная цена продажи акций | |
| Минимальная цена продажи акций | |
| Максимальная фактическая доходность акций | |
| Минимальная фактическая доходность акций |
4. В исходной таблице отсортируйте записи в порядке возрастания фактической доходности по дивидендам (выделите таблицу без заголовков и строки «Среднее значение», выполните команду Сортировка меню Данные).
5. Выполните фильтрацию данных в табл. 5, выбрав из нее только те эмитенты, фактическая доходность которых больше средней по таблице. Алгоритм фильтрации:
– выделите данные таблицы с прилегающей одной строкой заголовка;
– выполните команду Фильтр — Автофильтр меню Данные;
– в заголовке графы «Фактическая доходность» нажмите кнопку раскрывающегося списка и выберите Условие;
– в окне пользовательского Автофильтра задайте условие: >«среднее значение».
6. Результаты фильтрации поместите на новый рабочий лист, включив в него следующие графы:
– эмитент;
– номинал акции;
– цена продажи;
– доходность по дивидендам фактическая.
7. Постройте на отдельном рабочем листе Ехсеl круговую диаграмму, отражающую фактическую доходность по дивидендам каждого эмитента в виде соответствующего сектора (выделите столбцы «Эмитент» и «Фактическая доходность», выполните команду меню Вставка –> Диаграмма). На графике покажите значения доходности, выведите легенду и название графика «Анализ фактической доходности акций по уровню дивидендов».
8. Постройте на новом рабочем листе Ехсеl смешанную диаграмму, в которой представьте в виде гистограмм значения номиналов и продажи акций каждого эмитента, а их фактическую доходность покажите в виде линейного графика на той же диаграмме. Выведите легенду и название графика «Анализ доходности акций различных эмитентов».
Алгоритм построения смешанного графика:
1) выделите столбцы «Эмитент», «Номинал акции» и «Цена продажи»;
2) выполните команду меню Вставка —> Диаграмма —> тип диаграммы Гистограмма;
3) для добавления линейного графика «Фактическая доходность по дивидендам» правой кнопкой мыши активизируйте меню Диаграмма –> Исходные данные –» во вкладке Ряд, выберите кнопку Добавить, в поле Имя введите название ряда «Доходность», в поле Значения введите числовой интервал, соответствующий фактической доходности по дивидендам;
4) на полученной диаграмме установите курсор мыши на столбце, соответствующем значению «Доходность», правой кнопкой мыши активизируйте контекстное меню, выберите команду Тип диаграммы, затем тип диаграммы – График.
9. Подготовьте результаты расчетов и диаграммы к выводу на печать (меню Файл –> команда Печать).
Задание № 4
Сортировка данных в списке
Цель работы: выполнение сортировки данных по одному и более признакам.
1. Выполните сортировку данных в табл. 6 по возрастанию следующих параметров: код предмета, дата проведения занятия, номер группы.
2. Выполните сортировку данных в табл. 6 по возрастанию параметров, используя сочетание двух признаков (код предмета и дата проведения занятия; код предмета и номер группы; номер группы и дата проведения занятия), а также сочетание трех признаков (код предмета, дата проведения занятия и номер группы).
Таблица 6
| А | В | С | 0 | Е | Р | О | Н |
| 1 | Номер группы | Номер зачетной книжки | Код предмета | Таб. номер препод. | Вид занятия | Дата | Оценка |
| 2 | 133 | 1 | П1 | А1 | Практика | 26.05.04 | 3 |
| 3 | 134 | 2 | П2 | А2 | Лекция | 26.05.04 | 4 |
| 4 | 133 | 1 | П1 | А1 | Лекция | 11.06.04 | 4 |
| 5 | 134 | 2 | П1 | А2 | Лекция | 11.06.04 | 5 |
| 6 | 135 | 3 | П2 | А1 | Практика | 16.05.04 | 2 |
| 7 | 133 | 4 | П2 | А3 | Лекция | 20.05.04 | 3 |
| 8 | 133 | 4 | П1 | А1 | Лекция | 16.05.04 | 3 |
| 9 | 135 | 3 | П1 | А3 | Лекция | 16.05.04 | 4 |
| 10 | 133 | 5 | П1 | А2 | Лекция | 11.06.04 | 4 |
| 11 | 135 | 5 | П2 | А1 | Лекция | 20.05.04 | 2 |
| 12 | 135 | 5 | П1 | А2 | Практика | 26.05.04 | 5 |
| 13 | 136 | 6 | П2 | А1 | Лекция | 11.06.04 | 5 |
| 14 | 136 | 6 | П2 | А2 | Практика | 20.05.04 | 5 |
| 15 | 135 | 3 | П1 | А3 | Лекция | 16.05.04 | 4 |
| 16 | 135 | 3 | П1 | А1 | Практика | 26.05.04 | 3 |
| 17 | 134 | 2 | П2 | А2 | Лекция | 20.05.04 | 4 |
Методика выполнения работы
1. Создайте новую рабочую книгу (меню Файл –> команда Создать) и сохраните ее в рабочем каталоге, указав имя SORT.XLS (меню Файл –> команда Сохранить как).
2. Сформируйте таблицу результатов занятий.
3. Отформатируйте головку таблицы, используя следующие параметры:
– шрифт – Times New Roman;
– размер шрифта – 12 пт, начертание – курсив;
– выравнивание параметров по горизонтали – По значению;
– выравнивание параметров по вертикали – По верхнему краю;
– ключ «Переносить по словам» (выделите соответствующие ячейки и выполните команду Формат –> Ячейки).
4. Выполните сортировку данных в графе «Код предмета», расположив коды предметов по возрастанию. Для этого:
– выделите таблицу с одной строкой заголовка;
– выполните команду меню Данные –» Сортировка; в окне Сортировка диапазона укажите Сортировать по «коду предмета»).
5. Результат сортировки скопируйте на Лист 2:
– выделите всю таблицу, выполните команду Правка –> Копировать;
– на Листе 2 установите курсор в ячейку А1 и выполните команду Правка –> Вставить.
6. Переименуйте Лист 2, дав ему имя «Сортировка»:
– указатель мыши установите на ярлыке Лист 2;
– правой клавишей мыши вызовите контекстное меню; выполните команду Переименовать.
7. Выполните сортировку данных в графе «Дата» по возрастанию. Для этого установите курсор в любую ячейку поля Дата и введите команду меню Данные –» Сортировка. При этом выделится вся область списка, а в окне Сортировка Диапазона в строке Сортировать по – столбец G. Если этого не произошло, то предварительно выделите весь список, а затем выполните указанную команду.
8. Выполните сортировку данных по сочетанию следующих признаков: «Дата», «Номер группы», «Код предмета». Для этого выделите всю таблицу, в диалоговом окне Сортировка установите:
– в строке Сортировать по — поле «Дата» по возрастанию;
– в строке Затем — поле «Номер группы» по возрастанию;
– в следующей строке Затем — поле «Код предмета» по возрастанию;
– установите флажок Строка меток столбцов;
– результат сортировки скопируйте на Лист 3 и дайте ему новое имя Сортировка 2.
Задание № 5
Фильтрация записей
Цель работы: ознакомление со способом фильтрации записей списка, автофильтрации, с формами данных.
Методика выполнения работы
1. Создайте новую рабочую книгу с названием «Фильтрация».
2. Скопируйте в новую рабочую книгу табл. 6 (см. задание № 4).
3. Переименуйте Лист 1, присвоив ему имя «Автофильтр № 1».
4. Чтобы применить Автофильтрацию, установите курсор в область списка и выполните команду Данные –> Фильтр –> Автофильтр.
5. Сформируйте условия отбора: для преподавателя А1 выберите сведения о сдаче экзамена на положительную оценку, вид занятий – Лекция. Для этого выполните следующие действия:
– в графе «Таб. номер препод.» нажмите кнопку Фильтр, из списка условий отбора выберите А1;
– в графе «Оценка» нажмите кнопку Фильтр, из списка условий отбора выберите Условие и в диалоговом окне сформируйте условие отбора >2;
– в графе «Вид занятий» нажмите кнопку Фильтр, из списка условий отбора выберите Лекция.
6. Результат фильтрации скопируйте на новый лист, присвоив ему имя «Автофильтр № 2».
7. Установив указатель мыши в область списка и выполнив команду Данные –» Фильтр –» Автофильтр, отмените результат автофильтрации на листе «Автофильтр № 1».
8. Сформулируйте условия выборки для группы 133, получите сведения о сдаче экзамена по предмету П1 на оценки 3 и 4.
9. Результат сохраните на новом листе, присвоив ему имя «Автофильтр № 3».
10. Скопируйте исходную таблицу на новый рабочий лист, дайте ему новое имя «Форма данных».
11. Установите курсор в область списка и выполните команду Данные —> Форма.
12. В окне Форма данных просмотрите записи списка и внесите необходимые изменения по своему усмотрению с помощью кнопок <Предыдущая> и <Следующая>.
13. С помощью кнопки <Создать> добавьте новые записи.
14. В окне Форма данных сформируйте условия отбора записей. Для этого нажмите кнопку <Критерии>, название которой заменится на слово <Правка>. В пустые строки введите критерии:
– в строке «Таб. номер препод.» – «А1»;
– в строке «Вид занятия» – «Лекция»;
– в строке «Оценка» – условие «> 2».
15. Нажав на кнопку <Предыдущая> или <Следующая>, просмотрите отобранные записи.
16. Сформулируйте условия отбора записей (см. пункт 8).
Задание № 6
Создание базы данных
Цель работы: создание базы данных средствами Ехсеl, сортировка данных, выборка данных по различным критериям, поиск записи, автоматическое подведение итогов.
Методика выполнения работы
1. Создайте таблицу по предложенному образцу (см. табл. 7). Для того чтобы надпись расположить вертикально, используйте команду Формат ячеек > Выравнивание > Текст 90 градусов.
Таблица 7
| № п/п | Фамилия | Имя | Отчество | Дата рождения | Адрес | Телефон | Оклад | Налоги | Сумма к выплате | ||||||
| Город | Улица | Дом | Корпус | Кварт. | профсоюзные | пенсионные | подоходные | ||||||||
2. Для ячеек «Дата рождения» установите формат Дата (Формат – Ячейка – Число).
3. Для ячеек «Дом», «Квартира» установите числовой формат.
4. Для ячеек «Телефон» установите формат Номер телефона (Формат —> Ячейка – Дополнительный —> Номер телефона).
5. Для ячеек «Оклад», «Налоги», «Сумма к выдаче» установите Денежный формат.
6. В ячейку графы «Налоги профсоюзные» внесите формулу для подсчета налогов (1 % от оклада).
7. В ячейку графы «Налоги пенсионные» внесите соответствующую формулу (5 % от оклада).
8. В ячейку графы «Налоги подоходные» внесите формулу «13 % от оклада за вычетом минимальной заработной платы и пенсионного налога». Минимальную заработную плату следует принять равной 400 р.
9. Введите первую запись. Начиная со второй записи, заполните таблицу, используя команду меню Данные —> Форма. Перед использованием команды выделите первую запись таблицы и прилегающую к ней строку заголовка. Таблица должна содержать не менее 20 записей.
10. Выполните сортировку данных по фамилии (Данные —> Сортировка), результат сортировки сохраните на Листе 2.
11. Отсортируйте исходные данные по возрастанию окладов, результат сохраните на Листе 3.
12. Создайте список людей, проживающих, например, по улице Мира (Данные —» Фильтр —> Автофильтр), сохраните результат работы на Листе 4.
13. Создайте список людей, у которых первая цифра номера телефона больше 3. Сохраните результат работы на Листе 5.
Задание № 7
Дата: 2019-02-19, просмотров: 243.