Какие команды отражают базу данных excel в виде структуры и предоставляют возможность разворачивать

Обновлено: 07.07.2024

2.1. Общие положения.

2.2. Списки Excel как база данных.

2.3. Проверка данных при вводе.

2.4. Сортировка данных.

2.5. Промежуточные итоги в базе данных.

2.6. Автофильтр.

2.7. Расширенный фильтр.

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

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

5. Список рекомендуемой литературы.

1. ЦЕЛЬ РАБОТЫ

Цели:

  • повышение уровня понимания темы "Базы данных в приложении Microsoft Excel";
  • овладение специальными техническими умениями конструирования и использования реляционной базы данных на уровне их свободного использования;
  • развитие навыков самостоятельной работы и способности применить полученные знания на практике при разработке собственной базы данных.

2. ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ

2.1. Общие положения

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

Для учета данных о сотрудниках на предприятиях используют самые разнообразные методы. В одних организациях существуют журналы учета, куда информация вносится вручную, в других применяются классические базы данных для учета кадров, в третьих используются СУБД Access. Но в большинстве случаев на небольших предприятиях учет данных о сотрудниках ведется в электронных таблицах Microsoft Excel.

В предложенном пособии рассматриваются основные возможности, предоставляемые Excel, для работы с базами данных на примере списка "Сотрудники", который содержит информацию о работниках некоторого условного предприятия. Такая база данных удобна по следующим причинам:

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

2.2. Списки Excel как база данных

Приложение Microsoft Excel обладает богатыми встроенными средствами для обработки и анализа данных. Аналогом простой базы данных в Excel служить список.

Список - это группа строк таблицы, содержащая связанные данные.

Отличительной особенностью списка является то, что каждый его столбец содержит однотипные данные, например, перечень фамилий, дату рождения и т. д. (рис. 1).

Если провести аналогию между списком и табличной базой данных, то столбцы списка являются полями базы данных, а его строки - записями. Считается, что первая строка списка является его заголовком и содержит названия столбцов списка. Заголовок должен иметь на листе электронных таблиц горизонтальную ориентацию. Заголовки применяются Excel при составлении отчетов, а также при поиске и организации данных. Шрифт, размер шрифта, выравнивание и другие параметры форматирования, присвоенные заголовкам столбцов списка, должны отличаться от параметров, назначенных для строк данных. В списке не должно быть пустых строк и столбцов.

2.3. Проверка данных при вводе

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

2.4. Сортировка данных

Команда Сортировка позволяет переставить записи в другом порядке на основании значений одного или нескольких столбцов. Записи сортируются возрастанию/убыванию или по выбранному пользователю порядке (например, по дням недели).

Чтобы отсортировать список надо:

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

Примечание. Выбор настраиваемого порядка позволяет задать нестандартный порядок сортировки. Для этого надо в диалоговом окне Списки (рис. 2) выбрать НОВЫЙ СПИСОК, в поле Элементы списка ввести значения, образующие пользовательский порядок сортировки (например, АОП, ФЭО, ИВЦ, ИТО, МПО), после чего последовательно выбрать кнопки Добавить и ОК.

2.5. Промежуточные итоги в БД

Для организации списков используют команду Промежуточные итоги на ленте Данные в группе Структура, которая позволяет:

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

Перед вызовом команды Итоги список обязательно надо отсортировать по полю, которое будет использоваться для группировки.

Режим структуры, в котором оказывается список после выполнения команды Итоги, позволяет просматривать различные части списка с помощью кнопок, расположенных на левом поле (рис. 3).


Рис. 3. Просмотр списка в режиме структуры

Кнопки, расположенные в верхнем левом углу, определяют количество выводимых уровней данных. Кнопки со значками "+" и "-" предназначены для свертывания \ развертывания отельных групп.

Чтобы удалить промежуточный и окончательные итоги, надо повторно выполнить команду Промежуточные итоги, а затем щелкнуть по кнопке Убрать все.

2.6. Автофильтр

Отфильтровать список - значит показать только те записи, которые удовлетворяют заданному критерию.

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

Показать все записи по отфильтрованному полю, не убирая фильтр, можно выбрав в списке фильтра критерий Снять фильтр с….

Показать все записи по всем полям, не убирая фильтр, команда Очистить.

Для данных разного типа существуют дополнительные автофильтры, которые находятся в списке критериев Текстовые фильтры, Числовые фильтры, Фильтры по дате и т.д.

Если выделить какое-то числовое поле (например, Возраст), а в списке критериев выбрать Числовые фильтры, то появится список дополнительных фильтров (рис. 4), которые позволяют:

  • задать критерий в виде неравенства – критерии равно, не равно, больше, больше илиравно, меньше, меньшеилиравно, между;
  • вывести первые N значений – критерий Первые 10: после выбора в списке Числовых фильтров команду Первые 10…, необходимо в появившемся окне указать число значений (N), а также способ вычисления: количество элементов списка, % от количества элементов;
  • определить условие по среднему значению в указанном столбце – критерии Выше среднего, Ниже среднего;
  • самостоятельно задаваемый фильтр – критерий Настраиваемый фильтр.


Рис. 4. Дополнительные числовые фильтры

Настраиваемый фильтр позволяет задать критерии из одного или двух условий.

Простое условие состоит: из имени поля (атрибута); варианта условия (равно, не равно, больше, меньше, больше или равно, меньше или равно; начинается с, не начинается с, заканчивается на или не заканчивается на; содержит, не содержит); слова или числа для сравнения.

Сложное условие состоит из двух простых, соединенных союзами И или ИЛИ.

При написании значений в условиях сравнения в фильтрах можно использовать подстановочные знаки (Таблица 1).

1. MS Excel. Как при построении диаграммы выделить несколько диапазонов данных, расположенных в разных частях листа?

9. MS Excel. Укажите два списка, которые не принадлежат к стандартным списков автозаполнения:
упорядоченные названия месяцев;
+ названия областей Украины по алфавиту;
+ названия европейских стран по алфавиту;
названия дней недели.
10. MS Excel. Где в рабочем окне Microsoft Excel XP можно сразу увидеть сумму выделенных ячеек?
в заголовке рабочего окна;
в одном из полей статусной строки;
+ в строке формул.
11. Какая функция не может быть использована при создании сводной таблицы?
сумма;
количество значений;
+ округления;
максимум.
12. MS Excel. Укажите, какие адреса меняются в формуле при перемещении ее на место:
+ относительные адреса;
абсолютные адреса;
не изменяются никакие адреса.
13. Что понимают под Рабочей книгой в MS Excel?
системный файл;
+ файл с расширением названия .xls;
документ, состоящий из рабочих тетрадей;
текстовый документ, к которому введено таблицы.
14. Как называется строка для ввода данных в ячейки рабочего листа в MS Excel?
строка ввода;
строку статуса;
+ строка формул;
командную строку.

21. Возможна ли защита информации в отдельных ячейках таблицы MS Excel?
нет, потому что можно защищать только рабочие книги полностью;
так, можно установить защиту информации только в отдельных ячейках;
нет, потому что можно защитить только рабочий лист или рабочую книгу целиком;
+ так, можно установить защиту информации только в отдельных ячейках, но он начинает действовать только после защиты рабочего листа в целом.

22. Как целесообразнее изменить диапазон данных для построенной диаграммы в MS Excel?
изъять диаграмму i построить ее снова с помощью Мастера диаграмм;
+ с помощью команды Исходные данные контекстного меню диаграммы или меню программы Диаграмма;
с помощью команды Параметры диаграммы в меню программы Диаграмма.

23. В MS Excel при копировании формулы с относительной адресам в соседнюю за строкой ячейку:
относительные адреса меняются на абсолютные;
изменяется название колонки, а не номер строки;
изменяются i название колонки, i номер строки;
+ изменяется не название колонки, а номер строки.
24. Выберите правильное утверждение в MS Excel:
абсолютные адреса комiрoк изменяются при копировании формул;
+ абсолютные адреса и имена комiрoк не изменяются при копировании формул;
абсолютные i относительные адреса комiрoк изменяются при копировании формул.

25. MS Excel. Укажите правильный адрес ячейки:
А12С
+ В1256
123с
В1а

г)выполнять автоматическое форматирование диапазона ячеек.

2. MS Excel. Выберите правильную формулу для вычисления X ^ 3-3 * X для X = 12 (в противном случае развязку нет). Значение X — в ячейке B1:

+а) ЕСЛИ (ИЛИ (В1> 2; B1 = 12); B1 ^ 3-3 * B1; « Нет развязку »);

б) = ЕСЛИ (B1 = 12; B1 ^ 3-3 * B1; « Нет развязку »);

в) = ИЛИ (B1 ^ 3-3 * B1; « Нет развязку »).

3. MS Excel. С какого символа начинается формула в Microsoft Excel?

4 . MS Excel. Минимальной составной частью электронной таблицы является:

5. MS Excel. Как при построении диаграммы выделить несколько диапазонов данных, расположенных в разных частях листа?

+а) с помощью мыши и клавиши <Ctrl>;

б)с помощью мыши и клавиши <Alt>;

в)с помощью мыши и клавиши <Shift>.

6. MS Excel. Как найти среднее арифметическое значений ячеек B6 с рабочего листа Лист1 и С3 с листа Лист5?

а)= СРЗНАЧ (Лист1 $ B6; Лист5 $ C3);

+б) = СРЗНАЧ (Лист1! $ B $ 6; Лист5! $ C $ 3);

в)= СРЗНАЧ ( « Лист1 »! $ B $ 6: $ C $ 3).

а)деление на ноль;

+б) ширина ячейки не соответствует формату числа;

в)число в ячейке не соответствует допустимому числовом формата.

8. MS Excel. Как наиболее эффективно организовать хранение и подстановки различных вариантов входных данных для их многократного использования в таблице?

а)ввести все варианты в соседних ячеек и периодически менять в таблице адреса ячеек;

+б) написать простой макрос подстановки различных значений в таблицу;

в)для каждого варианта входных данных написать сценарий и использовать во время подстановки данных в таблице.

9. MS Excel. Укажите два списка, которые не принадлежат к стандартным списков автозаполнения:

а)упорядоченные названия месяцев;

+б) названия областей Украины по алфавиту;

+в) названия европейских стран по алфавиту;

г)названия дней недели.

10. Назовите основные типы данных, которые поддерживает табличный процессор MS Excel:

+а) текстовые, числовые;

б)формулы, текстовые, числовые;

в)числовые, формулы, дата-время, текстовые;

г)текстовые, числовые, проценты, формулы, функции.

11. Какая главная внешняя различие формул от других типов данных в MS Excel?

а)формулы обязательно помещают знаки арифметических действий;

+б) начинаются со знака рiвности =;

в)обязательно помещают встроенные функции;

г)в формулах отсутствует текстовая информация.

12. MS Excel. Где в рабочем окне Microsoft Excel XP можно сразу увидеть сумму выделенных ячеек?

а)в заголовке рабочего окна;

б)в одном из полей статусной строки;

+в) в строке формул.

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

14. MS Excel. Укажите, какие адреса меняются в формуле при перемещении ее на место:

+а) относительные адреса;

в)не изменяются никакие адреса.

15. Что понимают под Рабочей книгой в MS Excel?

+б) файл с расширением названия .xls;

в)документ, состоящий из рабочих тетрадей;

г)текстовый документ, к которому введено таблицы.

16. Как называется строка для ввода данных в ячейки рабочего листа в MS Excel?

+в) строка формул;

1 7 . Какие команды отражают базу данных Excel в виде структуры и предоставляют возможность разворачивать / сворачивать ее разделы с помощью мыши?

а)команды Данные \ Вид;

б)команды Данные \ Сортировка;

+в) команды Данные \ Итоги.

18. Язык программирования используется для разработки макросiв в MS Excel:

+б) Visual Basic for Applications;

19. MS Excel. Упорядочение значений диапазона ячеек называется:

20. MS Excel. Какими командами следует воспользоваться, чтобы занести в колонку чиcле от 1 до 10005?

На этом шаге мы рассмотрим создание структур рабочего листа.

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

Создать структуру можно одним из способов:

Чтобы автоматически создать стрктуру для некоторого диапазона данных, надо выполнить следующее:

  1. Поместить табличный курсор в любую ячейку диапазона.
  2. Выбрать команду Данные | Группа и структура | Создание структуры .

Программа Excel проанализирует формулы из выделенного диапазона и создаст структуру. В зависимости от формул будет создана горизонтальная, вертикальная или смешанная структура.

Если у рабочего листа уже есть структура, то будет задан вопрос, не хотите ли Вы изменить существующую структуру. Щелкните на кнопке Да , чтобы удалить старую и создать новую структуру.

Если воспользоваться командой Данные | Итоги , то Excel создаст структуру автоматически, при этом автоматически будут вставлены формулы для расчета промежуточных итогов, если данные введены в виде списка.

Excel определяет способ создания структуры на основании анализа формул, поэтому без формул автоматически создать структуру невозможно. Процесс создания структуры вручную подразумевает создание групп строк (для вертикальной структуры) или групп столбцов (для горизонтальной структуры).

Чтобы создать группу строк, выделите полностью все строки, которые нужно включить в эту группу, кроме строки, содержащей формулы для подсчета итогов. Затем выберите команду Данные | Группа и структура | Группировать . По мере создания группы Excel будет отображать символы структуры.

Если перед созданием группы Вы выделите диапазон ячеек (а не строки или столбцы целиком), то Excel отобразит диалоговое окно с вопросом о том, что Вы хотите сгруппировать. Затем программа сгруппирует целиком те строки или столбцы, ячейки которых содержатся в выбранном диапазоне.

Можно выбирать также группы групп. Это приведет к созданию многоуровневых структур. Создание таких структур следует начинать с внутренней группы и двигаться изнутри наружу. В случае ошибки при группировке можно произвести разгруппирование с помощью команды Данные | Группа и структура | Разгруппировать

В Excel есть кнопки инструментов, с помощью которых можно ускорить процесс группировки и разгруппировки (рис. 1). Кроме того можно воспользоваться комбинацией клавиш Alt + Shift + для группировки выбранных строк или столбцов, или Alt + Shift + для осуществления операции разгруппирования.

Читайте также: