Основы работы на компьютере (стр. 3 )

Для расчета количества клиентов, которые получили скидку свыше 50 руб., используем функцию:

СЧЕТЕСЛИ (диапазон; условие) – подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию. Формула будет выглядеть следующим образом: = СЧЁТЕСЛИ (C11:C15;" > 50")

Первый параметр в этой формуле показывает, в каком диапазоне будет вестись подсчет ячеек. Второй параметр – что нужно считать в указанном диапазоне (т. е. количество ячеек, значения в которых больше 50).

В результате расчетов получим следующую таблицу (рис. 2.21).

Рис. 2.21. Результирующая таблица

2.10. Работа с несколькими рабочими листами

Программа Excel позволяет использовать в рамках одной рабочей книги несколько рабочих листов. Названия рабочих листов ука­зываются на ярлычках в нижнем левом углу рабочей области. Вы­бор рабочего листа для текущей работы выполняют щелчком на соответствующем ярлычке.

Формулы могут ссылаться на ячейки, находящиеся в других рабочих листах. Формат такой «межлистовой» ссылки: <имя листа>!<адрес>

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

Программа Excel позволяет одновременно вводить или редактировать данные на нескольких рабочих листах. Это удобно, когда все рабочие листы содержат одинаковые заголовки или подписи. Чтобы работать сразу на нескольких рабочих листах, надо выбрать первый из них, а затем щелкнуть на ярлычках других листов, удерживая нажатой клавишу SHIFT или CTRL. В этом случае любая операция, проведенная на одном из рабочих листов, автоматически дублируется на остальных.

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

Электронные таблицы часто используются для ведения простейших баз данных. Таблица, используемая в качестве базы данных, состоит из нескольких столбцов, являющихся полями базы данных. Каждая строка – это отдельная запись. Если данные представлены в таком виде, программа Excel позволяет производить над ними сортировку. Сортировка – это упорядочение данных по возрастанию или по убыванию. Проще всего производить такую сортировку, выбрав одну из ячеек и щелкнув по кнопке– сортировка по возрастанию или сортировка по убыванию.

Также параметры сортировки задают командой Данные – Сортировка. При этом открывается диалоговое окно Сортировка диапазона (рис. 2.22), в нем можно выбрать от одного до трех полей сортировки, а также задать порядок сортировки по каждому полю.

Рис. 2.22. Диалоговое окно Сортировка диапазона

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

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

Фильтрацию данных можно выполнять двумя способами: с помощью автофильтра и расширенного фильтра. Чтобы использовать автофильтр, надо сначала выделить таблицу вместе с заголовками полей. Затем выполнить команду Автофильтр в меню Данные (рис. 2.23).

Рис. 2.23. Пример применения автофильтра

По команде Автофильтр Ехсеl помещает раскрывающиеся списки непосред­ственно в имена столбцов таблицы. Щелкнув по стрелке, можно выве­сти на экран список всех уникальных элементов соответствующего столбца. Если выделить некоторый элемент столбца, то будут скрыты все строки, кроме тех, которые содержат выделенное значение. Например, если выбрать значение К. счет, равное 50, то будут выбраны только проводки, которые имеют корреспондирующий счет 50 (рис. 2.24).

Элемент столбца, который выделен в раскрывающемся списке, называется крите­рием фильтра. Можно продолжить фильтрацию списка с помощью критерия из другого столбца. Чтобы удалить критерии фильтра для отдельного столбца, надо выбрать параметр Все в раскрывающемся списке. Чтобы показать все скрытые в списке строки, надо выбрать в меню Данные команду Фильтр, а затем – команду Показать все.

Рис. 2.24. Результат применения автофильтра

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

Для фильтрации таблицы по сложному критерию, а также для получения части исходной таблицы по нескольким заданным столбцам используется команда Расширенный фильтр меню Данные (рис. 2.25). Отличие этой команды от команды Автофильтр состоит в том, что, кроме перечисленных выше возможностей, отфильтрованные записи мож­но вынести в другое место рабочего листа Ехсеl, не испортив исходный список.

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

Чтобы использовать команду Расширенный фильтр, надо сначала создать таблицу критериев, которую следует разместить на том же рабочем листе, где исходная таблица, но так, чтобы не скрывать лист во время фильтрации. Для формирования таблицы критериев необходимо скопировать имена полей списка в ту часть рабо­чего листа, где будет располагаться таблица критериев. При этом число строк в этой таблице определяется только количеством критериев поиска. Однако включение пустых строк в таблицу критериев недопустимо, поскольку в этом случае будут найдены все записи таблицы. Задание критериев поиска в виде констант требует точной копии имен тех столбцов исходной таблицы, которые задают условия фильтрации.

Кроме таблицы критериев, для команды Расширенный фильтр надо определить вид выходного документа. Это означает, что следует скопировать в свободное место рабочего листа имена только тех полей таблицы, которые определяют вид выходного документа, т. е. необходимое количество столбцов будущей таблицы. Количество строк в выходном документе программа Ехсel определит сама.

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

2.13. Построение диаграмм

Построим диаграмму по данным, представленным в табл. 2.1.

Для этого выделите диапазон ячеек А1:В9, который содержит фамилии со­трудников и их зарплаты за январь. Затем через меню Вставка выберите команду Диаграмма... или нажмите на панели инструментов кнопку Мастер диаграмм . Вы­званный Мастер диаграмм предложит проделать четыре шага.

Шаг 1. Выбор типа диаграммы

Таблица 2.1.

	А	В
1	Ф. И.О.	Зарплата за январь
2		408,12
3		563,78
4		495,93
5		312,44
6		308,07
7		520,17
8		440,13
9		651,06

Выберите в появившемся окне тип диаграммы (рис. 2.26). Диаграммы бывают двух классов: стандартные и нестандартные. Выберите необходимый тип и нажмите кнопку Просмотр результата для предварительного просмотра диаграммы.

Рис. 2.26. Шаг 1: тип диаграммы

Выберем круговую диаграмму, а именно – объемный вариант разрезанной круговой диа­граммы.

Для перехода ко второму шагу построения диаграммы нажмите кнопку Далее >.

Шаг 2. Источник данных диаграммы

Рис. 2.27. Шаг 2: источник данных диаграммы

Теперь необходимо указать диапазон данных для построения диаграммы (рис. 2.27). Если диапазон нужно изменить, нажмите на кнопку . При этом диалоговое окно свернется (рис. 2.28), а данные для диаграммы будут выделены "бегающей" рамкой. Теперь можно изменить диапазон данных, например, выделить только первые 5 фамилий и их зарплаты, а затем снова нажать кнопку . После чего окно примет прежнее положение, но в диаграмме будет уже не 8 секторов, а только 5.

.

Рис. 2.28. Свернутое диалоговое окно Мастер диаграмм

Вернуться на предыдущий шаг можно, нажав кнопку < Назад. Переходим к третьему шагу, используя кнопку Далее >.

Шаг 3. Параметры диаграммы

В качестве параметров диаграммы можно указать название графика. Для этого на вклад­ке Заголовки в текстовом поле Название диаграммы щелкните мышью и напишите на­звание диаграммы, например Зарплата (рис. 2.29). При этом название диаграммы появится на образце. На вкладке Легенда можно указать расположение легенды, щелкнув мышью на соответствующем пункте (например, внизу). Легенду можно удалить с диаграммы, если убрать “галочку” возле надписи Добавить легенду. Подписи данных можно вывести на график значения возле секторов.

Рис. 2.29. Подписи данных на диаграмме

Обратите внимание, что существуют различные варианты Подписи значений:

·  нет (возле секторов отсутствуют подписи);

·  значение (возле каждого сектора будет проставлено соответствующее значение);

·  доля (возле каждого сектора значение будет переведено автоматически в %);

·  категория (каждому сектору будет дано название (аналогично легенде));

·  категория и доля (возле каждого сектора будут стоять название и значение в %).

При необходимости вернуться на предыдущий шаг нажмите кнопку < Назад (чтобы снова выбрать исходные данные). Переходим к четвертому шагу кнопкой Далее >.

Шаг 4. Размещение диаграммы

Укажите положение диаграммы в книге Excel: либо на отдельном листе, либо на листе с данными. Обычно диаграмму размещают на листе с данными, поэтому можно нажать кнопку Готово. После этого на листе появится диаграмма.

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

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

Изменить форматирование любого элемента диаграммы можно двумя способами.

1 способ. Дважды нажмите левую кнопку мыши на соответствующем элементе диаграммы. При этом появится диалоговое окно форматирования (в названии окна обязательно будет присутст­вовать слово формат, например, Формат легенды; это окно появится, если дважды щёлкнуть левой кнопкой мыши на легенде).

2 способ. Выделите одним нажатием левой кнопки мыши соответствующий элемент на диа­грамме, а затем нажмите правую кнопку мыши. Появится контекстное меню, из которо­го выберите команду Формат легенды... (рис. 2.30).

Рис. 2.30. Вызов контекстного меню

2.14. Практические упражнения

Упражнение 1. Установите в ячейке В12 денежный формат с двумя десятичными знаками после запятой и с отображением отрицательных чисел без знака минуса, но красным цветом.

Для этого выполните следующие действия:

1. Выделите ячейку В12.

2. Выберите в меню Формат команду Ячейки и затем вкладку Число.

3. В списке Числовые форматы выберите Денежный.

4. В окне Число десятичных знаков увеличьте счетчик до 2.

5. В списке Отрицательные числа выберите 2-ой элемент.

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

Рис. 2.31. Пример форматирования таблицы

1.  Выделите диапазон E1:R2 и на вкладке Выравнивание (меню Формат команда Ячейки) установите флажок Объединение ячеек.

2.  Аналогично выделите диапазоны A13:A15 и A16:A18; установите флажок Объединение ячеек; в списках Выравнивание по горизонтали и вертикали выберите – по центру.

Упражнение 3. Вычислите в Excel:

1)  (5+72)/(66-4);

2)  (6+sin(0,2))+(3+42).

Упражнение 4. Работа с листами:

1. Добавьте лист перед листом 2, переименуйте его.

2. Скопируйте лист 1 перед листом 3.

3. Переместите лист 2 в конец.

4. Удалите лист 3.

Упражнение 5. Отсортируйте таблицу текущего футбольного чемпионата (рис. 2.32).

Рис. 2.32. Пример таблицы для сортировки

Для этого выполните следующие действия:

1.  Укажите столбец в сортируемой таблице.

2.  Выберите команду Сортировка (меню Данные).

3.  В поле Сортировать по укажите столбец Команды.

4.  Переключатель рядом с полем Сортировать по установите в нужное положение (по умолчанию сортировка производится всегда по возрастанию).

5.  В поле Затем по укажите столбец В (выигрыш).

6.  Переключатель рядом с полем Затем по установите в нужное положение.

7.  В поле В последнюю очередь, по укажите столбец О (Очки).

8.  Переключатель рядом с полем В последнюю очередь, по установите в нужное положение.

9.  Нажмите кнопку ОК.

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

1.  Для решения каких задач можно применять электронные таблицы?

2.  Как называется документ в программе Excel? Из чего он состоит?

3.  Какие типы данных могут содержать электронные таблицы Excel?

4.  По какому признаку программа определяет, что данные в ячейке являются не значениями, а формулой?

5.  Что такое маркер заполнения?

6.  Как сделать так, чтобы текстовые данные, содержащиеся в ячейках, полностью помещались по ширине столбца?

7.  Как посмотреть и отредактировать формулу, содержащуюся в ячейке?

8.  В чем состоит удобство применения относительной адресации к ячейкам при записи формул?

9.  Какой тип адресации Excel использует по умолчанию?

10.  Что такое диапазон?

11.  Электронная таблица Excel может иметь до 65536 строк. Как вы думаете, откуда взята эта «некруглая» цифра? Чем она лучше, чем 50000 или 100000?

3. РАБОТА С БАЗАМИ ДАННЫХ

3.1 Основные понятия баз данных

База данных – это организованная структура, предназначенная для хранения информации. С понятием базы данных тесно связано понятие системы управления базой данных (СУБД). Это комплекс программных средств, предназначенных для создания структуры новой базы, наполнения ее содержимым, редактирования содержимого и визуализации информации. Под визуализацией информации понимается отбор отображаемых данных в соответствии с заданным критерием, их упорядочение, оформление и последующая выдача на устройство вывода или передача по каналам связи.

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

Ниже перечислены основные свойства полей таблиц баз данных на примере СУБД Microsoft Access:

·  Имя поля – определяет, как следует обращаться к данным этого поля при автоматических операциях с базой (по умолчанию имена полей используются в качестве заголовков столбцов таблиц).

·  Тип поля – определяет тип данных, которые могут содержаться в данном поле.

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

·  Формат поля – определяет способ форматирования данных в ячейках, принадлежащих полю.

·  Маска ввода – определяет форму, в которой вводятся данные в поле (средство автоматизации ввода данных).

·  Обязательное поле – свойство, определяющее обязательность заполнения данного поля при наполнении базы.

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

Базы данных Microsoft Access работают со следующими типами данных.

·  Текстовый – тип данных, используемый для хранения обычного неформатированного текста ограниченного размера (до 255 символов).

·  Поле Мемо – специальный тип данных для хранения больших объемов текста (досимволов). Физически текст хранится не в поле, а в другом месте базы данных. В поле находится только указатель на него, но для пользователя такое разделение заметно не всегда.

·  Числовой – тип данных для хранения действительных чисел.

·  Дата/время – тип данных для хранения календарных дат и текущего времени.

·  Денежный – тип данных для хранения денежных сумм (особенность, связанная с правилами округления).

·  Счетчик – специальный тип данных для уникальных (не повторяющихся в поле) натуральных чисел с автоматическим наращиванием. Естественное использование – для порядковой нумерации записей.

·  Логический – тип для хранения логических данных, которые могут принимать только два значения, например Да или Нет.

·  Поле объекта OLE – специальный тип данных, предназначенный для хранения объектов OLE, например мультимедийных.

·  Гиперссылка – специальное поле для хранения адресов URL Web – объектов Интернета. При щелчке на ссылке автоматически происходит запуск броузера и воспроизведение объекта в его окне.

·  Мастер подстановок – это объект, настройкой которого можно автоматизировать ввод данных в поле так, чтобы не вводить их вручную, а выбирать из раскрывающегося списка.

3.2. Проектирование баз данных

Обычно с базами данных работают две категории исполнителей: проектировщики и пользователи. Последние получают исходную базу данных от проектировщиков и занимаются ее наполнением и обслуживанием. Основные типы объектов баз данных рассмотрим на примере СУБД Microsoft Access.

Таблицы. Это основные объекты любой базы данных. Во-первых, в таблицах хранятся все данные, имеющиеся в базе, а во-вторых, таблицы хранят и структуру базы (поля, их типы и свойства).

Запросы. Эти объекты служат для извлечения данных из таблиц и предоставления их пользователю в удобном виде. С помощью запросов выполняют такие операции как отбор данных, их сортировка и фильтрация. Особенность запросов состоит в том, что они черпают данные из базовых таблиц и создают на их основе временную результирующую таблицу.

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

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

Страницы. Это страницы доступа к данным (Access 2000). Физически это особый объект, выполненный в коде HTML, размещенный на Web-странице и передаваемый клиенту вместе с ней.

Макросы и модули. Эти категории объектов предназначены как для автоматизации повторяющихся операций при работе с СУБД, так и для создания новых функций путем программирования. В СУБД Microsoft Access макросы состоят из последовательности внутренних команд СУБД и являются одним из средств автоматизации работы с базой. Модули создаются средствами внешнего языка программирования, в данном случае языка Visual Basic for Applications.

При проектировании баз данных разрабатывают техническое задание, в процессе подготовки которого составляют:

·  список исходных данных, с которыми работает заказчик;

·  список выходных данных, которые необходимы заказчику для управления структурой своего предприятия;

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

Далее приступают к созданию структуры базы данных т. е. структуры ее основных таблиц.

1.  Составляют генеральный список полей.

2.  Определяют наиболее подходящий тип данных для каждого поля.

3.  Распределяют поля генерального списка по базовым таблицам.

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

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

6.  Приступают к непосредственному созданию базы данных.

3.3. Работа с СУБД Microsoft Access

Работа с таблицами

Создание таблиц. Работа с любыми объектами начинается с окна База данных (рис. 3.1). На левой панели данного окна сосредоточены элементы управления для вызова всех семи типов объектов программы.

Создание таблиц начинается с выбора элемента управления Таблицы. На правой панели представлен список таблиц, уже имеющихся в составе базы, и приведены элементы управления для создания новой таблицы. Чтобы создать таблицу вручную, следует использовать значок Создание таблицы в режиме конструктора.

Рис. 3.1. Окно База данных

Окно Конструктора таблиц (рис. 3.2) является графическим бланком для создания и редактирования структуры таблиц. В первый столбец вводят имена полей. Если свойство Подпись для поля не задано, то Имя поля станет одновременно и именем столбца будущей таблицы. Тип для каждого поля выбирают из раскрывающего списка, открываемого кнопкой выбора типа данных. Она отображается только после щелчка на поле бланка. При создании таблицы целесообразно задать ключевое поле. Для этого достаточно щелкнуть на его имени правой кнопкой мыши и в открывшемся контекстном меню выбрать пункт Ключевое поле.

Рис. 3.2. Проектирование структуры таблицы

Созданную таблицу открывают в окне База данных двойным щелчком на ее значке. Заполнение таблицы данными производится в обычном порядке. Курсор ввода устанавливается в нужную ячейку указателем мыши. Переход к следующей ячейке можно выполнить клавишей ТАБ. Переход к очередной записи проиходит после заполнения последней ячейки (рис. 3.3).

Рис. 3.3. Пример новой таблицы

Если возникнет необходимость изменить структуру таблицы (состав полей и их свойства), таблицу надо открыть в режиме Конструктора. Для этого ее следует выделить в окне База данных и щелкнуть на кнопке Конструктор.

Создание межтабличных связей

Создание реляционных отношений между таблицами выполняется в специальном окне Схема данных (рис. 3.4) с помощью мыши. Окно Схема данных открывают кнопкой на панели инструментов или командой Сервис – Схема данных. Образовавшаяся межтабличная связь отображается в окне Схема данных в виде линии, соединяющей два поля разных таблиц. При этом одна из таблиц считается главной, а другая – связанной. Главная – это та таблица, которая участвует в связи своим ключевым полем. Связь между таблицами имеет два основных назначения. Первое – обеспечение целостности данных, а второе – автоматизация задач обслуживания базы.

Рис. 3.4. Установка межтабличных связей

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

Работа с запросами

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

– просмотреть отобранные записи в табличном представлении,

– сохранить запрос для дальнейшего использования,

– на базе запроса создать форму или отчет,

– применить запрос для сортировки данных,

– востребовать информацию из нескольких таблиц вашей базы данных,

– на базе имеющегося запроса создать новый.

В учебных целях запросы лучше готовить вручную, с помощью Конструктора. Как и в случае с таблицами, для этого есть специальный значок в окне База данных. Он называется Создание запроса в режиме конструктора и открывает специальный бланк, называемый бланком запроса по образцу (рис. 3.5). Он состоит из двух областей. В верхней отображается структура таблиц, к которым запрос адресован, а нижняя область разбита на столбцы – по одному столбцу на каждое поле будущей результирующей таблицы.

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

Рис. 3.5. Бланк запроса по образцу

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

·  запросы с параметром (критерий отбора может задать сам пользователь, введя нужный параметр при вызове запроса);

·  итоговые запросы (производят математические вычисления по заданному полю и выдают результат);

·  запросы на изменение (позволяют автоматизировать заполнение полей таблиц);

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