Заполнение таблицы исходными данными
Содержание
1. Постановка задачи
2. Заполнение таблицы исходными данными
3. Создание расчетной таблицы
3.1 Перенос исходных данных в расчетную таблицу
3.2 Выполнение расчетов в таблице
3.2.1 Расчет суммарного заработка каждого рабочего
3.2.2 Расчет итоговых данных по каждому цеху
3.2.3 Расчет итоговых данных по всему цеху
3.3 Итоговый вид таблицы
4. Построение диаграмм
4.1 Построение столбиковой диаграммы часов, отработанных сверхурочно и в ночное время
4.2 Построение круговой диаграммы суммарных начислений по каждой бригаде
Используемая литература
1. Постановка задачи
Имеется таблица 1 данных об отработанном времени рабочими цеха.
Таблица 1
| Номер бригады | Фамилия, и., о. рабочего | Разряд | Часовая тарифная ставка, руб | Отработано, ч | ||
| Всего | В том числе сверхурочно | В том числе ночью | ||||
1. Сформировать таблицу 2 «Ведомость начисления заработной платы рабочими цеха»
Таблица 2
| Номер бригады | Фамилия, и., о. рабочего | Разряд | Часовая тарифная ставка | Отработано, ч | Начислено, руб | Сумма всех начисле ний, руб | ||||
| Всего | В том числе сверх урочно | В том числе ночью | Повре менно | Сверх урочно | Ночные | |||||
Выходной документ должен содержать 15 - 20 записей (3 – 5 бригад, в каждой бригаде по 3-5 рабочих). Доплата за час, отработанный сверхурочно, составляет 50% от часовой тарифной ставки. Доплата за час, отработанный в ночное время, составляет 30% от часовой тарифной ставки.
Расчет данных в графах 8, 9, 10, 11 в каждой строке таблицы 2 осуществляется вы соответствии со следующей схемой (в квадратных скобках указаны порядковые номера граф):
[8] = [4] * [5]
[9] = 0,5 * [4] * [6]
[10] = 0,3 * [4] * [7]
[11] = [8] + [9] + [10]
2. Таблица 2 должна содержать итоговые данные по каждой бригаде и общие итоги по цеху в графах 8, 9, 10, 11.
3. Построить столбиковую диаграмму часов, отработанных сверхурочно и в ночное время рабочими одной (любой) бригады.
4. Построить диаграмму суммарных начислений заработной платы по каждой бригаде.
Заполнение таблицы исходными данными
Заполним исходную таблицу данными1, после чего лист Microsoft Excel примет вид:
Создание расчетной таблицы
Перенос исходных данных в расчетную таблицу
Необходимые расчеты будем выполнять на новом листе. Данные для расчета перенесем из исходной таблицы в виде ссылок на соответствующие ячейки.
После этого лист расчетной таблицы с исходными данными примет вид:
Итоговый вид таблицы
После выполнения всех описанных выше действий, расчетная таблица в режиме просмотра результатов примет вид:
Для просмотра этой таблицы в режиме просмотра формул нужно выполнить пункт главного меню Сервис > Зависимости формул > режим проверки формул2. В режиме проверки формул таблица примет вид:
Построение диаграмм
Работа с диаграммами
В Microsoft Excel имеется возможность графического представления данных в виде диаграмм. Диаграммы связаны с данными листа, на основе которых они были созданы, и изменяются каждый раз, когда изменяются данные на листе.
Диаграммы могут использовать данные несмежных ячеек. Диаграмма может также использовать данные сводной таблицы.
Примеры типов диаграмм
Гистограмма показывает изменение данных за определенный период времени и иллюстрирует соотношение отдельных значений данных. Категории располагаются по горизонтали, а значения – по вертикали. Таким образом, уделяется большее внимание изменениям во времени.
Рисунок 6.11. Обычная гистограмма
В трехмерной гистограмме сравнение данных производится по двум осям. Например, показанная на рисунке трехмерная диаграмма позволяет сравнить объемы продаж в Европе за каждый квартал с объемами продаж в двух других регионах.
Рисунок 6.12. Трехмерная гистограмма
Линейчатая диаграмма с накоплением показывает вклад отдельных элементов в общую сумму.
Рисунок 6.13. Линейчатая диаграмма с накоплением
Линейчатая диаграмма отражает соотношение отдельных компонентов. Категории расположены по горизонтали, а значения – по вертикали. Таким образом, уделяется большее внимание сопоставлению значений и меньшее – изменениям во времени.
График отражает тенденции изменения данных за равные промежутки времени.
Рисунок 6.14. График с маркерами
Круговая диаграмма показывает как абсолютную величину каждого элемента ряда данных, так и его вклад в общую сумму. На круговой диаграмме может быть представлен только один ряд данных. Такую диаграмму рекомендуется использовать, когда необходимо подчеркнуть какой-либо значительный элемент.
Рисунок 6.15. Круговая объемная диаграмма
Точечная диаграмма отображает взаимосвязь между числовыми значениями в нескольких рядах и представляет две группы чисел в виде одного ряда точек в координатах xy. Эта диаграмма отображает нечетные интервалы данных и часто используется для представления данных научного характера.
Рисунок 6.16. Точечная диаграмма
Диаграмма с областями подчеркивает величину изменения в течение определенного периода времени, показывая сумму введенных значений. Она также отображает вклад отдельных значений в общую сумму. В данном примере диаграмма с областями показывает увеличение продаж в Бразилии, а также иллюстрирует вклад каждой страны в общий объем продаж.
Рисунок 6.17. Диаграмма с областями
Как и круговая диаграмма, кольцевая диаграмма показывает вклад каждого элемента в общую сумму, но, в отличие от круговой диаграммы, она может содержать несколько рядов данных. Каждое кольцо в кольцевой диаграмме представляет отдельный ряд данных.
Рисунок 6.18. Кольцевая диаграмма
В лепестковой диаграмме каждая категория имеет собственную ось координат, исходящую из начала координат. Линиями соединяются все значения из определенной серии. Лепестковая диаграмма позволяет сравнить общие значения из нескольких наборов данных.
Рисунок 6.19. Лепестковая диаграмма
Поверхностная диаграмма используется для поиска наилучшего сочетания двух наборов данных. Как на топографической карте, области с одним значением выделяются одинаковым узором и цветом.
Рисунок 6.20. Поверхностная диаграмма
Биржевая диаграмма часто используется для демонстрации цен на акции. Этот тип диаграммы также может быть использован для научных данных, например, для определения изменения температуры. Для построения этой и других биржевых диаграмм необходимо правильно организовать данные.
Создание диаграммы
Выберите команду Диаграмма в меню Вставка или нажмите кнопку Мастер диаграмм
на панели инструментов Стандартная и следуйте инструкциям мастера:
Шаг 1. Выберите тип и вид диаграммы. Нажмите кнопку Далее.
Шаг 2. На вкладке Диапазон данных нажмите кнопку
чтобы временно убрать с экрана диалоговое окно, и выделите диапазон ячеек, содержащих данные, которые должны быть отражены на диаграмме. Если необходимо, чтобы в диаграмме были отражены и названия строк или столбцов, выделите также содержащие их ячейки.
На вкладке Ряды можно добавить или удалить любой из рядов данных. В поле Имя указывается ячейка листа, которую следует использовать как легенду или имя ряда. В поле Подписи оси X (категорий) указывается диапазон ячеек, которые нужно использовать как подписи делений оси категорий. Нажмите кнопку Далее.
Шаг 3. Укажите параметры элементов диаграммы. Нажмите кнопку Далее.
Шаг 4. Укажите, где следует поместить диаграмму – на отдельном новом листе или на имеющемся. Нажмите кнопку Готово.
Изменение диаграммы
Диаграмма может содержать следующие элементы:
• Область диаграммы – вся диаграмма, вместе со всеми ее элементами.
• Область построения – в двумерной диаграмме областью построения называется область, ограниченная осями и содержащая все ряды диаграммы. В трехмерной диаграмме это область, ограниченная осями и включающая ряды данных, названия категорий, подписи делений и названия осей.
• Легенда – подпись, определяющая закраску или цвета точек данных или категорий диаграммы.
• Название диаграммы – описательный текст, автоматически связанный с осью или расположенный по центру диаграммы.
• Ряд данных – группа связанных точек данных диаграммы, отображающая значение строк или столбцов листа. Каждый ряд данных отображается по-своему. На диаграмме могут быть отображены один или несколько рядов данных. На круговой диаграмме отображается только один ряд данных.
• Подпись значения – подпись, предоставляющая дополнительные сведения о точке данных, отображающей какое-либо значение ячейки. Подписями данных могут быть снабжены как отдельные точки данных, так и весь ряд целиком. В зависимости от типа диаграммы подписи данных могут отображать значения, названия рядов и категорий, доли или их комбинации.
• Маркер данных – столбик, закрашенная область, точка, сегмент или другой геометрический объект диаграммы, обозначающий точку данных или значение ячейки. Связанные точки на диаграмме образованы рядом данных.
• Ось – линия, часто ограничивающая с одной стороны область построения и используемая как основа измерений для построения данных на диаграмме. В большинстве диаграмм точки данных отображаются по оси (y), которая обычно является вертикальной осью, а категории отображаются по оси (x), как правило, горизонтальной.
• Деления и подписи делений – деления, или короткие вертикальные отрезки, пересекающиеся с осью, подобно делениям на линейке, позволяют отмерить одинаковые расстояния на линейке. Подписи делений обозначают меру длины, отложенную по оси, а также могут обозначать категории, значения или ряды значений диаграммы.
• Линии сетки – линии, которые, будучи добавлены к диаграмме, облегчают просмотр и анализ данных. Линии сетки отображаются параллельно осям от делений диаграммы.
• Таблица данных диаграммы – содержащая отображаемые на диаграмме данные таблица. Каждая строка таблицы данных содержит ряд данных. Таблица данных обычно связана с осью категорий и заменяет подписи оси категорий.
• Линия тренда – графическое представление тренда или направления изменения данных в ряде данных. Линии тренда используются при прогнозировании, например, при регрессионном анализе. Линии тренда могут быть построены на всех двумерных диаграммах без накопления (гистограмме, линейчатой диаграмме, графике, биржевой диаграмме, точечной диаграмме, а также пузырьковых диаграммах).
• Планки погрешностей – графические линии, отображающие потенциальную ошибку (или степень недостоверности) каждой точки данных ряда данных. Планки погрешностей могут отображаться для всех плоских диаграмм (гистограммы, линейчатой диаграммы, точечной диаграммы и пузырьковых диаграмм). На точечных диаграммах могут также отображаться линии погрешности по оси X. Линии погрешности могут быть выделены и форматированы как группа.
• Стенки и основание – плоскости, на фоне которых отображаются многие трехмерные диаграммы. Они придают трехмерным диаграммам впечатление объема и ограничивают область построения диаграммы. Обычно область построения ограничивают две стенки и одно основание.
Для изменения или форматирования элементов диаграммы:
1. Выберите нужный элемент диаграммы. Ряды данных, подписи значений и легенды можно изменять поэлементно. Например, чтобы выбрать отдельный маркер данных в ряде данных, выберите нужный ряд данных, затем – нужный маркер данных.
2. В меню Формат или в контекстном меню выберите команду Формат соответствующего элемента.
3. Измените и установите соответствующие параметры.
Для изменения размеров и перемещения элементов можно использовать мышь.
Чтобы отделить друг от друга все сектора в круговой диаграмме, выделите их и перетащите от центра диаграммы.
Упражнение для самостоятельной работы
Подготовьте таблицу по образцу.
Создание диаграммы
– Выделите таблицу со строкой заголовка.
– В меню Вставка выберите команду Диаграмма. Начнет работать Мастер диаграмм.
– В первом окне Мастера диаграмм выберите тип диаграммы – круговую объемную. Кнопка Просмотр результата позволяет увидеть диаграмму. Нажмите кнопку Далее.
– В следующем окне отображается выделенный диапазон ячеек. Нажмите кнопку Далее.
– На следующем шаге, выбирая вкладки диалогового окна, можно корректировать название диаграммы (оставьте Численность рабочих). На вкладке Легенда снимите флажок Добавить легенду. На вкладке Подписи данных выберите Категория. Нажмите Далее.
– На следующем шаге определите положение диаграммы и выберите кнопку Готово.
– Диаграмма построена. На экране одновременно должны быть видны и таблица, и диаграмма.
– Одиночным щелчком выделите область диаграммы. Поочередно выберите пункты горизонтального меню Вставка и Формат и обратите внимание на изменение команд, а также вид рамки вокруг диаграммы. Вы находитесь в режиме редактирования диаграммы и можете ее изменять.
– Вернитесь в режим работы с электронной таблицей, для чего щелкните мышью вне области диаграммы. Проверьте, что горизонтальное меню вернулось к первоначальному варианту. Вы вновь можете работать со своей электронной таблицей.
Выбор меток
– Щелчком войдите в режим редактирования диаграммы.
– В меню Диаграмма выберите команду Параметры диаграммы.
– На вкладке Подписи данных установите переключатель в положение Название категории и процент. Рядом с каждым сектором на диаграмме, кроме названия округа, появится числовое значение – процент работающих от общего числа работающих по Москве.
Повороты и наклон диаграммы
– Щелкните непосредственно по кругу диаграммы, чтобы появились квадратные метки на каждом секторе.
– В меню Диаграмма выберите команду Объемный вид и поверните диаграмму таким образом, чтобы подписи располагались наиболее оптимально.
– В процессе работы расположите диалоговое окно «Форматирование объемного вида» таким образом, чтобы диаграмма была видна. Пользуйтесь кнопкой Применить диалогового окна для отображения в документе результата поворота. Выбрав окончательный вариант поворота, нажмите кнопку ОК.
– Так же можно выбрать и угол наклона диаграммы.
Изменение цвета
– Выделите только один сектор.
– Дважды щелкните по выделенному сектору или в меню Формат выберите команду Выделенный элемент данных. Появится окно диалога «Форматирование элемента данных».
– На вкладке Вид выберите цвет или даже узор для выделенного сектора. Проследите, чтобы выбранный цвет не использовался для других секторов диаграммы. Можете воспользоваться кнопкой Способы заливки для градиентной заливки или текстуры.
Форматирование меток
– Для подписей данных диаграммы можно выбрать другой шрифт. Щелкните по любой из меток – и выделятся все.
– В меню Формат выберите команду Выделенные подписи данных. Появится окно диалога «Формат подписей данных». Просмотрите, какие возможности предоставляются на каждой из вкладок:
График функции у=х2
– Воспользовавшись Мастером функций, составьте таблицу значений функции у=х2 для значений аргумента от 0 до 4 с шагом 0,5. Для заполнения ряда абсцисс примените маркер заполнения. Подберите ширину столбцов.
– Введите формулу и вычислите значения функции.
– Выделите только значения функции (у), в противном случае у вас получатся два графика (по данным первой строки – график линейной функции, по данным второй строки – график квадратичной функции).
– Запустите Мастер диаграмм и выберите тип диаграммы – график.
– Постройте график, следуя указаниям Мастера диаграмм.
– Щелчком вне области диаграммы перейдите в режим таблицы. К исходной таблице добавьте новый ряд данных для значений функции у=х3.
– Сравните построенную диаграмму с образцом.
Упражнение
На предприятии работники имеют следующие оклады: начальник отдела – 1000 руб., инженер 1кат. – 860 руб., инженер – 687 руб., техник – 315 руб., лаборант – 224 руб. Предприятие имеет два филиала: в средней полосе и в условиях крайнего севера. Все работники получают надбавку 10 % от оклада за вредный характер работы, 25 % – от оклада ежемесячной премии. Со всех работников удерживают 20 % подоходный налог, 3 % профсоюзный взнос и 1 % в пенсионный фонд. Работники филиала, расположенного в средней полосе, получают 15 % районного коэффициента, работники филиала, расположенного в районе крайнего севера, имеют 70 % районный коэффициент и 50 % северной надбавки от начислений.
Расчет заработной платы должен быть произведен для каждого филиала в отдельности. Результатом должны быть две таблицы.
Требуется:
а) при помощи электронной таблицы рассчитать суммы к получению каждой категории работников;
б) построить две диаграммы, отражающие отношение районного коэффициента (районной и северной надбавки) и зарплаты для всех сотрудников обоих филиалов.
Просмотр таблиц Excel
Установки для просмотра
Существует несколько опций для предварительных установок просмотра таблиц в Excel, которые доступны при выборе соответствующих закладок в окне диалога командыПараметрыиз меню Сервис.
Для установки правил отображения элементов окна Excel выберите закладку Види установите требуемые параметры (рис. 1).
Для проверки правильности вычислений в таблице следует включить опцию Формулыв поле Параметры окна, при этом в ячейках, содержащих формулы, можно их увидеть.
Для таблиц, содержащих рисунки и текстовые блоки, можно отключить сетку.
Для остальных параметров можно рекомендовать те опции, которые установлены на представленном экране.
Рис. 1. Закладка Вид команды Параметры
Фиксация панелей
Строки и столбцы, содержащие заголовки, могут быть зафиксированы на месте, подобно именам строк и столбцов. Заголовки остаются на месте, в то время как пользователь может просматривать содержимое рабочего листа. Фиксация заголовков особенно эффективна для улучшения читаемости больших таблиц, строки или столбцы которых находятся за пределами видимого экрана.
Позиция активной ячейки будет определять, какие строки и/или столбцы станут заголовками в результате прокрутки окна:
- выделение строки фиксирует верхнюю строку в качестве заголовка;
- выделение столбца фиксирует левый столбец в качестве заголовка;
- выделение ячейки фиксирует верхнюю строку и левый столбец в качестве заголовка.
1. Выделите подходящую ячейку, строку или столбец.
2. Выберите Окно, Закрепить области.
Переместите бегунок и просмотрите таблицу при зафиксированных строках заголовка.
Если заголовок уже зафиксирован, команда Закрепить областиизменится на командуСнять закрепяение областей. Используйте эту команду для того, чтобы отменить фиксацию областей.
Разделение окон
Окно текущего рабочего листа может быть разделено на две или четыре части. Каждая панель показывает один и тот же рабочий лист, но любая из панелей может использоваться независимо для просмотра различных частей рабочего листа. Эта возможность полезна при создании формул для выбора ячеек, находящихся далеко друг от друга.
Позиция активной ячейки, строки или столбца определяет тип разделения (при выделении строки - вдоль листа, столбца - поперек, ячейки - на четыре части).
1. Выделите подходящую ячейку, строку или столбец.
2. Выберите Окно, Разделить.
Рабочий лист разделится, и появятся полосы разделения поперек или вдоль рабочего листа. Например, верхнее левое окно может использоваться для просмотра данных, в то время как нижнее правое для просмотра результатов вычислений.
Как только окно будет разделено, команда Разделитьизменится на команду Снять разделение. Для удаления разделения окна нужно выбрать эту команду.
Содержание
1. Постановка задачи
2. Заполнение таблицы исходными данными
3. Создание расчетной таблицы
3.1 Перенос исходных данных в расчетную таблицу
3.2 Выполнение расчетов в таблице
3.2.1 Расчет суммарного заработка каждого рабочего
3.2.2 Расчет итоговых данных по каждому цеху
3.2.3 Расчет итоговых данных по всему цеху
3.3 Итоговый вид таблицы
4. Построение диаграмм
4.1 Построение столбиковой диаграммы часов, отработанных сверхурочно и в ночное время
4.2 Построение круговой диаграммы суммарных начислений по каждой бригаде
Используемая литература
1. Постановка задачи
Имеется таблица 1 данных об отработанном времени рабочими цеха.
Таблица 1
| Номер бригады | Фамилия, и., о. рабочего | Разряд | Часовая тарифная ставка, руб | Отработано, ч | ||
| Всего | В том числе сверхурочно | В том числе ночью | ||||
1. Сформировать таблицу 2 «Ведомость начисления заработной платы рабочими цеха»
Таблица 2
| Номер бригады | Фамилия, и., о. рабочего | Разряд | Часовая тарифная ставка | Отработано, ч | Начислено, руб | Сумма всех начисле ний, руб | ||||
| Всего | В том числе сверх урочно | В том числе ночью | Повре менно | Сверх урочно | Ночные | |||||
Выходной документ должен содержать 15 - 20 записей (3 – 5 бригад, в каждой бригаде по 3-5 рабочих). Доплата за час, отработанный сверхурочно, составляет 50% от часовой тарифной ставки. Доплата за час, отработанный в ночное время, составляет 30% от часовой тарифной ставки.
Расчет данных в графах 8, 9, 10, 11 в каждой строке таблицы 2 осуществляется вы соответствии со следующей схемой (в квадратных скобках указаны порядковые номера граф):
[8] = [4] * [5]
[9] = 0,5 * [4] * [6]
[10] = 0,3 * [4] * [7]
[11] = [8] + [9] + [10]
2. Таблица 2 должна содержать итоговые данные по каждой бригаде и общие итоги по цеху в графах 8, 9, 10, 11.
3. Построить столбиковую диаграмму часов, отработанных сверхурочно и в ночное время рабочими одной (любой) бригады.
4. Построить диаграмму суммарных начислений заработной платы по каждой бригаде.
Заполнение таблицы исходными данными
Заполним исходную таблицу данными1, после чего лист Microsoft Excel примет вид:
Создание расчетной таблицы