Форматирование числовых данных

Выделите вторую строку (в ней расположены наименования данных) курсивом и установите верхнюю и нижнюю границы для всех ячеек этой строки в виде сплошной линии.

В таблице имеется два вида числовых данных – даты и числа. Чтобы все даты были представлены в нужном Вам виде, щелкните на ячейке H3, куда помещена дата первого заказа и выделите диапазон вниз до нижнего края таблицы. Выполните команду Формат-Ячейки. В открывшемся окне (рис. 3.), которое ранее использовалось для выравнивания текста в ячейках на закладке Выравнивание, теперь на его другой закладке Число выберите формат Дата, а затем в списке справа – желаемый формат даты. Образец даты помещается над выбранным форматом. После выбора нажмите кнопку ОК.

Для задания формата чисел используйте тот же способ, но вместо формата Дата выберите формат Числовой и установите два знака в дробную часть табличных чисел. Для денежных чисел можно назначить этот же формат или выбрать формат Денежный (какой из них выбрать решите сами). Отформатируйте столбцы F и D в денежном формате.

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

Закрепление областей и разделение окна

Для того чтобы одновременно в окне были видны, например, первый и последний столбцы, можно использовать закрепление областей. Щелкните на ячейке C3 и выполните команду Окно-Закрепить области. Слева и выше от ячейки появились линии, которые разделили таблицу на 4 части.

Подвигайте бегунками горизонтальной и вертикальной полос прокрутки. Теперь верхняя и левая от ячейки D6 области неподвижны, а нижняя и правая подвижны и можно добиться того, что в окне будут видны первый и последний столбцы одновременно. Снять режим можно командой Окно-Снять закрепление областей.

Другой способ состоит в разделении окна на 4 окна со своими полосами прокрутки. Для этого нужно выполнить команду Окно-Разделить. Снять разделение можно командой Окно-Снять разделение.

Лабораторная работа № 2 завершена. Сохраните таблицу. Она еще потребуется для последующих работ.

 

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

Если таблица содержит достаточно большое количество данных, то часто возникает потребность отобрать и систематизировать отдельные данные по определенному признаку или набору признаков. Например, может потребоваться узнать сколько в созданной ранее таблице зафиксировано заказанных товаров АО «Природа» за май месяц и на какую сумму, или мы захотим систематизировать товары по каждому заказчику в отдельности с простановкой сумм по каждому из них и общей суммой продаж. Таких потребностей может накопиться множество, и хотелось бы чтобы результат можно было получить быстро и в удобном виде. Excel имеет такие возможности.

Откройте созданную на прошлой лабораторной работе книгу.

Простой отбор данных

Сначала решим простую задачу. Показать в таблице только те заказы, которые относятся к ООО "Интер-Зоо".

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

Щелкните по ярлычку в ячейке Заказчик. Распахнется список, показанный на рис. 11.

Рис. 11.

Этот список содержит наименования всех введенных нами заказчиков и еще несколько строк, поэтому позволяет сделать выбор заказчиков по определенному правилу. Строки списка говорят сами за себя. Несколько непонятна строка Условие, ее мы разберем отдельно.

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

Щелкните в списке на строке ООО "Интер-Зоо". Будет произведена фильтрация покупателей по полю Заказчик, его значению ООО "Интер-Зоо". Результат фильтрации показан на рис. 12. В таблице показаны только те заказы, которые сделаны ООО "Интер-Зоо".

Рис.12.

Сложный отбор данных

Предположим, потребовалось отфильтровать из таблицы заказчиков: фирму «Челюсти» и центр «Престиж», которые заказали товары по цене от 100 до 400 руб. Щелкните по ярлычку ячейки Заказчик, затем в списке – по строке Условие (рис. 11). В окне Пользовательский автофильтр выберите условия так, как показано на рис 13. Установите опцию «ИЛИ». Это означает, что следует отбирать все строки, относящиеся к обоим заказчикам. Закройте окно рис. 13 кнопкой ОК.

Рис. 13

Щелкните в таблице по ярлычку в ячейке Цена, в списке щелкните по строке Условие. Установите в окне Пользовательский автофильтр параметры отбора «больше или равно 100» и «меньше или равно 400». Обратите внимание, что теперь использована опция «И». Это значит, что нужны цены, которые одновременно больше или равны 100 и меньше или равны 400. Если бы мы установили опцию «ИЛИ», т.е. потребовали раздельного выполнения условий, то в фильтрованный список попали бы, например, товары с ценами, которые меньше 100, т. к. они удовлетворяют условию меньшие или равно 400. Это привело бы к ошибке фильтрации. Просмотрите результат и попробуйте какие-либо другие условия.

Итоги по группам

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

Выделите все строки, кроме первой. Отсортируйте диапазон по полю Заказчик командой Данные-Сортировка или кнопкой на панели инструментов. Далее выполните команду Данные-Итоги (таблица перед исполнением этой команды должна быть по-прежнему выделена). Появится окно, показанное на рис. 14.

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

Рис. 14.

Теперь таблица кроме введенных строк содержит еще желаемые строки с итогами по каждому заказчику и общий итог по выбранным полям. Смысл появившихся слева обозначений в столбцах с заголовком “1 2 3” прост. Левая длинная “скобка” указывает выбранный диапазон товаров, средние скобки – диапазоны отдельных заказчиков, точки столбца 3 – строки товаров отдельного заказчика. “Минус” на кнопках означает, что диапазон распахнут. Его можно закрыть этой кнопкой. После закрытия на кнопке появляется “плюс”. Поупражняйтесь с кнопками, раскрывая и закрывая диапазоны. Попробуйте также щелкнуть по кнопкам с надписями “1 2 3”. Весьма удобный способ представления итоговых таблиц.

Сводные таблицы

Exсel имеет средство, с помощью которого можно на основании одной таблицы формировать различные сводные таблицы.

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

Прежде всего придется убрать в нашей таблице группировку и промежуточные итоги. Рекомендуем сначала скопировать все данные на другой лист, чтобы не терять все недавно проделанное, а потом сделать следующее. Выделив всю таблицу, кроме первой строки, вызовите команду Данные-Группа и структура-Удалить структуру. Затем удалите строки, содержащие промежуточные итоги. Теперь можно приступать к созданию сводных таблиц.

Снова выделите всю таблицу кроме первой строки и выполните команду Данные-Сводные таблицы. Будет запущен Мастер сводных таблиц, который за несколько шагов создаст нужную Вам сводную таблицу.

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

Не спешите в окне третьего шага щелкать кнопку Готово. Сначала нужно указать, какими данными следует наполнить таблицу. Щелкнув кнопку Макет, Вы попадете в окно, которое показано на рис. 15, и оно является ключевым. Справа показан список всех полей Вашей таблицы. Наведите курсор на кнопку Год и перетащите ее в левый столбец формируемой таблицы, как показано на рис. 15. Точно также перетащите кнопку Вид товара в верхнюю строку, кнопку Заказчик в область Страница, а кнопку Общая цена – в центр таблицы.

Рис. 15.

Дважды щелкните по перемещенной в таблицу кнопке с надписью Общая цена. Откроется окно Вычисление поля сводной таблицы, которое показано на рис. 16. Выберите в нем операцию Сумма (обратите внимание на другие операции списка – пригодится). Нажмите кнопку Формат и назначьте формат Финансовый, 2 знака в дробной части, обозначение – Нет (к суммам не нужно приписывать наименования). Закройте все окна кнопками ОК, и в последнем окне, выбрав опцию новый лист, щелкните кнопку Готово. Сводная таблица создана. Таблица разместится на новом листе с наименованием Лист4, ярлычок которого можно видеть в нижней части окна. Самостоятельно разберите возможности работы с полученной сводной таблицей. В частности, команда Сводная таблица-Формулы-Вычисляемое поле позволила бы обойтись без столбца Общая цена. Также найдите возможность фильтрации данных сводной таблицы по своим значениям.

Рис.16.

 

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

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

Круговая диаграмма

Скопируйте в буфер обмена диапазон сводной таблицы A4:D9. После этого перейдите на вновь созданный лист Диаграммы и вставьте содержимое буфера обмена в диапазон, начинающийся ячейкой A1. Вычислите суммы по столбцам и по строкам (см. рис. 17). Выделите диапазон E2:E6.

Щелкните на кнопке Мастер диаграмм  панели Стандартная. Запустится Мастер диаграмм и будет выведено окно первого шага. Обратите внимание на то, что Excel предлагает довольно большой набор видов диаграмм. Выберите нужный тип (слева) – Круговая и подтип (справа) диаграммы и нажмите кнопку Далее. Теперь будет выведено окно второго шага, в котором надо указать диапазон ячеек. Поскольку диапазон был выделен ранее, то ничего менять не нужно. Щелкните на кнопке Далее. На третьем шаге появится окно установки параметров. Щелкните в нем вкладку Легенда и оставьте галочку опции Добавить легенду. Теперь щелкните вкладку Подписи данных. Здесь можно установить опцию значение или доля. Нажмите кнопку Далее.

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

Готовую диаграмму можно корректировать. Например, можно сменить тип диаграммы. Щелкните по диаграмме для выделения. По контуру выделенной диаграммы появятся квадратики, а на экране – панель Диаграммы. Щелкните на ней по язычку справа от кнопки Тип диаграммы и выберите какой-нибудь другой тип. Диаграмма автоматически изменится. Важно, чтобы диаграмма была наглядна и не избыточна. Если мы иллюстрируем только один и при этом небольшой ряд данных, круговая диаграмма вполне подходит.

Обратите внимание, что при выделении диаграммы и выделении ее отдельных составляющих объекты снабжаются набором черных квадратиков по краям. Выделите круговую часть, и тогда в строке формул обнаружите следующую формулу: =РЯД(;;Диаграммы!$E$2:$E$6;1). Корректируя ее, можно настраивать вид диаграмм. Функция РЯД может иметь 4 параметра, разделенных точками с запятой. Если параметр отсутствует, разделитель все равно требуется.

Первый параметр рассматриваемой функции – заголовок диаграммы. На это место можно ввести в двойных кавычках текст «Распределение заказов по годам». Если этот текст уже содержится в какой-то ячейке листа, можно просто внести ссылку на нее. Проще всего это сделать следующим образом. Активизируйте строку формул клавишей F2 и становите текстовый курсор на место параметра функции. Затем щелкните ячейку, содержащую нужный заголовок, и в строке формул немедленно появится нужная ссылка.

Второй параметр – список наименований в легенде. Легенда – это прямоугольник, в который помещены наименования строк. Установите текстовый курсор на нужное место и проведите мышью по диапазону A2:A6. Нужная ссылка встанет на место. Она будет выглядеть так: Диаграммы!$A$2:$A$6. Пока не стоит углубляться в детали, но ясно, что речь идет о листе Диаграммы и диапазоне A2:A6.

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

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

Рис. 17.

Постройте круговую диаграмму, иллюстрирующую распределение заказов по видам товаров.