Работа с диаграммами при помощи команд меню.
При активизации диаграммы происходит изменение состава режимов главного меню, появляются специальные режимы, содержащие команды корректировки диаграмм. Вместо меню Данные появляется меню Диаграмма, которая содержит следующие команды:
§ команда «Тип диаграммы» используется для изменения типа диаграммы для отдельной последовательности данных, группы или всей диаграммы в целом;
§ команда «Исходные данные» используется для добавления или изменения выделенного ряда данных или отдельного элемента диаграммы;
§ команда «Параметры диаграммы» используется для изменения стандартных параметров выбранного типа диаграмм. Изменения могут затрагивать такие элементы, как сетка, оси, подписи данных и заголовок диаграммы;
§ команда «Размещение» позволяет выбрать расположение выделенных объектов на листе;
§ команда «Добавить данные» позволяет сделать добавление выделенного ряда данных или точек на диаграмму;
§ команда «Линия тренда» (меню Вставка) позволяет добавить или изменить линию тренда на диаграммах различных типов.
Алгоритм изменения диапазона ячеек, используемого для создания диаграммы:
§ выберите изменяемую диаграмму;
§ выберите команду Исходные данные в меню Диаграмма, а затем выберите вкладку Диапазон данных;
§ убедитесь, что выделена полностью вся ссылка в поле Диапазон данных;
§ на рабочем листе выберите ячейки, содержащие данные, которые должны появиться в диаграмме;
|
|
|
§ чтобы название столбца или строки для новых данных появилось в диаграмме, в выбираемые ячейки нужно включить те, которые содержат это название.
Настройка параметров печати.
Печать готового документа на принтере является заключительным этапом работы с электронными таблицами. Щелчок на кнопке Печать на панели инструментов Стандартная, осуществляет автоматическую печать рабочего листа с параметрами настройки принтера, заданными по умолчанию. По умолчанию область печати совпадает с заполненной частью рабочего листа и представляет собой прямоугольник, примыкающий к верхнему левому углу рабочего листа. Если эти параметры надо изменить, то используется команда Файл - Печать, которая открывает диалоговое окно Печать.
Задание области печати. Если часть данных не нужно выводит на печать, то можно задать область печати. Область печати – это заданный диапазон ячеек, который выдается на печать вместо рабочего листа
Создание формулы массива
При вводе формулы массива Excel автоматически заключает ее в фигурные скобки { }.
§ Если формула массива возвращает одно значение, укажите ячейку, в которую необходимо ввести формулу. Если формула массива возвращает несколько значений, выделите диапазон ячеек, в которые необходимо ввести формулу.
|
|
|
§ Наберите формулу.
§ Нажмите клавиши CTRL+SHIFT+ENTER.
Практическая работа.
Практическая работа № 1.
Цель работы: создание и сохранение электронной таблицы (рабочей книги). Изучение способов работы с данными в ячейке (форматирование содержимого ячеек, выбор диапазона ячеек и работа с ними, редактирование содержимого ячеек). Изучение возможностей автозаполнения.
Задание: создайте в новой рабочей книге таблицу №1 «Экзаменационная ведомость». Оформите шапку таблицы. Заполните таблицу данными, а порядковые номера студентов введите при помощи автозаполнения.
Методика выполнения работы:
1. Создайте новую рабочую книгу(кнопка Создать на стандартной панели инструментов или меню Файл команда Создать.).
2. Переименуйте текущий рабочий лист (дважды щелкнуть на ярлыке текущего рабочего листа, и переименовать его).
3. Добавьте еще один рабочий лист в рабочую книгу (щелкнуть правой кнопкой мыши на ярлыке листа и в контекстном меню выберать команду Добавить).
4. Сохраните созданный вами файл под именем book.xls в своем каталоге (меню Файл команда Сохранить).
|
|
|
5. Создайте таблицу по предложенному образцу. Для этого следует:
- в ячейку А1 ввести заголовок таблицы «Экзаменационная ведомость».
- в ячейку А3 ввести «№ п/п».
- в ячейку В3 ввести «Фамилия, имя, отчество».
- в ячейку С3 ввести «№ зачетной книжки».
- в ячейку D3 ввести «Оценка».
- в ячейку Е3 ввести «Фамилия экзаменатора».
Пример выполнения задания №5.
ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ Таблица №1
| № п\п | Фамилия, имя, отчество | № зачётной книжки | Оценка | Фамилия экзаменатора |
| 1 | Иванов И. И. | 120 | Иващенко И.И. | |
| 2 | Петров В.В. | 131 | Иващенко И.И. | |
| 3 | Сидоров С. С. | 145 | Иващенко И.И | |
| 4 | Фёдоров Ф. Ф. | 119 | Иващенко И.И | |
| 5 | Фролов Е. Е. | 149 | Иващенко И.И | |
| 6 | Демидов Д. Д. | 121 | Иващенко И.И |
6. Отформатируйте ячейки шапки таблицы. (Выделить блок ячеек (А3:Е3); выполнить из меню Формат команду Ячейки и перейти по вкладке Выравнивание. В диалоговом окне выбрать опции: Горизонтальное – по центру; Вертикальное - по верхнему краю; переключатель - Переносить по словам, а по вкладке Шрифт изменить начертание букв и размер шрифта).
7. Измените ширину столбцов, в которые не поместились введенные данные. Для этого можно перетащить границы между строками и столбцами или, навести указатель мыши на границу между заголовками столбцов, дважды щелкнуть основной кнопкой мыши. Для более точной настройки надо выбрать команду Строка (Столбец) из меню Формат и активизировать подходящую команду из открывающегося меню.
|
|
|
8. Обрамите таблицу (Панель инструментов à кнопка Обрамление).
9. Присвойте каждому студенту свой порядковый номер, используя маркер заполнения. Для этого сделайте текущей первую ячейку столбца «№ п/п» и введите в нее цифру 1. Затем заполните цифрой 2 следующую ячейку этого столбца. Выделите блок, состоящий из двух заполненных ячеек. Установите указатель мыши на правый нижний угол выделенного блока. Указатель мыши станет черным крестиком - это маркер заполнения. Перетащите маркер заполнения при нажатой правой кнопке мыши вниз. Или выберите команду Правка à Заполнить à Прогрессия).
10. Заполните столбец «Фамилия экзаменатора». Воспользуйтесь методом автозавершения, который состоит в том, что Excel «угадывает» слово, которое собирается вводить пользователь или заполните ячейки с помощью маркера заполнения.
11. Скопируйте таблицу на другой рабочий лист при помощи буфера обмена. Для этого необходимо выделить таблицу или диапазон ячеек, правой клавишей мыши вызвать контекстное меню, выполнить команду Копировать. Затем перейти на другой лист, установить курсор в первую ячейку предполагаемой таблицы и выполнить команду Вставить из контекстного меню.
12. Добавьте в новую таблицу одну строку и один столбец. (Для этого выделить диапазон ячеек по столбцу, щелкнуть правой кнопкой мыши и в открывшемся меню выбрать команду Добавить ячейки. То же самое повторить для строки)
13. Внесите в таблицу ряд изменений: очистите колонку с фамилией экзаменатора, и озаглавьте ее «Подпись экзаменатора».
14. Отсортируйте в новой таблице столбцы 2 и 3 по возрастанию. (меню Данные команда Сортировка или на Стандартной панели инструментов кнопка Сортировать по возрастанию (Сортировать по убыванию)).
15. Сохраните созданный документ.
Практическая работа № 2.
Цель работы: создание и использование простых формул в Excel.
Задание: торговая фирма имеет в своем ассортименте следующий товар: телевизоры стоимостью $300, видеомагнитофоны стоимостью $320, музыкальные центры стоимостью $550, видеокамеры стоимостью $700, видеоплееры стоимостью $198, аудиоплееры стоимостью $40. В январе было продано телевизоров - 10, видеомагнитофонов – 5, музыкальных центров – 6, видеокамер – 2, видеоплееров – 7, аудиоплееров –4. используя возможности Excel. Требуется найти сумму выручки от продаж в рублях и долларах.
Методика выполнения работы:
1. Создайте таблицу, внесите в неё исходные данные задачи.
2. Для подсчета выручки от продажи в долларах в яейки столбца D внесите соответствующие формулы. В формулах использована относительная адресация ячеек. Формула вводится лишь в одну ячейку, а остальные формулы в столбце получены при помощи автозаполнения.
3. Подсчитайте выручку от продажи в рублях. В формулах использована смешанная и абсолютная адресация ячеек. Для введения абсолютного и смешанного адреса необходимо после введения ссылки нажать клавишу F4 и выбрать из предлагаемых вариантов нужный.
4. Подсчитайте сумму выручки от продажи всех видов товаров. (выделить столбец и нажать кнопку Автосумма на стандартной панели инструментов или установить курсор в последнюю ячейку столбца Е в строку «Итого» и воспользоваться кнопкой Вставка функции, расположенной также на стандартной панели, в окне Мастера функций следует выбрать СУММ из категории Математические).
Пример выполнения задания №2.
Таблица 2
| А | В | С | D | Е | F | G |
| 1 | Наименование продукции | Цена за ед., долл. | Продано, шт. | Выручка от продажи в долл. | Выручка от продажи в руб. | Курс долл. |
| 2 | Телевизоры | 300 | 10 | =C3*D3 | =$E3*$G$3 | 27.1 |
| 3 | Видеомагнитофоны | 320 | 5 | =C4*D4 | =$E4*$G$3 | |
| 4 | Музыкальные центры | 550 | 6 | =C5*D5 | =$E5*$G$3 | |
| 5 | Видеокамеры | 700 | 2 | =C6*D6 | =$E6*$G$3 | |
| 6 | Видеоплееры | 198 | 7 | =C7*D7 | =$E7*$G$3 | |
| 7 | Аудиоплееры | 40 | 4 | =C8*D8 | =$E8*$G$3 | |
| 8 | Итого сумма выручки | =СУММ(E3:E8) | =СУММ(F3:F8) |
Практическая работа № 3
Цель работы: построение диаграмм
Задание: на основе приведенных данных таблицы №3 постройте несколько типов диаграмм, наглядно показывающих итоги сессии.
Сведения о результатах сдачи сессии на факультете. Таблица № 3.
| Средний балл по группе | ||||
| Группа | Информатика | Математический анализ | История | Экономика |
| И-123 | 4,2 | 3,8 | 4,5 | 4,3 |
| И-124 | 4 | 4,4 | 4,4 | 4,2 |
| И-125 | 3,9 | 4 | 4 | 3,9 |
| И-126 | 4,3 | 4,4 | 4,4 | 4,1 |
| И-127 | 3,8 | 4 | 4 | 3,9 |
| И-128 | 3,3 | 3,9 | 3,9 | 3,6 |
| И-129 | 4,5 | 4,8 | 4,8 | 3,9 |
Методика выполнения работы:
8. На листе 1 создайте таблицу «Сведения о результатах сдачи сессии на факультете», внесите в нее данные.
9. Постройте диаграмму для всех групп и всех предметов на отдельном листе тип Гистограмма (выделить всю таблицу, выполнить команду меню Вставка – Диаграмма или воспользоваться кнопкой Мастер диаграмм на стандартной панели инструментов).
10. На третьем шаге построения диаграммы внесите название диаграммы, обозначения осей, добавьте легенду.
11. Постройте диаграммы и сравнить результаты сдачи по предметам: информатика, математический анализ и экономика (выделить столбцы «Группа», «Информатика», «Математический анализ» и, удерживая клавишу Ctrl, выделить столбец «Экономика», выбрать тип диаграммы График).
12. Измените результаты сдачи сессии и проверить, как это отразилось на построенных диаграммах.
13. Отчет о работе представьте в виде диаграмм на отдельных листах рабочей книги.
Практическая работа № 4.
Цель работы: используя тематику финансового и банковского менеджмента, изучить создание и использование простых формул и возможности представления данных для визуального анализа результатов.
Задание: сопоставьте доходность акции по уровню дивидендо в за 1999 год по отдельным эмитентам. Исходные данные задачи представлены в таблице №4:
(NA) - номинал акции;
(CP) - цена продажи ;
(Div) - дивиденды, объявленные в расчёте на год .
Доходность акций по отдельным эмитентам. Таблица №4
| Эмитент | Номинал акции (в руб.) | Цена продажи (в руб.) | Дивиденды, объявленные в расчёте на год | Доходность по дивидендам | ||
| NA | CP | В % Div | В руб. DivR | К номиналу DN | Фактическая DF | |
| Сибирьгазбанк | 10000 | 17780 | 400% | |||
| Инкомбанк | 10000 | 22900 | 400% | |||
| Сургутнефтегазбанк | 5000 | 5600 | 320% | |||
| Нефтехимбанк | 1000 | 2015 | 653% | |||
| Сбербанк | 1000 | 2482 | 736% | |||
| КБ Аккобанк | 1000 | 1000 | 325% | |||
| СКБ банк | 50000 | 27050 | 360% | |||
| Промстройбанк | 1000 | 1200 | 1535 | |||
Методика выполнения работы:
1. В соответствующие столбцы введите формулы для расчёта выходных показателей:
| DivR(i)=NA(i)*Div(i); DN(i)=Div(i); DF(i)=DivR(i)/CP(i), |
где i=[1,N], N – число рассматриваемых эмитентов.
2. На основании исходного документа «Доходность акций по отдельным дивидендам» рассчитайте следующие значения:
§ средняя цена продажи акций по всем эмитентам (выделить столбец «Цена продажи» без заголовка, вызвать из стандартной панели Мастер функций à категория Статистическая à функция =СРЗНАЧ;
§ максимальная цена продажи акций по всем эмитентам (выделить столбец «Цена продажи» без заголовка, вызвать из стандартной панели Мастер функций à категория Статистическая à функция =МАКС;
§ минимальная цена продажи акций (выделить столбец «Цена продажи» без заголовка, вызвать из стандартной панели Мастер функций à категория Статистическая à функция =МИН;
§ максимальная фактическая доходность акций по уровню дивидендов (выделить столбец «Фактическая доходность» без заголовка, вызвать Мастер функций à категория Статистическая à функция =МАКС;
§ минимальная фактическая доходность акций по уровню дивидендов (выделить столбец «Фактическая доходность» без заголовка, вызвать Мастер функций à категория Статистическая à функция =МАКС;
Результаты расчётов оформите в виде Таблицы №5:
Таблица № 5
| Расчётная величина | Значение |
| Средняя цена продажи акций | |
| Максимальная цена продажи акций | |
| Минимальная цена продажи акций | |
| Максимальная фактическая доходность акций | |
| Минимальная фактическая доходность акций |
3. В исходной таблице отсортируйте записи в порядке возрастания фактической доходности по дивидендам (выделить таблицу без заголовков и строки «Среднее значение», выполнить команду Сортировка меню Данные).
4. Выполните фильтрацию таблицы, выбрав из неё только тех эмитентов, фактическая доходность которых больше средней по таблице. Алгоритм фильтрации следующий:
· выделить данные таблицы с прилегающей одной строкой заголовка;
· выполнить команду Фильтр – Автофильтр меню Данные);
· в заголовке столбца «Фактическая доходность» нажать кнопку раскрывающегося списка и выбрать Условие;
· в окне пользовательского автофильтра задать условие > «среднее значение».
5. Результаты фильтрации поместите на новый рабочий лист, включив в него следующие графы:
§ эмитент;
§ номинал акции;
§ цена продажи;
§ доходность по дивидендам фактическая.
6. Постройте на отдельном рабочем листе EXCEL круговую диаграмму, отражающую фактическую доходность по дивидендам каждого эмитента в виде соответствующего сектора. (выделить столбцы «Эмитент» и «Фактическая доходность», выполнить команду меню Вставка à Диаграмма). На графике показать значения доходности, вывести легенду и название графика «Анализ фактической доходности акций по уровню дивидендов»
7. Постройте на новом рабочем листе Excel смешанную диаграмму, в которой представьте в виде гистограмм значения номиналов и цены продажи акций каждого эмитента, а их фактическую доходность покажите в виде линейного графика на той же диаграмме. Выведите легенду и название графика «Анализ доходности акций различных эмитентов». Алгоритм построения смешанного графика следующий:
· выделить столбцы «Эмитент», «Номинал акции» и «Цена продажи»;
· выполнить команду меню Вставка à Диаграмма à тип диаграммы Гистограмма.
· для добавления линейного графика «Фактическая доходность по дивидендам» правой клавишей мыши активизировать меню Диаграмма à Исходные данные à во вкладке Ряд выбрать кнопку Добавить, в поле Имя ввести название ряда «Доходность», в поле Значения ввести числовой интервал соответствующий фактической доходности по дивидендам;
· на полученной диаграмме курсор мыши установить на столбец, соответствующий значению «Доходность», правой клавишей мыши активизировать контекстное меню, выбрать команду Тип диаграммы, где выбрать типдиаграммы - График.);
8. Отчет о работе представьте в виде таблиц результатов расчётов и диаграммы на отдельных листах рабочей книги.
Практическая работа № 5. (не делаем)
Цель работы. сортировка данных в списке.
Задание: последовательно, используя сочетание признаков, выполнить сортировку данных таблицы №6 по возрастанию кода предмета, даты проведения занятия, номера группы.
Методика выполнения работы:
- Создайте новую рабочую книгу (меню Файл команда Создать) и сохранить её под именем SORT.XLS в рабочем каталоге (меню Файл команда Сохранить как)
2. Сформируйте таблицу:
Результаты занятий. Таблица №6
| A | B | C | D | E | F | G | H |
| 1 | № группы | № зачётной книжки | Код предмета | Таб. № препод. | Вид занятия | Дата | оценка |
| 2 | 133 | 1 | П1 | A1 | Практика | 26.05.99 | 3 |
| 3 | 134 | 2 | П2 | A2 | Лекция | 26.05.99 | 4 |
| 4 | 133 | 1 | П1 | A1 | Лекция | 11.06.99 | 4 |
| 5 | 134 | 2 | П1 | A2 | Лекция | 11.06.99 | 5 |
| 6 | 135 | 3 | П2 | A1 | Практика | 16.05.99 | 2 |
| 7 | 133 | 4 | П2 | A3 | Лекция | 20.05.99 | 3 |
| 8 | 133 | 4 | П1 | A1 | Лекция | 16.05.99 | 3 |
| 9 | 135 | 3 | П1 | A3 | Лекция | 16.05.99 | 4 |
| 10 | 133 | 5 | П1 | A2 | Лекция | 26.05.99 | 4 |
| 11 | 135 | 5 | П2 | A1 | Лекция | 11.06.99 | 2 |
| 12 | 135 | 5 | П1 | A2 | Практика | 20.05.99 | 5 |
| 13 | 136 | 6 | П2 | A1 | Лекция | 26.05.99 | 5 |
| 14 | 136 | 6 | П2 | A2 | Практика | 11.06.99 | 5 |
| 15 | 135 | 3 | П1 | A3 | Лекция | 20.05.99 | 4 |
| 16 | 135 | 3 | П1 | A1 | Практика | 16.05.99 | 3 |
| 17 | 134 | 2 | П2 | A2 | Лекция | 20.05.99 | 4 |
3. Отформатируйте шапку таблицы следующим образом: шрифт Times New Roman, размер шрифта 12 пт., курсив; выравнивание по горизонтали По значению, по вертикали По верхнему краю, установить ключ «Переносить По Словам» (выделить соответствующие ячейки и выполнить команду Формат- Ячейки).
4. Выполните сортировку по столбцу «Код предмета» расположив коды предметов по возрастанию (выделить таблицу с одной строкой заголовка, выполнить команду Данные – Сортировка, в окне Сортировка диапазона в строке Сортировать по выбрать «Код предмета»).
5. Результат сортировки скопируйте на Лист2 (выделить всю таблицу, выполнить команду Правка - Копировать, а затем на Листе 2 установить курсор в ячейку А1 и выполнить команду Правка -Вставить).
6. Переименуйте Лист2, дав ему имя – Сортировка1 (указатель мыши установить на ярлычке Лист2, правой клавишей мыши вызвать контекстное меню, выполнить команду Переименовать)
7. Выполните сортировку по столбцу «Дата», расположив данные по возрастанию. Для этого следует: установить курсор в любую ячейку поля «Дата» и ввести команду Сортировка из меню Данные. При этом должна выделиться вся область списка, а в окне Сортировка Диапазона в строке Сортировать по – столбец G. Если этого не произошло, то предварительно выделите весь список, а затем выполните указанную команду;
8. Выполните сортировку по сочетанию признаков «Дата», «№ группы», «Код предмета». Для этого следует выделить всю таблицу и в диалоговом окне Сортировка установить:
§ В строке Сортировать по – поле «Дата» по возрастанию;
§ В строке Затем – поле «№ группы», по возрастанию;
§ В следующей строке Затем – поле «Код предмета», по возрастанию;
§ Установите флажок Строка меток столбцов.
§ Результат сортировки скопировать на Лист 3 и переименовать его в Сортировка 2.
Практическая работа № 6. (не делаем)
Цель работы: ознакомиться со способом фильтрации записей списка, автофильтрации, работой с формой данных.
Задание: выполнить по заданным условиям отбора фильтрацию. Создайте на основе Таблицы № 6 форму данных.
Методика выполнения работы:
1. Работа выполняется на основании Таблицы № 6.
Автофильтрация
1. Выделите блок ячеек таблицы, начиная с имён заголовков полей вниз до конца записей , и скопируйте их в новую рабочую книгу с названием «Фильтрация».
2. Переименуйте Лист1, присвоив ему имя «автофильтр №1».
3. Установите курсор в область списка и выполните команду Данные – Фильтр - Автофильтр.
4. Сформируйте условия отбора – для преподавателя А1 выбрать сведения о сдаче экзамена на положительную оценку, вид занятий – Лекция. Для этого выполните следующие действия:
§ в столбце Таб № препод. Нажмите кнопку Фильтра, из списка условий отбора выберите А1;
§ в столбце Оценка нажмите кнопку Фильтра, из списка условий отбора выберите Условие и в диалоговом окне сформируйте условие отбора >2;
§ в столбце Вид занятий нажмите кнопку Фильтра, из списка условий отбора выберите Лекция;
5. Результат фильтрации скопируйте на новый лист, присвоив ему имя – «автофильтр №2».
6. На листе «Фильтрация» результат автофильтрации отменить, установив указатель мыши в область списка и выполнив команду Данные – Фильтр - Автофильтр.
7. Сформулируйте выборку – для группы 133 получите сведения о сдаче экзамена по предмету П1 на оценки 3 и 4.
8. Результат сохраните на новом листе, присвоив ему имя «автофильтр №3».
Форма данных
9. Скопируйте исходную таблицу на новый рабочий лист, переименовав его в «Форма данных».
10. Установите курсор в область списка и выполните команду Данные - Форма.
11. Просмотрите записи списка и внесите необходимые изменения по своему усмотрению с помощью кнопок <Предыдущая> и <Следующая>.
12. С помощью кнопки <Создать> добавьте новые записи.
13. Сформируйте первое условие отбора, приведённое в задании. Для этого нажмите кнопку <Критерии>, название которой поменяется на <Правка>. В пустых строках имён полей списка введите критерии:
в строку Таб. № препод. введите А1;
в строку вид занятия введите Лекция;
в строку оценка введите условие > 2.
14. Просмотрите отобранные записи, нажатием на кнопку <Предыдущая> или <Следующая>.
15. По аналогии сформулируйте условия отбора записей, указанные в задании №7.
Практическая работа № 7. (не делаем)
Цель работы. создание базы данных средствами Excel. Сортировка данных, выборка по различным критериям, поиск записи. Автоматическое подведение итогов.
Задание: Создайте таблицу по предложенному образ, заполните базу данных (до 20 записей). Выполните различные виды сортировки данных
Методика выполнения работы:
1. Создайте таблицу по предложенному образцу.
| № п/п | Фамилия | Имя | Отчество | Дата рождения | Адрес | Телефон | Оклад | Налоги | Сумма к выдаче | ||||||
| Город | Улица | Дом | Корпус | Кварт. | Проф. | Пенсион. | Подоход. | ||||||||
2. Для ячеек «Дата рождения» установите формат Дата (Формат – Ячейка – Число).
3. Для ячеек «Дом», «Квартира» установите числовой формат.
4. Для ячеек «Телефон» установите формат Номер телефона (Формат – Ячейка – Дополнительный – Номер телефона).
5. Для ячеек «Оклад», «Налоги», «Сумма к выдаче» установите Денежный формат.
6. В ячейку столбца «Налоги профсоюзные» внесите формулу для подсчёта налогов.
7. В ячейку столбца «Налоги пенсионные» внесите соответствующую формулу.
8. В ячейку столбца «Налоги подоходные» внесите формулу «12 % от оклада за вычетом минимальной заработной платы и пенсионного налога». Минимальную заработную плату принять равной 140 рублям.
9. Внесите первую запись. Начиная со второй записи, заполните таблицу, используя команду меню Данные – Форма. Перед использованием команды выделите первую запись таблицы и прилегающую к ней строку заголовка. Таблица должна содержать не менее 20 записей.
10. Выполните сортировку данных по фамилии (Данные – Сортировка) результат сортировки сохранить на Листе 2.
11. Отсортируйте исходные данные по возрастанию окладов, результат сохраните на Листе3.
12. Получите список людей проживающих по улице Мира (Данные – Фильтр – Автофильтр) результат сохраните на Листе 4.
13. Получите список людей, телефоны которых начинаются на 35, результат сохраните на Листе5.
Практическая работа№ 8.
Цель работы. Работа с матрицами. Работа с панелью инструментов. Создание структуры таблицы.
Задание: введите исходную матрицу; умножьте её на число; исходную и полученную при умножении матрицы перемножьте между собой; для исходной матрицы найдите: определитель и обратную матрицу, а так же транспонированную матрицу. Создайте собственную панель инструментов, в которой разместите все кнопки, которые были использованы при работе. Любую кнопку в созданной вами панели инструментов отредактируйте. Все расчётные данные представьте в виде структурной таблицы.
Методика выполнения работы:
1. Введите в диапазон ячеек В3:D5 произвольные числовые значения, которые будут представлять исходную матрицу.
2. Умножьте исходную матрицу (В3:D5) на число 10. Число 10 занесите в отдельную ячейку F4. Выделите блок ячеек H3:J5 и введите в строку формул формулу = В3:D5*F4, а затем представить формулу в виде массива, для этого нажмите комбинацию клавиш Ctrl+Shift+Enter.
3. Умножьте две матрицы (матрицу В3:D5 и матрицу H3:J5) и результат получите в ячейках В8:D10. Для умножения матриц используется функция МУМНОЖ из категории Математические функции.
4. В ячейке В13 найдите определитель исходной матрицы (Мастер функций à категория функции Математические à функция МОПРЕД).
5. Найдите транспонированную матрицу в ячейках В15:D17 для исходной матрицы. Для транспонирования матриц используется функция ТРАНСП из категории Математические функции.
6. В ячейках В20:D22 вычислите матрицу обратную к исходной и докажите, что она является обратной (Мастер функций à категория функции Математические à функция МОБР. Эта функция возвращает обратную матрицу, а для доказательства надо исходную матрицу и обратную матрицу перемножить в ячейках H20:J22 и получить единичную матрицу).
7. Создайте структуру таблицы (меню Данные à команда Группа и структура à выделить часть таблицы, которая будет включена в структуру и активизировать команду Группировать).
Структура таблицы должна иметь вид дерева:
§ 1 уровень Общая расчётная область;
§ 2 уровень Умножение исходной матрицы на число.
§ 3 уровень Умножение двух матриц.
§ 4 уровень Нахождение определителя матрицы.
§ 5 уровень Транспонирование матрицы.
§ 6 уровень Нахождение обратной матрица и доказательство, что данная матрица является обратной.
8. Создайте собственную панель инструментов, поместив в неё все кнопки, которыми вы пользовались при выполнении предыдущих пунктов практической работы. Для создания собственной панели инструментов выполните следующие действия:
§ В меню Вид à выберете команду Панели инструментов à Настройка;
§ В диалоговом окне Настройка выберете вкладку Панели инструментов и нажмите кнопка Создать;
§ В диалоговом окне Создание панели инструментов задайте имя Вашей панели, например «Личная», нажмите кнопку ОК, созданная панель отобразится на экране;
Для вставки в панель инструментов кнопок, которыми вы пользовались при выполнении практической работы, выполните следующее действие:
§ В диалоговом окне Настройка выберете вкладку Команды, и в списке Категории выбираете нужную категорию, а в ней выбирается соответствующая команда и кнопка соответствующая данной команде вставляется, при помощи перетаскивания, в созданную вами панель инструментов.
9. На любой кнопке, из помещаемых в собственную панель инструментов, отредактируйте изображение. Для редактирования изображения на кнопке необходимо выполнить следующие действия:
§ В меню Вид à выберете команду Панели инструментов à Настройка;
§ В диалоговом окне Настройка выберете вкладку Команды, а из списка Категории выберете нужную команду;
§ Вставьте в созданную вами панель выбранную команду, а в диалогов окне Настройка выберете кнопку Изменить выделенный объект. Далее отредактируйте изображение кнопки по точкам.
Практическая работа № 9
Цель работы: ознакомиться с использованием логических функций для решения задач.
Задание: средствами Excel создать зачетную ведомость и подсчитать количество оценок: пятерок, четверок и т.д.
Методика выполнения работы:
1. На новом листе рабочей книги создайте Таблицу 8, заполнив первый, второй, третий и четвёртый столбцы данными.
2. В шестой, седьмой, восьмой, девятый и десятый столбцы введите формулы логических функций. Для этого воспользуйтесь Мастером функций.
· установите курсор в первую ячейку столбца отличных оценок и активизируйте Мастер функций;
· в первом диалоговом окне выберите категорию функции и название функции;
| Категория: | Логические функции |
| Имя функции: | ЕСЛИ |
· щелкните на кнопке Готово;
· во втором диалоговом окне установите курсор в поле Логическое выражение и щелкните мышью на ячейке D2 (Оценка) в рабочем поле Excel;
· с клавиатуры введите «=5»;
· в поле Значение_если_истина введите 1;
· в поле Значение_если_ложь введите 0
· нажмите кнопку Готово;
· методом протягивания скопируйте формулу по столбцу «Количество 5» вниз.
3. С помощью Мастера функций аналогичным способом введите формулы в столбцы «Количество 4», «Количество 3», и т.д., изменяя значения поля Логическое выражение, соответственно: «D2=4», «D2=3» и т.д.
Таблица 8
| A | B | C | D | E | F | G | H | I | J |
| № п / п | Фамилия, имя, отчество | № зачётной книжки | Оценка | Подпись экзаменатора | Кол-во 5 | Кол-во 4 | Кол-во 3 | Кол-во 2 | неявка |
| 1 | Демидов М. И. | 119 | 5 | ||||||
| 2 | Иванов И. П. | 120 | 4 | ||||||
| 3 | Кукушкин В. Л. | 121 | 3 | ||||||
| 4 | Орлов А. П. | 131 | 4 | ||||||
| 5 | Петров К. Н. | 145 | 5 | ||||||
| 6 | Сидоров Р. О. | 149 | 2 | ||||||
| 7 | Фролов В. А. | 156 | н/я |
4. Чтобы подсчитать сумму всех пятерок, четверок и т.д. и результаты представить в виде отдельной таблицы, нужно по каждому столбцу количества оценок задать имена блокам ячеек. Для этого выполните следующие действия:
· выделите ячейки 1-7 столбца «Количество пятерок»;
· выполните команду Вставка - Имя - Присвоить;
· В диалоговом окне Присвоение имени в строке Имя введите слово ОТЛИЧНО и щелкните на кнопке Добавить;
· выделите ячейки 1-7 столбца «Количество четверок» и выполните команду Вставка – Имя – Присвоить;
· в диалоговом окне Присвоение имени в строке Имя введите слово ХОРОШО;
· аналогичные действия выполните с остальными столбцами таблицы 8, создав имена блоков ячеек: УДОВЛЕТВОРИТЕЛЬНО, НЕУДОВЛЕТВОРИТЕЛЬНО, НЕЯВКА.
5. Создайте таблицу Итоги сессии (табл. 9).
6. Введите формулу подсчёта количества полученных оценок определённого вида, используя имена блоков ячеек с помощью Мастера функций:
· установите указатель мыши в клетку подсчета количества отличных оценок;
· щелкните по кнопке Мастера функций;
· в первом диалоговом окне выберите : категорию функции - Математические , имя функции - СУММ и щелкните на кнопке ОК;
· во втором диалоговом окне установите в строку Число 1 курсор и введите команду Вставка - Имя - Вставить;
· В диалоговом окне Вставка имени выберите имя блока ячеек ОТЛИЧНО и щелкните на кнопке ОК;
· Повторить аналогичные действия для подсчёта количества других оценок.
8. Подсчитайте общее количество всех полученных оценок, используя кнопку Автосумма на стандартной панели инструментов..
Таблица 9
| Итоги сессии | |
| Количество отличных оценок | |
| Количество хороших оценок | |
| Количество удовлетворительных оценок | |
| Количество неудовлетворительных оценок | |
| Неявки | |
| ИТОГО | |
Практическая работа № 10.
Цель работы: ознакомиться с применением логических функций для решения задач.
Задание: определить, в какой из интервалов, заданных в таблице №8, попадает зарплата каждого сотрудника НИИ.
Методика выполнения работы:
1. Создать новую рабочую книгу.
2. Создать таблицу из восьми столбцов, в которой содержатся сведения о семи сотрудниках НИИ: № п/п, Ф.И.О., ежемесячная зарплата (Таблица 7).
3. Также создать таблицу, содержащую четыре интервала числовых значений зарплат (1000 – 2000, 2000 – 3000, 3000 – 4000, 4000 - 6000) (Таблица №8).
4. Чтобы определить попадает ли значение зарплаты из столбца С в заданный интервал, следует использовать логическую функцию ЕСЛИ с заданием сложного условия И. Для этого следует выполнить алгоритм, показанный на рис. 13:
· установить курсор в ячейку D2;
· щелкнуть на значке Вставка функции Стандартной панели инструментов;
· в окне Мастера функций выбрать Категорию функции Логические, в окне Вид функции - выбрать функцию ЕСЛИ, нажать кнопку ОК;
· в строке формул в раскрывающемся списке выбрать функцию И;
· установить курсор в поле Логическое 1;
· на рабочем поле щелкнуть на ячейке C2;
· с клавиатуры ввести >=;
· на рабочем поле щелкнуть на ячейке А10;
· установить курсор в поле Логическое 2;
· ввести С2<В10;
· не закрывая окно Функции И щелкнуть на слове Если в строке формул – откроется окно функции Если;
· в поле Значение_если_истина с клавиатуры ввести 1;
· в поле Значение_если_ложь с клавиатуры ввести 0;
· нажать кнопку ОК.
Дата добавления: 2019-02-12; просмотров: 803; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!