У вас вопросы?
У нас ответы:) SamZan.net

Лабораторная работа 8- Возможности базы данных в Excel.

Работа добавлена на сайт samzan.net: 2016-03-13

Поможем написать учебную работу

Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.

Предоплата всего

от 25%

Подписываем

договор

Выберите тип работы:

Скидка 25% при заказе до 3.2.2025

5

Электронные таблицы Excel в среде Windows.

  1.  

Лабораторная работа № 8:

Возможности базы данных в Excel. Формы данных.

Операции с данными

Цель работы: Ознакомление с возможностями баз данных Excel; научиться вводить данные в таблицу при помощи Формы данных; освоить сортировку данных по различным ключам; изучить возможности Автофильтра; научиться изменять ориентацию текста в ячейке и редактировать содержимое таблиц при помощи контекстного меню.  

Порядок выполнения работы:

  1.  Изменение ориентации текста в ячейке.
  2.  Возможности базы данных Excel. Формы данных.
  3.  Оперирование данными.

1. Изменение ориентации текста в ячейке.

Часто текст заголовка таблицы является слишком длинным, в результате чего таблица не умещается по ширине листа. Уместить всю таблицу на листе можно, если изменить ориентацию текста в ячейке. Для этого следует:

  1.  выделить ту ячейку, в которой находится ваш текст;
  2.  в пункте меню Формат выбрать Ячейки, затем выбрать вкладку Выравнивание;
  3.  в группе Ориентация установить угол (в градусах) относительно горизонта, по которым должен быть наклонен ваш текст, и нажать OK.

В отличие от редактора Word, электронные таблицы Excel позволяют расположить текст в ячейке под любым углом относительно горизонта в интервале от –90 до 90.

Задание 1.

Создайте таблицу ежедневного учета прихода и расхода товаров. Выполнение этого задания можно провести в несколько этапов: создание таблицы; заполнение таблицы; подбор и сортировка данных по определенному признаку.

  1.  Введите заголовки таблицы (см. образец на рис. 1): в ячейку Е1 – «Приход», в G1 – «Расход», в I1 –  «Остаток». Каждая из этих граф занимает две ячейки. Чтобы расположить название ячейки «Приход» по центру двух, следует выделить ячейки Е1 и F1, затем в меню ФорматЯчейки на вкладке Выравнивание установите в группе по горизонтали выравнивание по центру выделения. Аналогичные действия проделайте с названиями в ячейках G1 и I1.
  2.  Строкой ниже введите остальные пункты заголовка: в ячейку А2 - №, в ячейку В2 – «Отдел», в  С2 – «Наименование товара», в D2 – «Единица измерения», в Е2 – «Цена прихода», в F2 – «Кол-во прихода», в G2 – «Цена расхода», в Н2 – «Кол-во расхода», в I2 – «Кол-во остатка» и последнюю ячейку J2 – «Сумма остатка». Выделите вторую строку заголовка и выровняйте по центру.

Образец таблицы:

ПРИХОД

РАСХОД

ОСТАТОК

№

Отдел

Наименование товара

Единица измерения

Цена прихода

Кол-во прихода

Цена расхода

Кол-во расхода

Кол-во остатка

Сумма остатка

1

2

3

4

5

6

Рис. 1

  1.  Для того, чтобы вся таблица могла разместиться по ширине на листе, в некоторых ячейках текст следует развернуть на 900. Выделите те ячейки, в которых нужно «развернуть» текст и выберите команду ФорматЯчейки, на вкладке Выравнивание выберите Ориентацию текста и обязательно активизируйте переключатель Переносить по словам (вертикальное выравнивание оставьте По нижнему краю).
  2.  Для оставшихся (неразвернутых) ячеек примените вертикальное выравнивание По центру в меню ФорматЯчейки. Выделите все столбцы и примените команду ФорматСтолбецАвтоподбор ширины.
  3.   Задайте обрамление таблицы ФорматЯчейки, вкладка Рамка.
  4.  Установите в ячейках, содержащих цены денежный формат числа (ФорматЯчейки, вкладка Число).
  5.  Введите нумерацию строк таблицы (столбец №), при помощи Маркера заполнения.
  6.  Вставьте формулу для подсчета количества остатка («Кол-во прихода» минус «Кол-во расхода»): в ячейку I3 “=F3-H3” и заполните до строки 7 остальные ячейки этого столбца введенной формулой при помощи Маркера заполнения. Затем наберите формулу для подсчета суммы остатка («Кол-во остатка» умножить на «Цену расхода»): в ячейку J3 “= I3*G3” и распространите эту формулу вниз по таблице.

2. Возможности базы данных в Excel. Формы данных.

Одной из типичных задач, выполняемых с помощью электронных таблиц, является ведение списков – списков номеров телефонов, списков клиентов предприятия, списков торговых и финансовых операций и т.д. Такого рода большие списки, содержащие однотипную информацию, называют базой данных.  Основные правила оформления баз данных:

  1.  каждый столбец должен содержать информацию одного типа;
  2.  несколько верхних строк (как минимум - одна) должны содержать заголовки, описывающие содержимое расположенного ниже столбца;
  3.  не включать в список пустые строки и столбцы.

Заполнение таблицы с помощью формы данных.

Новую информацию в список можно внести, если перейти к первой пустой строке внизу списка и вводить данные с клавиатуры. Однако более удобным способом добавления информации в базу данных является использование Формы данных.

Для ввода данных с помощью Формы необходимо выделить любую ячейку списка и в пункте меню Данные выбрать команду Форма. В результате появится диалоговое окно, представляющее собой одну строку списка с подписанными названиями столбцов. Рядом с каждым заголовком столбца расположено поле для вводы информации, если этот столбец не содержит значений, вычисленных по формуле. В правой части Формы находятся кнопки для работы со списком.

Перемещаться между записями можно либо при помощи кнопок Предыдущая, Следующая, либо клавишами управления курсором (вверх, вниз), либо перемещая бегунок на полосе прокрутки формы данных. Перемещаться между окнами редактирования (в которые вносятся данные) удобно клавишей Tab. 

Чтобы вставить в список новую строку, следует нажать кнопку Добавить. Чтобы вернуться в лист таблицы, достаточно нажать кнопку Закрыть.

Редактирование ячеек при помощи контектсного меню.

При редактировании таблиц во многих случаях удобнее пользоваться контекстным меню, вызываемым нажатием правой клавиши мыши. Так, для форматирования ячеек их достаточно выделить, щелкнуть правой клавишей мыши в тот момент, когда указатель мыши находится внутри выделения и выбрать команду ФорматЯчеек, вы перейдете к тому же диалоговому окну. Редактировать содержимое ячейки (исправлять, изменять данные) совсем не обязательно в Строке формул. Если дважды щелкнуть мышью по ячейке, в ней появится текстовый курсор, и можно произвести все необходимые исправления.

Задание 2.

Теперь продолжите выполнение задания и приступайте к заполнению таблицы данными.

  1.  В текущей работе обычно приходится заполнять таблицы вперемешку, например, в порядке поступления товара, поэтому вносите данные в таблицу в произвольном порядке.. Заполнение рабочей книги удобнее проводить в режиме Формы данных.
  2.  Выделите таблицу без первой строки, но захватите обязательно нижнюю незаполненную строку (в образце таблицы это строка 8). Эта строка должна содержать все формулы и нумерацию. Если вы забудете выделить в таблице незаполненную строку, то не будете иметь возможность продолжать заполнение таблицы в режиме формы.
  3.  Выполните команду ДанныеФорма. Вы получите в диалоговом окне форму данных, содержащую статический текст (имена полей базы данных) и окон редактирования, в которых можно вводить и редактировать текст. Вычисляемые поля (в которых размещены формулы) выводятся на экран без окон редактирования («Кол-во остатка» и «Сумма остатка»). Теперь вы имеете свою таблицу как бы в форме отдельных карточек – записей (каждая из которых представляет строку таблицы).
  4.  Начинайте заполнение формы. Введите название отдела: Мясной, нажните Tab или перейдите мышью в следующую строку, в которой введите название поступившего товара, например, говядина. В следующей строке введите ед измерения: кг. В следующей строке введите цену прихода, например 28, далее – кол-во прихода, например, 50, далее – цену расхода, например 32, далее – кол-во расхода, например, 41. Когда заполните всю запись, нажмите клавишу Enter, и вы автоматически перейдете к новой чистой карточке – записи. Как только вы заполните новую запись, вся внесенная вами информация автоматически воспроизведется и в исходной таблице.
  5.  Заполните несколько новых записей на ваше усмотрение (или см. образец таблицы на рис.2). Вводите данные так, чтобы были товары из одного и того же отдела, и обязательно присутствовал товар с нулевым остатком. После окончания ввода данных нажмите кнопку Закрыть. В последних столбцах автоматически появятся результаты вычислений.

Образец таблицы:

ПРИХОД

РАСХОД

ОСТАТОК

№

Отдел

Наименование товара

Единица измерения

Цена прихода

Кол-во прихода

Цена расхода

Кол-во расхода

Кол-во остатка

Сумма остатка

1

Мясной

Говядина

кг

28р.

50

32р.

41

9

288р.

2

Кондитерский

Конфеты

упаковка

60р.

50

75р.

44

6

450р.

3

Молочный

Кефир

упаковка 1л

5р.

40

9р.

35

5

45р.

4

Мясной

Колбаса

кг

35р.

30

45р.

24

6

270р.

5

Вино-водочнный

Водка

бут. 0,5 л

25р.

100

32р.

72

0

0р.

6

0

0р.

Рис. 2.

3. Оперирование данными.

Фильтрация данных.

Отфильтровать список – это значит скрыть все строки, кроме тех, которые удовлетворяют условиям отбора. Фильтрация данных по определенному признаку позволяет видеть не всю таблицу, а только ту ее часть, которая сейчас нужна. Это особенно удобно при большом количестве данных в таблице.

Автофильтр.

Для простых условий отбора данных используется команда Автофильтр. Сначала следует выделить любую ячейку в списке, затем в пункте меню Данные в подменю Фильтры выбрать команду Автофильтр. В результате появятся кнопки со стрелочками радом с каждым заголовком столбца. Это кнопки автофильтра. Нажав на эту кнопку, можно раскрыть список значений, которые можно использовать для условий отбора. После фильтрации списка в Строке состояния будут содержаться количество отобранных строк и общее число записей.

Для того, чтобы восстановить все данные (воспроизвести таблицу в полном виде с перечнем всех товаров), достаточно убрать отметку команды Автофильтр (команда Данные Фильтр).

Примеры использования Автофильтра:

  1.  Автофильтр можно использовать, чтобы  найти заданное число наибольших или наименьших элементов в списке. Для этого следует нажать на кнопку автофильтра и выбрать условие отбора: Первые 10.
  2.  Для того, чтобы найти пустые ячейки в данном столбце нужно нажать кнопку автофильтра и внизу условий фильтрации выбрать условие отбора Пустые. Если пустые строки с пустыми ячейками данного столбца нужно скрыть, то следует выбрать условие отбора Непустые.
  3.  Если требуется оставить в таблице только те строки, в которых в ячейках данного столбца содержится определенное число, то в списке условий отбора в Автофильтре следует выбрать именно это число.

Операции с отфильтрованными данными.

По отфильтрованному участку таблицы можно вычислять суммы, произведения и другие операции так, будто никаких скрытых строк нет. Если выделить все показанные на листе строки и нажать на кнопку Автосумма на Панели инструментов, то посчитается специальная функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ. В ней первым аргументом является номер математической или статистической операции (см. ниже список номеров операций), а вторым – интервал вычислений.

Номера некоторых математических и статистических операций:

1 –  вычисление среднего;

2,3 – подсчет количества чисел и непустых ячеек;  

4, 5 – вычисление максимума и минимума;

6 – произведение;

7, 8 – стандартное отклонение;

9 – суммирование;

10,11 – дисперсия.

Скрытые столбцы.

Электронные таблицы позволяют временно скрыть некоторые столбцы, которые не участвую в данный момент в обработке данных. Для этого нужно выделить столбец, который хотите скрыть, затем  вызвать контекстное меню (правой клавишей мыши в тот момент, когда указатель мыши находится внутри выделения) и выбрать команду Скрыть. Вместо команды контекстного меню можно воспользоваться командой горизонтального меню ФорматСтолбецСкрыть. Скрытые столбцы можно вернуть, если выделить таблицу целиком (кнопка без опознавательных знаков выше названия первой строки) и воспользоваться командой меню ФорматСтолбецПоказать.

Задание 3.

Теперь осталось научиться оперировать с введенными в данными.

  1.  Вы заполнили таблицу в порядке поступления товаров, а хотелось бы иметь список товаров по отделам, для этого примените сортировку строк:
  2.  выделите таблицу без заголовка и выберите команду ДанныеСортировка;
  3.  выберите первый ключ сортировки: в раскрывающемся списке Сортировать выберите «Отдел» и установите переключатель в положение по возрастанию (все отделы в таблице расположатся по алфавиту);
  4.  чтобы внутри отдела все товары размещались по алфавиту, выберите второй ключ сортировки: в раскрывающемся списке Затем по выберите «Наименование», установите переключатель в положение по возрастанию.

Теперь вы имеете полный список товаров по отделам.

  1.  Продолжим знакомство с возможностями баз данных Excel. Часто ежедневно нужно распечатывать список товаров, оставшихся в магазине (имеющих ненулевой остаток), но для этого сначала нужно получить такой список, т. е. отфильтровать данные. Фильтрацию можно осуществить следующим образом. Сначала нужно подготовить Пользовательский автофильтр. Для этого:
  2.  выделите таблицу со второй строкой заголовка (как перед созданием формы данных);
  3.  выберите команду меню ДанныеФильтрАвтофильт;
  4.  снимите выделение с таблицы.
  5.  У каждой ячейки заголовка таблицы появилась кнопка, (она не выводится на печать) позволяющая задать критерий фильтра. Следует оставить все записи с ненулевым остатком. Раскройте список ячейки «Кол-во остатка», нажав на созданную вами кнопку Автофильтра в этом столбцу, и выберите 0. Вместо полного списка товаров, вы получили список непроданных на сегодняшний день товаров. Можно известным нам способом вставить формулу подсчета общей суммы остатка (в режиме фильтра будет подсчитана сумма выведенных на экран данных). Фильтр можно усилить. Если дополнительно выбрать какой-нибудь конкретный отдел, то можно получить список непроданных товаров по отделу.
  6.  Для того, чтобы снова увидеть перечень всех непроданных товаров по всем отделам, нужно в списке Отдел выбрать критерий Все.
  7.  Но и это еще не все возможности баз данных Excel. ежедневно нет необходимости распечатывать все сведения о непроданных товарах, нас интересует только «Отдел», «Наименование» и «Кол-во остатка». Можно временно скрыть остальные столбцы. Для этого выделите столбец №, вызовите контекстное меню (правой клавишей мыши в тот момент, когда указатель мыши находится внутри выделения) и выберите команду Скрыть. Таким же образом можно скрыть и остальные столбцы, связанные с приходом, расходом и суммой остатка. Вместо команды контекстного меню можно воспользоваться командой горизонтального меню ФорматСтолбецСкрыть.
  8.  Восстановите все данные в полном виде с перечнем всех товаров: в пункте меню Данные Фильтр уберите отметку команды Автофильтр.

Контрольные вопросы.

  1.  Опишите способ изменения ориентации текста в ячейке. Какие дополнительные возможности ориентации текста в ячейке предоставляют электронные таблицы Excel по сравнению с редактором Word?
  2.  Как можно вызвать контекстное меню? Для чего оно предназначено?
  3.  Какими способами можно заполнять данными таблицу в Excel?
  4.  В каких случаях удобно использовать Фильтры?
  5.  Как с помощью команды Автофильтра произвести фильтрацию списка при заданном условии отбора?
  6.  Опишите несколько примеров использования Автофильтра.
  7.  Для чего нужна функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ?
  8.  Какими способами можно скрыть некоторые столбцы таблицы? Как вернуть скрытые столбцы?




1. In when required; Selective circultion between nnulus nd tubing; Instll plug in the tubing e
2. сухие относительная влажность воздуха не более 60 ; 2 влажные относительная влажность воздуха более 753
3. Методические рекомендации по оформлению выпускных квалификационных работ курсовых проектов-работ для о
4. Явка с повинной и ее реализация в уголовном судопроизводстве
5. ЛЕКЦИЯ 11 Ликероводочные изделия План 1
6. хозяйственной деятельности субъекта; рациональности и целесообразности охвата контролем сфер деятельности
7. 04 апреля 2011 года между Обществом с ограниченной ответственностью Производственнокоммерческая фирма Вер
8. Управление развитием физической культуры и спорта в субъекте Российской Федерации (на примере Краснодарского края)
9. Ипотечное кредитование в России и за рубежом на современном этапе
10. Когда нужна экологическая оценка1

Материалы собраны группой SamZan и находятся в свободном доступе