Объединение листов в Excel
Для выполнения общего форматирования ячеек таблицы Excel, ввода заголовков и однотипных данных используется группировка или объединение листов. Для объединения смежных листов:
1. Выделите ярлык первого листа.
2. Нажмите Shift и щелкните ярлык последнего листа.
Ярлыки объединенных листов Excel станут выделенными. Для объединения несмежных листов:
1. Выделите ярлык первого листа.
2. Нажмите Ctrl и щелкайте ярлыки тех листов, которые должны быть включены.
Ярлыки объединенных листов станут выделенными.
Для отмены объединения щелкните правой кнопкой ярлык любого листа из объединенных и в контекстном меню выберите Разгруппировать листы или щелкните ярлык любого листа Excel, не принадлежащего к группе.
В Microsoft Excel больше нет мастера диаграмм. Чтобы создать базовую диаграмму, теперь нужно выбрать нужный диапазон и щелкнуть тип диаграммы на вкладке Вставка в группе Диаграммы.
Элементы диаграммы
Диаграмма состоит из различных элементов. Некоторые из них отображаются по умолчанию, другие можно добавлять по мере необходимости. Можно изменить вид элементов диаграммы, переместив их в другое место или изменив их размер либо формат. Также можно удалить элементы диаграммы, которые не требуется отображать.
1. область диаграммы.
2. область построения диаграммы.
3. точки данных для ряд данных, отмеченные на диаграмме.
4. ось категорий (горизонтальная) и значений (вертикальная), вдоль которых строится диаграмма.
5. легенда диаграммы.
6. Названия диаграммы и осей, которые можно использовать в диаграмме.
7. подпись данных, с помощью которых можно обозначать сведения точки данных в ряду данных.
Изменение базовой диаграммы в соответствии с потребностями
Создав диаграмму, можно изменить любой из ее элементов. Например, можно изменить вид осей, добавить название диаграммы, переместить или скрыть легенду, а также добавить дополнительные элементы.
Чтобы изменить диаграмму, выполните одно или несколько из указанных ниже действий.
Изменение вида осей диаграммы. Можно указать масштаб осей и изменить промежутки между значениями или категориями. Для удобства чтения диаграммы вы можете добавить на оси деления и указать величину промежутков между ними.
Добавление к диаграмме названий и меток данных Для пояснения отображенных на диаграмме данных можно добавить название диаграммы, названия осей и метки данных.
Добавление легенды и таблицы данных Можно отобразить или скрыть легенду, изменить ее расположение или элементы. Для некоторых диаграмм также можно отобразить таблицу данных, в которой содержатся ключи легенды и значения, представленные на диаграмме.
Применение специальных параметров для диаграмм разных типов. Для различных типов диаграмм можно применять различные специальные линии (например, коридор колебания и линии тренда), полосы (например, полосы повышения и понижения и пределы погрешностей), маркеры данных.
Практическая часть
Задание 1. Откройте программу MS Excel из пакета MS Office. Опишите последовательность команд, которые необходимо выполнить, чтобы добавить лист в книгу
Задание 2. Опишите последовательность действий для объединения смежных листов книги Excel для выполнения их общего форматирования
Задание 3. Создайте книгу MS Excel, содержащую шесть листов. Создайте для них общее оформление.
1. Первый лист назовите Рыба жаренная,
второй лист Рыба, запеченная с картофелем,
третий лист Рыба, жаренная во фритюре,
четвертый лист Эскалоп из свинины,
пятый лист Котлетная масса.
2. Объедините листы (кликните по ярлыку первого листа, нажмите клавишу SHIFT и удерживая ее, кликните по ярлыку шестого листа) и выполните следующие действия, которые позволят оформить заголовок шести листов сразу.
3. Установите следующие параметры для ширины столбцов А – 3, В – 45, С, D, E 16
4. Выделите на первом листе ячейки А1-Е1, объедините их, введите текст
5. Выделите на первом листе ячейки А3-Е3, объедините их, введите текст
6. Выделите блок ячеек А3: Е15, оформите внешние и внутренние границы
7. Введите следующий текст в указанные ячейки:
А4 №
А5:А8 – 1, 2, 3, 4 соответственно
В4 – Наименование продуктов
С4 – Цена,
D4 Количество
Е4 – Сумма
В12 – Выход (порций)
В13 – Общая стоимость
В14 Продажная цена блюда без НДС
В15 Продажная цена блюда с НДС
8. Введите в ячейку Е5 (не снимая группировку листов книги) формулу =С5*D5
9. Скопируйте эту формулу до ячейки Е11
10. Не снимая группировку листов книги, введите в ячейку Е13 формулу для подсчета общей стоимости =СУММ(E5:E11)
11. Не снимая группировку листов книги, введите в ячейку Е13 формулу для подсчета продажной цены блюда без НДС =E13/D12
12. Не снимая группировку листов книги, введите в ячейку Е14 формулу для подсчета продажной цены блюда с НДС =E14+E14*0,18
13. Не снимая группировку листов книги, выделите ячейки А12-Е12 желтым цветом
14. Не снимая группировку листов книги, выделите ячейки А13-Е15 голубым цветом
Задание 4. Заполните на каждом листе таблицу (аналог калькуляционной карточки) для конкретного блюда, используя созданный шаблон.
Кликните на первом листе – так вы снимите группировку. Введите оставшуюся часть таблиц для каждого блюда на отдельном листе. Расчет производится автоматически
Лист 1.
№1 Рыба жаренная
Наименование блюда - рыба жаренная
|
№
|
наименование продуктов
|
цена
|
кол-во
|
сумма
|
1
|
рыба свежая (минтай)
|
85.00
|
1,57
|
?
|
2
|
масло растительное
|
50.00
|
0,06
|
?
|
3
|
мука
|
25.00
|
0,06
|
?
|
4
|
гарнир - картофельное пюре
|
|
|
|
|
картофель
|
10.00
|
2,70
|
?
|
|
масло сливочное
|
120.00
|
0,07
|
?
|
|
молоко
|
30.00
|
0,03
|
?
|
|
|
|
|
|
|
Выход (порций)
|
|
10
|
|
|
общая стоимость
|
|
|
?
|
|
продажная цена блюда без НДС
|
|
|
?
|
|
продажная цена блюда с НДС
|
|
|
?
|
Лист 2.
№2 Рыба, запеченная с картофелем.
Наименование блюда - рыба,запеченная с картофелем
|
№
|
наименование продуктов
|
цена
|
кол-во
|
сумма
|
1
|
рыба свежая (хек)
|
100,00
|
2,54
|
?
|
2
|
картофель
|
10,00
|
2,06
|
?
|
3
|
сыр Российский
|
210,00
|
0,04
|
?
|
4
|
сухари
|
30,00
|
0,05
|
?
|
5
|
масло сливочное
|
120,00
|
0,11
|
?
|
|
|
|
|
|
|
|
|
|
|
|
Выход (порций)
|
|
10
|
|
|
общая стоимость
|
|
|
?
|
|
продажная цена блюда без НДС
|
|
|
?
|
|
продажная цена блюда с НДС
|
|
|
?
|
Лист 3.
№3 Рыба, жаренная во фритюре.
Наименование блюда - рыба, жаренная во фритюре
|
№
|
наименование продуктов
|
цена
|
кол-во
|
сумма
|
1
|
рыба свежая (горбуша)
|
95,00
|
2,33
|
?
|
2
|
мука пшеничная
|
25,00
|
0,06
|
?
|
3
|
яйцо
|
32,00
|
20,00
|
?
|
4
|
сухари панировочные
|
30,00
|
0,01
|
?
|
5
|
жир кулинарный
|
80,00
|
0,20
|
?
|
|
|
|
|
|
|
|
|
|
|
|
Выход (порций)
|
|
10
|
|
|
общая стоимость
|
|
|
?
|
|
продажная цена блюда без НДС
|
|
|
?
|
|
продажная цена блюда с НДС
|
|
|
?
|
Лист 4.
№4 Эскалоп из свинины
Наименование блюда - эскалоп из свинины
|
№
|
наименование продуктов
|
цена
|
кол-во(кг)
|
сумма
|
1
|
свинина
|
210,00
|
1,47
|
?
|
2
|
жир кулинарный
|
80,00
|
0,07
|
?
|
3
|
гренки
|
14,00
|
0,02
|
?
|
4
|
гарнир -морковь припущенная
|
|
|
|
|
морковь
|
25,00
|
2,71
|
?
|
|
маргарин
|
80,00
|
0,05
|
?
|
|
масло сливочное
|
120
|
0,01
|
?
|
|
салат листовой
|
30
|
0,003
|
?
|
|
|
|
|
|
|
|
|
|
|
|
Выход (порций)
|
|
10
|
|
|
общая стоимость
|
|
|
?
|
|
продажная цена блюда без НДС
|
|
|
?
|
|
продажная цена блюда с НДС
|
|
|
?
|
Лист 5.
№5 Котлетная масса
Наименование блюда - котлетная масса
|
№
|
наименование продуктов
|
цена
|
кол-во(кг)
|
сумма
|
1
|
мясо свинина
|
180,00
|
0,70
|
?
|
2
|
мясо говядина
|
170,00
|
0,30
|
?
|
3
|
хлеб пшеничный
|
14,00
|
0,25
|
?
|
4
|
молоко
|
30,00
|
0,30
|
?
|
5
|
соль
|
10,00
|
0,02
|
?
|
|
|
|
|
|
|
|
|
|
|
|
выход
|
|
1,5
|
|
|
общая стоимость
|
|
|
?
|
|
продажная цена блюда без НДС
|
|
|
?
|
|
продажная цена блюда с НДС
|
|
|
?
|
Задание 5. На шестой лист добавьте диаграмму для сравнения цены на блюда, используя данные из калькуляционных карточек.
1. На шестом листе оформите таблицу следующего вида:
2. Выделите ячейку А2, введите знак =, кликните по первому листу, выделите ячейку с название блюда на первом листе (ячейка А3) , нажмите Ввод
3. Аналогично добавьте названия других четырех блюд
4. Выделите ячейку В2, введите знак =, кликните по первому листу, выделите ячейку с ценой на первом листе (ячейка Е15), нажмите Ввод
5. Выделите ячейки А2-В6, выберите команду Вставка/Диаграммы/Круговая
6. Измените диаграмму, добавьте подписи
Контрольные вопросы
1. Каково назначение группировки листов MS Excel?
2. Как выполнить группировку листов книги MS Excel?
3. Как изменить формат границы ячейки?
Лабораторная работа № 14
Создание базы данных. Ввод и форматирование данных
Цель работы: сформировать умения и навыки работы с системой управления базами данных (СУБД)
Ход выполнения работы
1. Изучите теоретическую часть
2. Выполните практическое задание
3. Оформите отчет о выполнении лабораторной работы
4. Ответьте на контрольные вопросы
Теоретическая часть
ACCESS – это реляционная СУБД. Это означает, что с ее помощью можно работать одновременно с несколькими таблицами базы данных, эти таблицы между собой связаны.
База данных состоит из следующих компонентов:
Таблицы – основные объекты базы данных (БД). В них хранятся данные. Реляционная база данных может иметь много взаимосвязанных таблиц. В БД столбцы называются полями, а строки – записями.
Запрос – это средство, с помощью которого извлекается из базы данных информация, отвечающая определенным критериям. Результаты запроса представляют не все записи из таблицы, а только те, которые удовлетворяют запросу.
Формы – Обеспечивают более наглядную работу с таблицами, с помощью форм в базу вводят новые данные или просматривают имеющиеся.
Отчеты – средство представления данных таблиц. Отчеты могут быть оформлены надлежащим образом и распечатаны в том виде, в котором требуется пользователю.
Макросы – набор из одной или более макрокоманд, выполняющих определенные операции (открытие форм, печать отчетов)
Модули это программы, написанные на языке программирования Visual Basic.
С таблицами можно работать в двух режимах таблицы и конструктора.
В режиме таблицы можно начать ввод данных сразу, структура таблицы при этом будет создаваться автоматически.
На вкладке Создание в группе Таблицы щелкните Таблица. 
Приложение Access создаст базу данных с пустой таблицей с именем «Таблица1» и откроет эту таблицу в режиме таблицы. При этом будет выделена первая пустая ячейка столбца Добавить поле.
Создание таблицы в режиме конструктора
На вкладке Создание в группе Таблицы щелкните Конструктор таблиц. 
Для каждого поля в таблице введите имя в столбце Имя поля, а затем в списке Тип данных выберите тип данных.
Когда все необходимые поля будут добавлены, сохраните таблицу:
4 Щелкните значок Кнопка Microsoft Office  , а затем выберите команду Сохранить.
Создание таблицы на основе шаблона
На вкладке Создание в группе Таблицы щелкните Шаблоны таблицы и затем выберите из списка один из доступных шаблонов.
Свойства полей. Типы полей
Поля – это основные элементы структуры базы данных, которые обладают свойствами. Основным свойством любого поля является его длина, которая выражается в символах. Уникальным свойством любого поля является его Имя.
Разные типы полей имеют разное назначение и разные свойства
Типы данных
|
Описание
|
Текстовый
|
Произвольный набор (не более 255) букв, цифр, знаков пунктуации. Может использоваться для хранения имен, адресов, телефонов, кратких описаний.
|
Числовой
|
Используется для хранения чисел
|
Дата/Время
|
Для хранения информации о дате и времени до 9999 года включительно
|
Денежный
|
Денежные значения и числовые данные, используемые в математических расчетах
|
Поле МЕМО
|
Для хранения комментариев до (64Кб)
|
Счетчик
|
Специальное числовое поле, в котором Access автоматически присваивает уникальный порядковый номер каждой записи. Значение полей типа счетчика обновлять нельзя
|
Поле объекта OLE
|
Позволяет поместить в таблицу произвольный объект из другой программы
|
Гиперссылка
|
Адрес в Интернете, электронный адрес или адрес файла
|
Мастер подстановок
|
Создает поле, в котором предлагается выбор значений из списка или из поля со списком, содержащего набор постоянных значений или значений из другой таблицы
|
Логический
|
Содержит одно из двух значений: Истина или ЛОЖЬ
|
Правильная структура базы данных
В основе процесса создания базы данных лежат определенные принципы. Первый принцип заключается в необходимости исключать повторяющиеся (или лишние) данные.
Второй принцип касается важной роли правильных и полных данных. Если база данных содержит неправильные данные, все отчеты, в которых данные объединяются, будут также содержать неверные сведения. Это может привести к принятию неправильных решений на основе отчетов.
Процесс разработки
Процесс разработки базы данных включает следующие шаги: Определение цели создания базы данных, поиск и организация необходимых данных, распределение данных по таблицам, преобразование элементов данных в столбцы
Задание первичных ключей.
Каждая таблица должна содержать столбец или набор столбцов для однозначного определения каждой строки таблицы. Как правило, в этих целях используется уникальный идентификационный номер, например код сотрудника или серийный номер. В базе данных такие сведения носят название первичного ключа таблицы.
Создание связей между таблицами
Проанализируйте все таблицы и определите, как данные одной таблицы связаны с данными других таблиц. Добавьте в таблицы поля или создайте новые таблицы для создания необходимых связей.
Практическая часть
Задание 1. Откройте программу MS Access из пакета MS Office. Запишите, какие шаблоны вам доступны.
Задание 2. Вызовите команду Файл/Создать/Доступные шаблоны/Образцы шаблонов. Перечислите доступные вам образцы шаблонов
Задание 3. Создайте файл базы данных Анкета32ТПОП.accdb.
1. Откройте программу Access.
2. Вызовите команду Файл/Создать/Доступные шаблоны/Новая база данных.
3. Введите имя Анкета 32ТПОП.
4. Появится окно <Таблица> (Рисунок 1).
Рисунок 1
5. В появившемся окне откройте меню команды <Режим> и выберите вариант <Конструктор>  и сохраните будущую таблицу под названием <ВедомостьУспеваемости>. Появится окно Конструктора.
6. Заполните поля в Конструкторе данными из таблицы 1. Тип данных можно выбрать из меню, появившемся при нажатии на кнопку  в ячейке справа.
О братите внимание: ключевое поле «Счетчик» внесен в таблицу автоматически. Если напротив поля отсутствует значок ключа, то на панели инструментов щелкните по этому значку
Таблица 1.
7. Перейдите в режим таблицы, щелкнув по кнопке Режим на панели инструментов. Введите данные в этом режиме, заполняя клетки таблицы. Значение поля Код будет меняться автоматически.
8. Заполните базу данных значениями из таблицы 2. Напротив каждой фамилии выставьте по всем дисциплинам оценки от 2 до 5
Таблица 2
Код
|
Фамилия
|
Имя
|
Математика
|
Менеджмент
|
Сервисная деятельность
|
Информационные технологии
|
Стандартизация
|
Гостиничная индустрия
|
Пропуски по неуважительной причине
|
Пропуски по уважительной причине
|
1
|
Бочкарева
|
Ульяна
|
|
|
|
|
|
|
|
|
2
|
Внукова
|
Диана
|
|
|
|
|
|
|
|
|
3
|
Дегтева
|
Анастасия
|
|
|
|
|
|
|
|
|
4
|
Должикова
|
Марина
|
|
|
|
|
|
|
|
|
5
|
Зиновьева
|
Светлана
|
|
|
|
|
|
|
|
|
6
|
Зюзюкин
|
Александр
|
|
|
|
|
|
|
|
|
7
|
Иконникова
|
Ирина
|
|
|
|
|
|
|
|
|
8
|
Ильин
|
Никита
|
|
|
|
|
|
|
|
|
9
|
Карташева
|
Алена
|
|
|
|
|
|
|
|
|
10
|
Кизилова
|
Александра
|
|
|
|
|
|
|
|
|
11
|
Масалитина
|
Евгения
|
|
|
|
|
|
|
|
|
12
|
Романченко
|
Диана
|
|
|
|
|
|
|
|
|
13
|
Степанов
|
Алексей
|
|
|
|
|
|
|
|
|
14
|
Цимбалов
|
Роман
|
|
|
|
|
|
|
|
|
15
|
Чижденко
|
Елена
|
|
|
|
|
|
|
|
|
16
|
Шмараева
|
Анастасия
|
|
|
|
|
|
|
|
|
17
|
Ярожко
|
Иван
|
|
|
|
|
|
|
|
|
9. Выполните редактирование ячеек:
Замените фамилию Ярожко на Ярыжко.
10. Отсортируйте:
а) фамилии – по алфавиту (поставьте маркер на любую фамилию в столбце Фамилия и щелкнете мышкой по кнопке  на панели инструментов или произведите сортировку с помощью контекстного меню)
б) имя – по алфавиту
11. Сохраните текущую таблицу, щелкнув по кнопке «крестик» в правом верхнем углу окна таблицы.
Задание 4. Выполните поиск в базе данных Анкета32ТПОП.accdb.
1. Выполните поиск записей по образцу: найти студентку по фамилии Внукова. Для этого установите курсор в поле фамилия, щелкните на кнопке  <Бинокль> на панели инструментов меню Главная и в появившемся диалоговом окне введите в поле <Образец> фамилию Внукова и щелкните по кнопке <Найти>.
Примечание: Если требуется найти следующую подобную запись, то щелкните мышкой по кнопке <Найти далее>. По окончании работы щелкните по кнопке <Отмена>.
2. Скройте столбец Пропуски по неуважительной причине, потом отобразите его назад.
3. Войдите в режим Конструктора и назначьте полю Пропуски по неуважительной причине и Пропуски по уважительной причине. Маску ввода
Для этого необходимо перейти к свойству поля, найти строку Маска ввода и ввести 00 «часов».
Заполните эти поля данными от 0 до 99.
Задание 5. Добавьте таблицу Преподаватели в файл базы данных Анкета32ТПОП.accdb.
1. Создайте таблицу <Преподаватели > в Режиме таблицы. Для этого в меню Создание выберите кнопку Таблица. В появившейся таблице сделайте следующее:
Добавьте два поля – Поле 1 и Поле 2, выполнив команду через контекстное меню (правая кнопка мыши).
Переименуйте <Поле 1> на <Предмет>. Для этого поставьте курсор в любую ячейку столбца <Поля 1> и выполните команду Переименовать столбец из контекстного меню. Или щелкните два раза по имени поля, удалите старое название и впечатайте новое.
Переименуйте аналогично <Поле 2> на <Преподаватель>.
2. Сохраните таблицу с именем <Преподаватели>, щелкнув по кнопке <Сохранить>.
3 . Перейдите в режим <Конструктор> и удалите строку с ключевым словом Счетчик. Посмотрите, как заданы поля. Сделайте поле <Предмет> ключевым, поместив курсор на имя этого поля и щелкнув по кнопке - Ключевое поле. Тип данных поля задайте текстовым.
4. Перейдите в Режим таблицы и заполните таблицу <Преподаватели> записями из Таблицы3.
|
