Амелина Н. - Excel ЗАДАЧИ И УПРАЖНЕНИЯ
Ростовский ордена Трудового Красного Знамени государственный университет
АННОТАЦИЯ
Методические указания содержат описание технологии работы со списками - базами данных в Excel и лабораторные работы, позволяющие практически освоить эту технику при организации простейшей базы данных и реализации поиска в ней необходимой информации.
Методические указания предназначены для студентов экономического и механико-математического факультетов РГУ.
РАБОТА С ТАБЛИЦЕЙ КАК С БАЗОЙ ДАННЫХ
Базы данных являются ядром компьютерных систем обработки информации (информационных систем). Собственно база данных - это система файлов, хранящих всю необходимую информацию, и она может быть организована по различным правилам. Одним из наиболее наглядных и распространенных принципов организации является табличная организация.
В этом случае база данных реализуется как набор таблиц, хранящихся в отдельных файлах. Каждая из таблиц имеет фиксированную структуру и может состоять из произвольного числа строк.
Структура таблицы определяет количество столбцов в ней, их заголовки (имена) и вид информации, представимой в каждом столбце (числа, символы, тексты, даты и т.п.).
Для создания баз данных, работы с ними и разработки информационных систем используется специальное программное обеспечение - системы управления базами данных (СУБД). В электронные таблицы Excel включен набор средств, позволяющий организовать простейшую базу данных и осуществлять автоматический поиск в ней необходимой информации.
В Excel каждая таблица базы данных называется списком и должна находиться на отдельном рабочем листе.
Введем ряд определений.
Список - последовательность строк рабочего листа, содержащих в одинаковых столбцах данные одного типа.
Список состоит из записей (строк). Каждая строка является элементом списка.
Поля (столбцы) списка содержат компоненты (атрибуты) записи.
Поля списка имеют имена (метки или заголовки), расположенные в верхней части списка (в первой строке).
ТЕХНОЛОГИЯ РАБОТЫ СО СПИСКАМИ
Итак, список в Excel - это специальным образом организованная таблица, первая (заглавная) строка которой содержит заголовки столбцов (имена полей), а все остальные строки - однотипные записи.
Для создания списка необходимо определить поля списка и ввести данные в таблицу:
1. В заглавной строке таблицы (первой или второй строке рабочего листа) введите имена полей.
2. В следующей строке определите, если нужно, выражения для вычисляемых полей (введите в соответствующие ячейки формулы для необходимых вычислений). В этой строке можно предварительно выполнить форматирование ячеек, чтобы обеспечить необходимый вид отображения информации в таблице.
Как формулы, так и форматы будут автоматически копироваться при заполнении таблицы в новую строку.
3. Заполнение списка информацией осуществляется командой Данные/ Форма... главного меню Excel (см. раздел 1.1).
Замечание. Если список расположен со второй строки, то можно в первой строке рабочего листа указать название списка.
Для этого введите в ячейку A1 или B1 соответствующий текст.
Формыдля ввода и редактирования данных
Форма - способ отображения данных, когда на экране располагаются поля только одной записи-строки.
Для работы с формой нужно выбрать пункт меню Данные/Форма..., предварительно выделив любую ячейку списка. Открывшееся диалоговое окно будет иметь то же название, что и рабочий лист.
В окне выводятся заголовки столбцов таблицы в качестве названий полей и окна для ввода данных, курсор стоит в первом окне ввода. Для перехода в следующее окно ввода используется клавиша ТаЬ, для возврата в предыдущее - одновременно нажатые клавиши Shift и Tab.
Перемещаться по всем элементам формы можно щелчком левой кнопки мыши.
Доступ к вычисляемым полям блокирован. Значения этих полей автоматически заполняются на основе информации, содержащейся в таблице.
Кроме полей, форма содержит индикатор номера записи и ряд кнопок справа от полей ввода.
Кнопка Добавить вводит новую строку в конец списка.
Кнопка Удалить удаляет текущую запись.
Кнопка Вернуть(Восстановить) позволяет восстановить исходное содержание записи, отменяя результаты редактирования, если только Вы не перешли к следующей записи или не нажали клавишу Enter.
Для "пролистывания" списка предназначены кнопки Назад (Предыдущая запись) и Далее (Следующая запись), можно также использовать полосу прокрутки.
Кнопка Закрыть закрывает форму и возвращает нас в режим работы с таблицей.
Нажатие кнопки Критерии обеспечивает переход в режим поиска данных по критериям. В качестве критериев используются как значения полей, так и условия на значения полей.
Для текстовых полей может быть задан поиск значений по частичному совпадению. Например, при поиске всех фамилий, начинающихся с буквы П, в соответствующем поле указывается одна буква.
Если нужно отыскать записи о сотрудниках, поступивших на работу после 01.06.96, то в поле Дата поступления следует указать условие поиска 01.06.96. Если необходимо найти записи о сотрудниках 1 отдела, поступивших на работу после 01.06.96, то в поле Отдел указывается номер 1, а в поле Дата поступления - условие 01.06.96.
После нажатия клавиши Enter или кнопки Правка с помощью кнопок Назад и Далее можно просмотреть все записи в таблице, удовлетворяющие заданному набору критериев - совокупности критериев поиска.
Сортировка списка
Сортировка - упорядочение данных по возрастанию или убыванию.
Сортировка записей (строк списка) реализуется в Excel в соответствии с содержимым конкретных столбцов командойДанные / Сортировка...
При сортировке по возрастанию текстовые данные упорядочиваются в алфавитном порядке, числа располагаются по возрастанию значений от минимального к максимальному, даты - в хронологическом порядке. Сортировка по убыванию происходит в обратном порядке.
Пустые ячейки всегда располагаются в конце списка.
В Excel можно реализовать многоуровневую сортировку. В трех строках ввода диалогового окна Сортировка диапазона последовательно задаются ключи сортировки - имена полей (столбцов), по которым сортируются данные.
Порядок сортировки для каждого поля устанавливается переключателями по возрастанию или по убыванию.
На каждом из следующих уровней сортировка применяется к группам записей (строк), имеющих одинаковые значения ключа для предыдущего уровня сортировки. Например, если первый уровень задает сортировку по номеру отдела, а второй - по фамилии, то сначала записи будут отсортированы по номеру отдела, а внутри каждого отдела - по фамилии сотрудника.
Если на очередном уровне все записи имеют различные значения ключа сортировки, то организация следующих уровней сортировки становится бессмысленной.
Сортировку по одному полю можно реализовать с помощью кнопок
Замечание. При сортировке меняется порядок следования записей. Ес
ли возникает необходимость часто возвращаться к первоначальной последовательности строк списка, то следует добавить в таблицу еще один столбец с порядковым номером записи и выполнять сортировку по этому полю.
Фильтрация данных в списке
Фильтрация позволяет находить и отбирать для обработки часть записей (строк), которые содержат определенное значение или отвечают определенным критериям. Остальные строки при этом скрыты.
В Excel для отбора данных списка (реализации запроса к базе данных) используются Автофильтр и Расширенный фильтр.
Расширенный фильтр применяется, если в отборе участвуют более двух критериев (условий поиска), или если при сравнении используются результаты вычислений.
Для установки Автофильтра следует, находясь в пределах списка, выполнить командуДанные / Фильтр / Автофильтр
Метки (заголовки) столбцов преобразуются в раскрывающиеся списки (кнопки - стрелки), в которых можно задавать нужные критерии для поиска данных.
В раскрывающемся списке выводятся все значения, встречающиеся в столбце, и дополнительные опции: Все, Первые 10, Условие, Пустые или Непустые.
При выборе конкретного значения или опций Все, Пустые (Непустые) на экран выводятся соответствующие записи списка.
Опция Первые 10 предоставляет возможность выбрать необходимое число (по умолчанию 10) записей, содержащих наибольшие или наименьшие значения в поле фильтра. Предварительно список должен быть отсортирован по этому полю.
Опция Условие позволяет в диалоговом окне Пользовательский автофильтр указать для одного столбца один или два критерия отбора, объединив их (И - для случая, когда оба условия должны выполняться одновременно, ИЛИ - для случая, когда достаточно выполнение хотя бы одного из условий). Например, для поиска сотрудников, родившихся до 49 года, используется условие для поля Дата рождения меньше 01.01.49
А если нас интересуют сотрудники, родившиеся с 49 по 78 год, условие будет таким:
больше или равно 01.01.49 И
меньше 01.01.79
В условиях поиска для текстовых полей можно задавать символы шаблона:
* для указания любой последовательности символов,
? для представления любого одного символа.
Например, для отбора номеров телефонов АТС-66 следует задавать 66-*
Для выбора поиска номера телефона, если Вы сомневаетесь в одной из
цифр
6?-26-76
Сброс одного из фильтров осуществляется выбором опции Все раскрывающегося списка этого фильтра.
Отмена режима фильтрации (без уничтожения фильтров) реализуется командойДанные / Фильтр / Показать все
Удаление фильтров ( т.е. отключение Автофильтра ) производится командойДанные / Фильтр / Автофильтр
Формирование итогов
Подведение различных итогов - один из способов обработки списка.
Для получения итоговой информации в Excel используется команда Данные/Итоги..., которая позволяет применить одну из итоговых функций к некоторому полю или полям. При этом итоги могут быть сформированы не только ко всему списку, но и к отдельным группам записей, имеющих одинаковые значения по одному из полей.
Перед выполнением команды Итоги список должен быть отсортирован по тому полю, по которому предусматривается группирование записей.
После сортировки можно подвести итоги по списку в целом и итоги по группам записей.
Команда Данные/Итоги... вызывает диалоговое окно Промежуточные итоги, содержащее три поля ввода.
В поле ввода При каждом изменении в указывается (выбирается из раскрывающегося списка) то поле, по которому группируются записи для получения промежуточных итогов.
В списке поля ввода Операция выбирается функция, вычисляющая итоги. Для подведения итогов по числовым полям используются следующие функции: Сумма (по умолчанию), Среднее, Максимум, Минимум, Произведение, Кол-во чисел и функции, вычисляющие отклонение от среднего.
Для подведения итогов по нечисловым полям используется по умолчанию операция Кол-во значений, вычисляющая количество записей.
В поле ввода Добавить итоги по выделяются (левой кнопкой мыши) столбцы, по которым подводятся итоги.
Диалоговое окно Промежуточные итоги содержит три флажка.
Флажок Заменить текущие итоги разрешает обновление данных. Для сохранения текущих итогов и добавления новых следует снять этот флажок.
Установка флажка Конец страницы между группами приводит к вставке разделителя страниц перед каждой группой данных. Вследствие этого каждая группа с промежуточными итогами будет распечатываться на отдельной странице.
При установке флажка Итоги под данными строки промежуточных итогов и общих итогов помещаются под соответствующими данными, а при снятии флажка - над ними.
Кнопка Убрать все диалогового окна позволяет удалить все итоги из текущего списка.
Кроме итоговых строк, команда Данные/Итоги... формирует так называемую структуру, которая соответствует группировке данных для вычисления промежуточных и общих итогов.
Структура документа - это средство, которое позволяет управлять сокрытием или выводом на экран частей рабочего листа.
Слева от таблицы с итогами располагаются символы структуры - кнопки "+" и "-".
Кнопка "-" (символ сокрытия деталей) позволяет скрыть группу данных.
Кнопка "+" (символ показа деталей), которая появляется после сокрытия данных, позволяет их восстановить.
Замечание. Сокрытие данных не влияет на итоговые величины.
К символам структуры относятся и кнопки с изображением цифр ("1", "2", ...), обозначающие уровни структуры. Если нажать кнопку с номером уровня, то будут скрыты детальные данные этого уровня и всех низших.
Сформированную структуру можно скрыть. Для этого следует выполнить команду Сервис/Параметры. и на вкладке Вид снять флажок Символы структуры.
Структуру можно удалить, выполнив команду Данные/Структура. Удаление структуры не приведет к удалению итоговых строк.
ЗАЩИТА И СОКРЫТИЕ ДАННЫХ
Защитить данные в Excel можно двумя способами: используя средства запрещения доступа к данным и средства сокрытия данных.
Защита книги и листа
Защитить рабочую книгу в целом можно с помощью командыСервис / Защита / Защитить книгу...
В защищенной книге нельзя удалять или копировать листы, но возможно менять их содержимое.
Защита отдельного листа производится командойСервис / Защита / Защитить лист.
При защите и книги, и листа можно указать пароль, который задается с подтверждением в "секретном" режиме (в виде последовательности звездочек).
Снять защиту с рабочего листа можно, выполнив командуСервис / Защита / Снять защиту листа.
Снятие защиты с рабочей книги осуществляется аналогичной командойСервис / Защита / Снять защиту книги.
Замечание. Если защита была дополнена паролем, то снять защиту можно будет только в том случае, если известен пароль.
Защита ячеек рабочего листа
Для того чтобы защитить лист, но при этом разрешить изменение содержимого части ячеек, необходимо с этих ячеек снять статус "защищаемая ячейка", а затем защитить рабочий лист. Для этого нужно выполнить следующие действия:
- Снять защиту с листа, если он был защищен.
- Выделить все ячейки, содержимое которых может изменяться.
- Выполнить команду Формат / Ячейки и во вкладке Защита сбросить флажок Защищаемая ячейка.
- Выполнить командуСервис / Защита / Защитить лист...
Сокрытие данных
Скрыть формулы в ячейках можно аналогично снятию защиты с ячеек, содержимое которых может изменяться (см. выше). Только при выполнении команды Формат/Ячейки. во вкладке Защита следует установить флажок Скрыть формулы.
В Excel существует возможность скрыть любые строки и столбцы таблицы (в том числе и несмежные). Для этого достаточно выделить принадлежащие им ячейки и выполнить командуФормат / Строка / Скрыть или Формат / Столбец / Скрыть
Отображение скрытых данных выполняется командой Формат / Строка / Показать или Формат / Столбец / Показать
Скрыть (отобразить ) рабочий лист можно командойФормат / Лист / Скрыть ( Формат / Лист / Показать )
Сокрытие (отображение) всей книги выполняется командой Окно / Скрыть ( Окно / Показать )
ЗАДАЧИ И УПРАЖНЕНИЯ ЗАДАЧА
РАСЧЕТ ЗАРПЛАТЫ
Дан список сотрудников предприятия с указанием их табельных номеров, должности, номера отдела.
Необходимо произвести расчет зарплаты с указанием итоговой информации по всему предприятию в целом и по отделам. Рассмотрим упрощенный вариант задачи расчета зарплаты, когда все начисления уже произведены, а удержания состоят только из подоходного налога, исчисляемого как 12% от суммы начислений.
Этап 1. С о з д а н и е т а б л и ц ы р а с ч е т а з а р п л а т ы
Таблица расчета зарплаты представляет собой (рис.1 ) список - базу данных Excel.
Для создания списка используйте технологию, описанную в разделе 1:
1. Введите в ячейки B2 : H2 заголовки столбцов:
Ф.И.О., Таб. номер, Отдел, Должность,
Начислено, Удержано, К выдаче.
2. Отрегулируйте ширину столбцов в соответствии с введенными заголовками. Обведите строку заголовков рамками.
3. Так как в простейшем случае удержания составляют 12% от начисленного, введите в ячейку G3 формулу=F3 * 12%
4. Сумма к выдаче в ячейке H3 будет равна разности начислений и удержаний:=F3 - G3
5. Присвойте рабочему листу содержательное имя, например Зарплата вместо Листі и удалите лишние листы.
6. Сохраните текущее состояние таблицы в личной папке в файле с именем salary.xls.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Рис.1. Таблица для расчета зарплаты |
Для ввода данных в созданную таблицу - список следует воспользоваться Формой (см. раздел 1.) :
1. Выделите любую ячейку заголовка таблицы (B2:H2).
2. Выберите пункт меню Данные/ Форма...
3. В открывшемся диалоговом окне Зарплата (по имени рабочего листа), нажимая кнопку Добавить, внесите данные об очередном сотруднике: его фамилию и инициалы, табельный номер, номер отдела, должность и начисленную сумму.
Записи добавляются в конец таблицы. Доступ к вычисляемым полям Удержано и К выдаче блокирован.
4. После ввода последней записи нажмите кнопку Закрыть.
Этап 3. Р е д а к т и р о в а н и е т а б л и ц ы
Добавление новых записей осуществляется в конец таблицы. Для этого, находясь в пределах списка, выполните п.п.2-4 этапа 2.
Для внесения исправлений в записи списка также используется форма. Предположим, что сотрудникам 2 отдела, зарплата которых менее 1000 р., повысили ее. Для этого :
1. Вызовите окно диалога формы командой Данные / Форма...
2. Нажмите кнопку Критерии. При этом поля формы очистятся, а название кнопки Критерии заменится на Правка.
3. Перейдите с помощью клавиши ТаЬ в поле Отдел и укажите номер отдела - 2, а в поле Начислено задайте условие 1000.
4. Нажмите клавишу Enter или кнопку Правка для возврата в режим просмотра и редактирования данных.
5. Теперь в форме отображаются только необходимые записи ( сотрудники 2 отдела, получающие менее 1000 р.). Листая их с помощью кнопок Назад и Далее, внесите исправления в поле Начислено.
6. Закройте форму, нажав кнопку Закрыть.
Для удаления записи из списка сначала ее нужно найти (см. п.п.1-4), а затем удалить, нажав кнопку Удалить.
Этап 4. В ы б о р д а н н ы х и з т а б л и ц ы р а с ч е т а за р п л а т ы
При обработке данных таблицы расчета зарплаты может возникнуть необходимость отбора данных, имеющих определенное значение или удовлетворяющих определенным условиям, то есть организации разнообразных запросов.
Предположим, что необходимо выделить из таблицы записи о сотрудниках 3 отдела с зарплатой ниже 1000 р. с целью возможности ее повышения.
Эта задача уже решалась для сотрудников 2 отдела на этапе 3 с помощью формы. Теперь для выбора данных из таблицы можно воспользоваться автофильтром. Для этого:
1. Выполните команду Данные / Фильтр / Автофильтр
В ячейках заголовков столбцов появятся кнопки-стрелки списков фильтров.
2. Для отбора сотрудников 3 отдела нажмите кнопку со стрелкой в поле Отдел и в списке фильтра выберите 3.
3. Для наложения условия на зарплату (менее 1000 р.) нажмите кнопку со стрелкой в поле Начислено и выберите строку Условие.
4. В диалоговом окне Пользовательский автофильтр укажите условие 1000 и нажмите кнопку OK.
После всех этих действий на экран будет выведена часть таблицы расчета зарплаты с необходимыми сведениями.
5. Перемещаясь по выделенным записям сотрудников 3 отдела, внесите исправления в поле Начислено.
6. Отмените режим фильтрации командойДанные / Фильтр / Показать все
Упражнение 11.1. Выделите из таблицы расчета зарплаты записи об инженерах 3 отдела.
Упражнение 11.2. Выделите из таблицы записи о сотрудниках 1 отдела, у которых сумма к выдаче находится в пределах от 1000 р. до 1500 р.
Замечание. После выполнения упражнений отмените режим фильтрации (см. п. 6) или удалите фильтры командой
Данные / Фильтр / Автофильтр
Этап 5. С о р т и р о в к а т а б л и ц ы р а с ч е т а з а р п л а т ы
Так как предприятие имеет несколько отделов, удобнее работать со списком, в котором записи отсортированы по отделам, а в пределах одного отдела по ФИО сотрудников.
1. Выполните команду Данные / Сортировка...
2. В открывшемся диалоговом окне Сортировка диапазона в двух полях ввода задайте или выберите из списка ключи сортировки: Отдел, а затем Ф.И.О. Установите порядок сортировки в обоих случаях - по возрастанию.
Упражнение 11.3. Отсортируйте список по двум ключам: Отдел, Начислено. Установите для поля Начислено порядок сортировки по убыванию.
Упражнение 11.4. Отсортируйте список по трем ключам:
а) Отдел, Должность, Ф.И.О. ;
б) Отдел, Должность, Таб. номер.
Указание к решению. Для выполнения упражнений 11.3, 11.4. трижды скопируйте рабочий лист Зарплата. Назовите новые листы, например, Сортировка 1, Сортировка 2 и Сортировка 3.
Этап 6. П о л у ч е н и е в е д о м о с т и
Ведомость на зарплату (рис.2) должна включать все данные таблицы расчета зарплаты за исключением должности сотрудников и итоговую информацию:
- суммы начисления, удержания и выдаваемой на руки суммы по каждому отделу и в целом по предприятию,
- среднюю зарплату (начисление) по каждому отделу и в целом по предприятию.
Для получения итоговой информации выполните следующие шаги:
1. Скопируйте рабочий лист Зарплата и присвойте новому листу другое имя, например, Ведомость.
2. В ячейку C1 введите название таблицы Ведомость.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Рис. 2. Итоговая ведомость |
4. Записи таблицы должны быть отсортированы по отделам. Если список не отсортирован, выполните сортировку по полю Отдел (этап 5).
5. Выделите одну из ячеек списка и выполните команду Данные / Итоги ...
6. В открывшемся диалоговом окне Промежуточные итоги выполните следующие действия:
- Раскройте список в поле ввода При каждом изменении в и выберите то поле, по которому группируются записи для получения итогов. Таким полем в поставленной задаче является поле Отдел, т.к. все итоги подводятся по отделам.
- В поле ввода Операция по умолчанию будет выбрана функция Сумма.
- В поле ввода Добавить итоги по выберите элементы, по которым подводятся итоги: Начислено, Удержано и К выдаче. При помощи полосы прокрутки раскрывающегося списка пройдите по всем его элементам и проверьте, что другие элементы не помечены.
- Оставьте отметки для флажка Заменить текущие итоги и для флажка Итоги под данными.
- Нажмите кнопку ОК.
7. На экране появятся строки с промежуточными итогами для каждой группы записей и общий итог. Обратите внимание на кнопки, которые появились слева от таблицы. Кнопки с цифрами 1,2 и 3 обеспечивают степень детализации показа данных в таблице: кнопка 3 - вся таблица с промежуточными итогами и общим итогом; кнопка 2 - только итоги: промежуточные и общий; кнопка 1 - только общий итог. Той же цели служат и кнопки со знаками + и -. Нажмите на каждую из кнопок и посмотрите, как свернулась структурная схема слева. После этого разверните весь список со всеми итогами, нажав кнопку 3.
8. Добавьте в таблицу итоговые строки по вычислению среднего значения по полю Начислено'.
- Выполните команду Данные/Итоги...
- В поле ввода При каждом изменении в оставьте то же значение Отдел.
- В поле ввода Операция выберите элемент Среднее.
- В поле ввода Добавить итоги по уберите отметку с элементов Удержано и К выдаче.
- Для сохранения старых промежуточных итогов уберите отметку с флажка Заменить текущие итоги. Это позволит добавлять новые итоговые строки к уже имеющимся.
- Нажмите кнопку ОК. На экране появились новые итоговые строки, добавилась кнопка с номером 4 и изменилась структурная схема детализации.
9. Можно улучшить внешний вид таблицы, выполнив ее форматирование. Попробуйте воспользоваться Автоформатом. Для этого выполните команду Формат/Автоформат. и выберите один из вариантов форматирования. В окне просмотра для каждого варианта можно увидеть образец формата. Перед форматированием списка выделите одну из ячеек списка, тогда весь список будет выбран автоматически. Чтобы отформатировать только часть списка, ее следует выделить.
10. Чтобы убрать все итоги, выделите любую ячейку списка, выполните команду Данные/Итоги. и в диалоговом окне Промежуточные итоги нажмите кнопку Убрать все.
11. Для отображения скрытых столбцов используйте командуФормат/Столбец /Показать
Упражнение 11.5. На отдельном листе получите таблицу кадрового состава предприятия с указанием общего числа сотрудников и их количества в каждом отделе.
Указание к решению. Скопируйте рабочий лист Зарплата, назовите новый лист, например, Кадры. Отсортируйте таблицу по полям Отдел, Должность и Таб.номер (см. упр. 11.4 б)). Скройте столбцы Начислено, Удержано и К выдаче. Подсчитате количество сотрудников в каждом отделе и в целом на предприятии, используя функцию Кол-во чисел.
Упражнение 11.6. На отдельном листе получите таблицу, сгруппированную по должностям сотрудников с указанием номеров отделов.
Этап 7. З а щ и т а д а н н ы х
В ведомости на зарплату (рис.2) необходимо защитить от изменения заголовок таблицы и заголовки столбцов, столбцы Удержано и К выдаче, а также скрыть формулы в последних столбцах. Для этого:
1. Если лист Ведомость был защищен, снимите с него защиту командой Сервис /Защита /Снять защиту листа...
2. Выделите столбцы B : F, содержимое которых может меняться. Выполните команду Формат/Ячейки. и во вкладке Защита сбросьте флажок Защищаемая ячейка.
3. Выделите столбцы G : H, формулы в которых должны быть скрыты. Выполните команду Формат/Ячейки. и во вкладке Защита установите флажки Защищаемая ячейка и Скрыть формулы.
4. Выделите строки 1 и 2, которые должны быть защищены. Выполните команду Формат/Ячейки. и во вкладке Защита установите флажок Защищаемая ячейка.
5. Выполните командуСервис/Защита /Защитить лист.
Упражнение 11.7. Обеспечить защиту данных листа Кадры, полученного при выполнении упражнения 11.5.
ЗАДАЧА 12. СБЫТ ГОТОВОЙ ПРОДУКЦИИ
Даны сведения об отпуске готовой продукции со склада:
Код изделия, Наименование изделия, Покупатель,
Дата отпуска, Количество, подлежащее отпуску,
Количество отпущенное, Цена за единицу, Сумма .
Примерный вид таблицы сбыта продукции приведен на рисунке 3.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Рис.3. Таблица сбыта готовой продукции |
Упражнение 12.2. Выполните сортировку по следующим полям:
а) по дате отпуска и сумме отпущенного;
б) по наименованию и дате отпуска;
в) по покупателю и дате отпуска;
г) по покупателю и сумме отпущенного.
Упражнение 12.3. Выполните фильтрацию по следующим критериям :
а) для определенной даты получить список отпущенной продукции;
б) получить список продукции, отпущенной за конкретный период;
в) получить сведения об отпуске продукции конкретного наименования;
г) получить список изделий, отпущенных на сумму более 10 000 руб.;
д) получить список полностью отпущенных изделий;
е) получить список изделий, отпуск которых еще не производился. Упражнение 12.4. Подготовьте итоговые документы следующего вида
(каждый документ оформлять на новом листе):
а) получить итоговые суммы для каждого наименования отпущенных изделий и в целом по складу;
б) получить итоговые данные по количеству отпущенных изделий и сумме отпущенного на каждую дату;
в) для каждого покупателя сформировать на отдельном листе накладную на отпуск, скрыв столбец Покупатель и подведя итоговую сумму.
Указание к решению. В последнем упражнении, прежде чем копировать список на новый лист, выполните фильтрацию данных, выбрав только те, которые относятся к конкретному покупателю.
ЛИТЕРАТУРА
1. Гончаров А. Excel 7.0 в примерах.- СПб: Питер, 1996.
2. Долголаптев В.Г. Работа в Excel 7.0 для Windows 95 на примерах. - М: БИНОМ, 1995.
3. ^коль H., Альбрехт Р. Электронные таблицы Excel 5.0 для квалифицированных пользователей. - М: ЭКОМ, 1995.
