Цель работы - Изучить основные приемы работы с электронной таблицей MS Excel. Научиться создавать электронные таблицы средней сложности
Основные понятия и определения
Документ Excel называется рабочей книгой. Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет табличную структуру и может содержать одну или несколько таблиц.
Ввод и редактирование данных.
Форматирование содержимого ячеек. Текстовые данные по умолчанию выравниваются по левому краю ячейки, а числа - по правому. Чтобы изменить формат отображения данных в текущей ячейке или выбранном диапазоне, используют команду Формат => Ячейки.
Работа с формулами.
Вычисления в таблицах программы Excel осуществляются при помощи формул. Формула может содержать числовые константы, ссылки на ячейки и функции Exсel, соединенные знаками математических операций.
Ссылки на ячейки.
По умолчанию, ссылки на ячейки в формулах рассматриваются как относительные. Это означает, что при копировании формулы адреса в ссылках автоматически изменяются в соответствии с относительным расположением исходной ячейки и создаваемой копии.
При абсолютной адресацииадреса ссылок при копировании не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как не табличная. Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать клавишу F4. Элементы номера ячейки, использующие абсолютную адресацию, предваряются символом $.
Построение диаграмм и графиков.
Построение графического изображения производится на основе ряда данных. Так называют группу ячеек с данными в пределах отдельной строки или столбца. На одной диаграмме можно отображать несколько рядов данных. Диаграмма представляет собой вставной объект, внедренный на один из листов рабочей книги. Она может располагаться на том же листе, на котором находятся данные, ми на любом другом листе (часто для отображения диаграммы отводят отдельный лист). Диаграмма сохраняет связь с данными, на основе которых она построена, и при обновлений этих данных немедленно изменяет свой вид.
Для построения диаграммы обычно используют Мастер диаграмм, запускаемый щелчком на кнопке Мастер диаграмм на стандартной панели инструментов.
Microsoft Excel позволяет добавлять текстовые примечания к ячейкам. После добавления примечания к ячейке в ее верхнем правом углу появляется указатель примечания (красный треугольник). Для добавления текстового примечания необходимо:
¯ выделить ячейку, к которой следует добавить примечание;
¯ вызывать команду Примечание из меню Вставка;
¯ в поле, которое появилось, ввести примечание (размер поля можно изменить, перетягивая маркеры размера);
¯ щелкнуть мышью за пределами поля.
Примечание присоединится к ячейке и будет появляться при наведении на него указателя мыши. Для изменения текста примечания следует выделить соответствующую ячейку и в меню Вставка выбрать пункт Изменить примечание. Также для этого удобно использовать контекстное меню.
Чтобы увидеть одновременно все примечания и работать с ними, можно перейти в режим Примечания через соответствующий пункт в меню Вид. При этом появляется панель Рецензирование
Сортировка данных
Прежде чем начать сортировать данные, необходимо определить для них столбец. Сортировку можно производить одновременно не более, чем по трем полям. Перед началом сортировки выделите только записи, которые необходимо сортировать, но не заголовки столбцов (имена полей).
Чтобы отсортировать записи базы данных, выполните следующее.
1. Выделите область записей для сортировки. Чтобы сортировать весь синеок, щелкните на любой его ячейке.
2. Откройте меню Данные и выберите пункт Сортировка. Появится соответствующее диалоговое окно.
Лабораторное задание
1. Задание выбирается в соответствии с номером варианта. После построения таблицы, необходимо построить диаграмму по полученным данным. Варианты лабораторных заданий:
1.1. Прайс-лист на группу товаров. Включить цену в рублях и долларах, предусмотреть автоматический перевод в рубли в зависимости от текущего курса доллара. Если цена в долларах > 1000, то рассчитать сумму в евро, если цена < 1000, то цену в евро оставить прежнюю.
1.2. Смета затрат на организацию конференции. В смету должны быть включены телефонные переговоры, стоимость переговоров учитывать в зависимости от времени суток (льготный тариф). В таблице должен проставляться временной период и в зависимости от его считаться сумма.
1.3. Расчет квартплаты. Электроэнергию рассчитывать по прогрессивному тарифу (при показаниях счетчика меньше 150 кВт использовать тариф 100 руб. за киловатт, в противном случае применять тариф ‑ 120 руб.).
1.4. Расчет премий. Для сотрудников предприятия, имеющих стаж работы до 10 лет премия составляет 10 % от зарплаты, для сотрудников предприятия со стажем больше 10 лет процент премии равен 20 %.
1.5. Расчет налогов. При годовом доходе до 12 млн. налоговая ставка ‑ 12 %. При годовом доходе свыше 12 млн. налоговая ставка ‑ 20 %.
1.6. Ведомость успеваемости группы. Автоматически подсчитывается средний балл для каждого студента и абсолютная успеваемость по группе. Двойки в подсчёте среднего бала не учитываются.
1.7. Начисление заработной платы. Исходные данные: коэффициент тарифного разряда, минимальная ставка 1-го тарифного разряда и количество отработанного времени. Предусмотреть проверку количества отработанных часов, на максимально возможное за месяц.
1.8. Расчет командировочных расходов подразделения предприятия. Даны: место назначения, стоимость проезда, суточные расходы, даты отъезда и приезда, количество командируемых. Определять сумму командировочных расходов. Произвести проверку, чтобы дата приезда не была < даты отъезда.
1.9. Счет на оплату телефонных переговоров за месяц. Исходные данные: дата, время, длительность разговора. Оплата производиться по переменным тарифам: длительность разговора от 3-х до 5-х минут, длительность разговора от 5 до 10-ти минут, длительность разговора свыше 10-ти минут.
1.10. Непосредственным измерением были по пять раз определены размеры внутреннего (d) и внешнего (D) диаметров полого цилиндра. Результаты измерения даны в таблице. Найти значения абсолютной (Dx=x-xср) и относительной (d=Dx/x) погрешностей для каждого измерения и для эксперимента в целом (Dxср и dср).. Исключить ошибку при неправильном вводе, когда внешний диаметр меньше внутреннего (Таблица 2).
Таблица 2
| № наблюдения (i) | d | D |
| 17,3 | 22,7 | |
| 17,0 | 22,8 | |
| 17,3 | 23,0 | |
| 17,4 | 22,8 | |
| 17,2 | 22,6 |
2. Вставьте в ячейки, в которых используется оператор условия if примечание, предупреждающее об этом.
3. Создать сводную таблицу по данным, используемым в 1 задании, сводную таблицу представить в форме диаграмм и в форме таблицы
4. Скопировать созданную в 1 задании таблицу на другой лист открытой книги Microsoft Excel в 3 экземплярах. Отсортируйте по 1, 2 и 3 столбцам 1, 2 и 3 экземпляр.
5. Элементы управления в Excel. Используя элементы управления «Переключатель» и «Рамка» составить формулу для нахождения суммы всех, положительных или отрицательных значений из диапазона ячеек A10:D11, в зависимости от установки переключателей. Примерный вид решения задачи представлен на рисунке 5.
Для выполнения задания необходимо:
5.1. Вывести панель инструментов ФОРМА;
5.2. Перенести элемент управления «Переключатель»на лист Excel и установить желаемые размеры. Сверху от переключателя должно остаться свободное место. Оно понадобится при объединении переключателей в группу;
Рисунок 5 – Использование переключателей в Excel.
5.3. Ввести название этого переключателя, например “Положительные”.
5.4. Повторить последние два шага для размещения переключателей “Отрицательные” и “Все”
5.5. Выбрать элемент управления “ Рамка ” и перенести его на лист Ехcel таким образом, чтобы он охватывал, ранее построенные переключатели. Ввести название группы – «Суммировать»
5.6. Щелкните правой кнопкой мыши по любому из переключателей и из контекстного меню выберите “ Формат объекта ” и установите связь между переключателями и ячейкой Excel, например A1;
5.7. Ввести формулу вычисляющую требуемую сумму.
5.8. Используемые функции: ECЛИ, СУММЕСЛИ, СУММ.
Выполнить то же задание используя два элемента управления типа флажок (рисунок 6)
Рисунок 6 – Использование переключателей - флагов в Excel.
Содержание отчета
Отчёт по всей работе оформляется с использованием редактора Microsoft Word, Microsoft Excel и сдаётся как в печатном, так и в электронном виде. Студент обязан подтвердить выполнение каждого пункта задания используя функцию screenshot (клавиша prtscr или alt- prtscr).
Лабораторная работа № 4
