Одновременная работа с несколькими книгами

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

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

 

Лабораторная работа № 1.

Теперь у Вас уже есть необходимый минимум знаний для работы с электронными таблицами. Создадим таблицу, которая показана на рис. 2.

Рис. 2.

Закройте старую книгу и создайте новую кнопкой Создать.

Сначала в таблицу нужно ввести строки с наименованием дисциплин в столбец А. Они расположены в строках 3-10. Для того, чтобы увеличить ширину столбца установите курсор на серый заголовок между ячейками с надписями А и В так, чтобы он принял форму креста с разнонаправленными стрелками (на рисунке он находится в заголовке между столбцами D и E). Нажмите левую клавишу мыши и плавно сдвиньте границу вправо так, чтобы надписи полностью поместились в ячейки. Далее занесите в ячейки имеющиеся в таблице числа.

Другой способ подбора ширины столбца следующий. Выделите нужные ячейки и выполните команду Формат – Столбец – Автоподбор ширины. Теперь ширина будет соответствовать ширине содержимого ячеек. Это другой и вполне совершенный способ выравнивания ширины столбцов.

Введите слово «ИТОГО» в строку 11, как показано на рис. 2, и отформатируйте его кнопкой По правому краю  панели Форматирование.

Введите надписи к столбцам, которые расположены в строке 2. Выделите ячейки этой строки, щелкните на кнопке По центру  панели Форматирование, затем на кнопке Курсив (на ней надписана буква К).

Чтобы надписи были отчеркнуты линиями снизу и сверху как на рис. 2, не убирая выделения щелкните на указателе справа от кнопки Границы  панели Форматирование, затем выберите в предложенном наборе подходящий элемент разметки (границы сверху и снизу) и щелкните по нему. Аналогично разметьте все остальные части таблицы, которые выделены на рис. 2 линиями границ.

Вновь вернемся к строке 2 для того, чтобы увеличить высоту ячеек. Выделите ячейки строки. Выполните команду Формат-Строка-Высота и установите во всплывшем окошке высоту 19,75. Теперь текст нужно выровнять по высоте. Не снимая выделения ячеек выполните команду Формат-Ячейки. В ответ всплывет окно Формат ячеек, которое показано на рис. 3. Установите в нем свойство Выравнивание – по вертикали в значение по центру и нажмите кнопку ОК.

Рис. 3.

Теперь займемся строкой 1, в которой находится заголовок таблицы. Выделите ее ячейки и выполните команду Формат - Ячейки. Вновь появится окно рис. 3. В нем на закладке Выравнивание щелчком установите галочку в окошко объединение ячеек и щелкните на кнопке ОК. Теперь введите в объединенную ячейку текст заголовка. Отформатируйте его по центру. Установите размер символов в значение 14.

После ввода данных в ячейки ширину столбцов можно еще раз отрегулировать. Для этого нужно выделить ячейки таблицы и выполнить команду Формат-Столбец-Автоподбор ширины. Предварительная часть работ закончена. Таблица должна иметь вид рис. 2.

Автоматизация расчетов

Приступим к автоматизации расчетов. Суть расчетов заключается в следующем:

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

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

· найти общее количество часов для всех дисциплин по всем видам занятий (общая сумма).

Сначала найдем суммарное количество часов для первой дисциплины (физика). Плавно проведите курсором от ячейки B3 до ячейки G3 и щелкните по кнопке Автосумма  панели Стандартная. В ячейке G3 появится сумма чисел из ячеек B3:F3. Щелкните дважды по ячейке G3. Там содержится формула =СУММ(B3:F3), т. е. формула, вызывающая функцию СУММ суммирования нужных нам чисел, которую Excel подставил после нажатия кнопки.

Можно было бы аналогичным способом выполнить расчет по другим дисциплинам. Но мы применим другой, более эффективный прием. Щелкните на ячейке G3. Смещайте курсор к правому нижнему краю ячейки. Как только он примет форму маленького черного крестика , который называется маркером автозаполнения, нажмите левую клавишу мыши, протяните курсор по столбцу от ячейки G3 до ячейки G10 и отпустите клавишу. Теперь столбец G в строках 3-10 заполнился нужными нам суммами. Измените в любой ячейке диапазона B3:F10 число, нажмите Enter или щелкните по любой другой ячейке. Программа немедленно пересчитает суммы столбца G в соответствии с новыми данными.

Теперь заполним строку 11, которая должна содержать суммы по видам занятий. Сначала заполним ячейку B11. Она соответствует количеству часов по всем дисциплинам для вида Лекции. Выделите ячейки B3:B10, протянув по ним курсор с нажатой левой клавишей, затем щелкните на кнопке Автосумма . В ячейке B11 появится сумма чисел, размещенных в выделенных ячейках столбца, – вновь Excel “догадался”, что именно в эту ячейку нужно вставить нужную нам сумму.

Заполним всю строку 11. Щелкните по ячейке B11. Найдите маркер автозаполнения (черный крестик) в правом нижнем углу и протяните курсор до ячейки G10. Все выделенные ячейки заполнились требуемыми суммами.

Наконец, протяните курсор по ячейкам G3:G11 и нажмите кнопку Автосумма. Теперь правый столбец и нижняя строка заполнены числами.

Щелкните по ячейке G11, затем по кнопке с надписью Ж на панели Форматирование. Итоговое число 725 выделится полужирным начертанием.

Окончательный вид рассчитанной таблицы показан на рис. 4.

Измените какие-нибудь числа в прямоугольнике B3:F10. Обратите внимание, что после нажатия клавиши Enter или щелчка по другой ячейке суммы в строке 11 и столбце G тут же автоматически пересчитываются.

Рис. 4.

Электронная таблица имеет прямую аналогию с таблицей баз данных. Роль записей в ней играют строки (в нашем случае строки 3-10), роль полей – литеры столбцов или наименования столбцов (в строке 2). Такие таблицы можно упорядочивать, осуществлять отбор нужных данных по разнообразным запросам и многое другое.

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

Рис. 5.

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

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

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

Ориентация текста в ячейках

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

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

· Для увеличения размеров надписей выполните команду Формат - Строка-Автоподбор высоты. Теперь надписи имею прежний размер и вертикальную ориентацию.

· Теперь ширину столбцов можно уменьшить. Для этого выделите всю таблицу и выполните команду Формат-Столбец-Автоподбор ширины. Таблица уменьшилась, но теперь не вмещается ее заголовок. Щелкните по верхней ячейке с надписью-заголовком, назначьте размер 12, начертание – жирное.

Печать таблицы

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

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

Работа с большими таблицами. Получение данных из внешних источников.

В реальной практике работы в Excel обычно приходится оперировать большим числом данных, когда таблица может иметь десятки столбцов, а также десятки, сотни, а иногда и тысячи строк. Иногда данные хранятся в другом месте, и их требуется перенести для обработки в Excel. Эти данные могут находиться не только в другом файле, но и на другом компьютере, подключенном к локальной сети или даже в сети INTERNET. Кроме того, способ организации этих данных может отличаться от таблиц Excel. Тем не менее, в Excel предусмотрена возможность сформировать запрос и получить нужные данные из любых доступных источников. Это осуществляет специальная надстройка в MS Office, которая носит название MS Query.

Лабораторная работа № 2.

Рассмотрим типичную потребность торгового предприятия в учете продажи товаров. Запустите Excel, создайте новую книгу. На ее первом листе нужно будет поместить данные, содержащиеся в файле базы данных формата MS Access. В качестве учебного примера используем файл Tovar2000.mdb. Эта база данных содержит следующую информацию: вид товара, наименование товара, дата заказа, количество, цена за единицу, заказчик и номер варианта. Вся информация помещается в четыре связанных между собой таблицы. При обращении к базе данных необходимо выбрать только те записи, которые соответствуют номеру варианта, равному N. Пусть N=0.

Рис. 6.

Вызовем команду Данные-Внешние данные-Создать запрос. Появится окно, представленное на рис. 6. Следует выбрать нужный нам источник (База данных MS Access) и щелкнуть кнопку OK. Теперь для подключения к источнику данных необходимо найти и указать упомянутый файл Tovar2000.mdb.

В появившемся окне (см. рис. 7) MS Query помогает нам сформировать запрос. В правую часть остается перенести те поля из разных таблиц, которые нам нужны. Это легко сделать, если раскрыть каждую (список слева) из четырех таблиц источника, щелкая значок с плюсом около каждой таблицы. Затем выделяем поле и щелкаем кнопку  переноса в правую часть. Можно просто выполнять двойной щелчок на имени поля. Таким образом в запрос должны быть перенесены шесть полей из базы данных (см. рис. 7).

Рис. 7.

Рис. 8.

Далее (щелкните такую кнопку) окончательно сформируем запрос (нам нужны не все записи, а только те, что соответствуют варианту № 0. Поле «Вариант» есть в таблице «Заказы»). Сейчас мы находимся в среде MS Query. В верхней части окна представлены таблицы источника данных, которые были выбраны на предыдущем шаге. Они связаны по ключевым полям (см. рис. 8). Если по каким-либо причинам связь не установилась, не огорчайтесь. Вы с легкостью можете установить нужные связи вручную. Мышью достаточно перетащить поле одной таблицы к соответствующему полю другой таблицы, и между таблицами протянется ниточка. Эта ниточка и указывает на связь таблиц.

Перейдем к отбору (фильтру) данных. Вызов команды Условия - Добавить условия выводит окно, показанное на рисунке 9. Здесь нужно выбрать из списка Поле «Номер варианта» из таблицы «Заказы», выбрать Оператор «равно», а Значение поставить равным нулю.

Рис. 9.

Чтобы не вводить значение с клавиатуры, можно получить список всех значений, содержащихся в выбранном поле (кнопка Значения…). Щелкните кнопку Добавить, а затем Закрыть. Мы выполнили свою задачу, хотя MS Query предоставляет более богатые возможности. Вполне возможно добавление нескольких условий, соединенных операциями И или ИЛИ (обратите внимание на соответствующие селекторные кнопки). Выбор групповой операции позволит найти сумму или среднее для числовых данных.

Введенное нами условие появилось в рабочем окне MS Query. В нижней части окна отражаются результаты запроса. Сравните полученное на вашем компьютере с тем, что было ранее (как на рис. 8).

Прежде чем сделать последний шаг и отправить данные в рабочую книгу, загляните в другой режим просмотра сформированного запроса. Выберите команду Вид-Запрос SQL …. Теперь можно просмотреть и пролистать текст сделанного нами запроса на специальном и достаточно стандартизированном языке. Этот язык, называемый «SQL» служит для управления базами данных с форматами очень широкого спектра. Например, наш запрос можно было написать, используя этот язык, не прибегая к помощи мастера запросов. Впрочем, как видите, и без этих знаний в данном случае можно решить задачу. Поэтому не будем ничего исправлять в тексте и щелкнем кнопку Отмена.

Теперь мы опять в рабочем окне MS Query. Выберем команду Файл-Вернуть данные в MS Excel. На промежуточный вопрос «куда помещать полученные данные», укажем ячейку A1 первого листа нашей рабочей книги. Результат представлен на рис. 10. В первой строке размещаются заголовки столбцов. Excel активизирует панель Внешние данные. Одним щелчком мыши или выбором команды Данные - Обновить данные можно получить свежую информацию по сформированному запросу. Это очень важно, ведь источник может постоянно обновляться. В случае необходимости можно запрос корректировать.

Рис. 10.

Предположим все же, что данные больше обновляться не будут, и поработаем с рабочим листом. Добавим два столбца: «год» и «цена товара».