Шрифтовое оформление текста

Используемый в ячейке шрифт изменяют посредством списка панели "форматирование".

Размер шрифта изменяют с помощью списка , а его тип - кнопкой на полужирный, а кнопкой - на наклонный.

Эту же задачу можно решить командой Формат > Ячейки > Шрифт.

Изменим шрифт заголовка таблицы:

 активизируйте ячейку A1, где хранится текст "Таблицы";

 для смены шрифта введите команду Формат > Ячейки > Шрифт;

 в появившемся диалоговом окне задайте шрифт Courier New Cyr, размер шрифта 14 и начертание - полужирным.

Отформатируйте:

 заголовки "основная" и "дополнительная" шрифтом Courier New Cyr, полужирный, размер 12. Используйте для этого кнопки панели инструментов "Форматирование";

 шапки таблиц шрифтом Courier New Cyr, курсив, размер 12. Используйте для этого команды меню Формат > Ячейки > Шрифт.

Любым способом:

 подгоните ширину столбцов так, чтобы текст помещался полностью;

 произведите выравнивание надписей шапок по центру.

Окончательно начало таблицы будет иметь вид

 

Обрамление

Для выделения группы данных их обычно обрамляют, используя кнопки панели "Форматирование" или команду Формат > Ячейки > Рамка.

Задайте рамки для шапок основной и дополнительной таблиц, используя список панели инструментов "Форматирование".

 

Фон

Содержимое ячейки или блока можно выделить фоном. Для этого используют список панели "Форматирование" или команду Формат > Ячейки > Вид.

Задайте фон заполнения шапок таблиц - бирюзовый.

Окончательно начало таблицы будет иметь вид

Функции рабочего листа

Функции являются инструментами для вычисления значений, принятия решений и выполнения действий.

В общем виде запись функции имеет вид f(x; y;...; z). Здесь x, у,..., z- аргументы, а f - функция. Например, хранимая в некоторой ячейке формула =sin(F7) означает определение значения функции "sin" для аргумента, значение которого хранится в ячейке F7.

Аргументами функций могут быть числа, тексты, логические значения, значения ошибки, ссылки и массивы.

Рассмотрим некоторые типы функций.

 

Арифметические функции

Подсчитаем в ячейке E25 сумму вычисленных значений функции у:

Сделайте текущей ячейку E25.

Введите в нее формулу =СУММ(E4:E24).

Этот же результат можно достичь, используя кнопку "Автосумма" .

Запишите в ячейку D25 поясняющий текст "Сумма у=" и, используя кнопку панели инструментов "Форматирование", прижмите его к правому краю.

Введите в ячейку E26 формулу =E25/21, чтобы подсчитать среднее арифметическое значений функции у.

Здесь 21 – это число значений функции.

Занесите в ячейку D26 поясняющий текст "Ср. арифм. y=".

Пользуясь "Мастером функций",найдем минимальное значение функции:

сделайте текущей ячейку Е27;

запустите "Мастер функций", щелкнув на кнопке ;

в появившемся диалоговом окне сделайте текущей функцию "МИН" и щелкните на кнопке OK;

 

Рисунок 4.1- Выбор функции

в следующем диалоговом окне "панель формул" задайте аргументы функции (диапазон поиска минимума - E4:E24) и щелкните на кнопке OK;

 

Рисунок 4.2 - Задание аргументов функции

 

занесите в ячейку D27 поясняющий текст "min=".

Подсчитайте, аналогично, в ячейке E28 максимальное значение вычисленных значений функции у.

Оформим блок ячеек D25:E28: Задайте рамку блока D25:E28.

Заполните этот блок тем же фоном, что и шапку таблицы.

Поясняющие подписи в ячейках D25:D28 оформите полужирным шрифтом Arial Cyr с выравниванием вправо. Окончательно рассчитанные значения будут иметь вид

 

Текстовые функции

Перейдите на новый лист электронной таблицы.

Введите в ячейку А1 текстовую строку "День ", в ячейку А2 - число 2, а в ячейку А3 - текст: "-й".

Переместим указатель ячейки в В1 и объединим три фразы с помощью формулы

=А1&TЕКСТ(А2;0)&А3

При вводе функции TЕКСТ(А2;0) пользуйтесь "Мастером функций".

Результатом объединения будет текст "День 2-й".

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

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

 

Логические функции

Перейдите на новый лист электронной таблицы.

Введите в ячейку А1 число 12, а в свободную ячейку - формулу

=ЕСЛИ(И(A1>0;A1<3);"Прибыль";"Убыток")

Результат, вычисленный по формуле, - текстовая строка "Убыток".

Введите в ячейку А1 число 2. Текстовая строка "Убыток" сменится на строку "Прибыль".

 

Форматы чисел

Число в ячейке можно представить в различных форматах. Например, число 100 будет выглядеть как: 100,00 грн. - в денежном формате[1]⁾; 10000% - в процентном выражении; 1,00Е+2 - в экспоненциальный форме.

Для форматирования числа можно воспользоваться кнопками панели "Форматирование" или командой Формат > Ячейки.

Для выполнения команды необходимо:

 выделить ячейку или блок, который нужно форматировать;

 выбрать команду Формат > Ячейки > Число;

 выбрать желаемый формат числа в диалоговом окне.

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

Если ячейка отображается в виде символов ####, то это означает, что столбец недостаточно широк для отображения числа целиком в установленном формате.

Скопируйте значения функции у из столбца E в столбцы J, K и L. Для этого:

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

щелкните на кнопке ; укажите левую верхнюю ячейку области вставки;

в меню Правка выберите команду Специальная вставка;

установите переключатель Вставить в положение Значения и щелкните мышью на кнопке OK. Представьте числа в следующих форматах:

"Числовой" с двумя значащими цифрами после запятой - в столбце J;

"Экспоненциальный" - в столбце K;

"Денежный" - в столбце L. При этом значения должны отображаться с точностью до копеек.

Оформите блок J2:L4 в стиле основной и дополнительной таблиц: заголовок, обрамление, заполнение и шрифт.

Окончательно измененная часть таблицы будет иметь вид

 

Сохраните файл под старым именем work2_2.xls.

Диаграммы

Одним из достоинств Excel является способность превращать абстрактные ряды и столбцы чисел в привлекательные информативные графики и диаграммы.

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

 

Построение

Представим графически данные блока G15:М17 файла work1.xls.

Откройте файл work1.xls.

Выведите на экран часть листа, как показано ниже.

	Выделите мышью блок F14:L15, содержащий данные, которые должны быть отражены на диаграмме. Щелкните на кнопке "Мастер диаграмм" панели инструментов "Стандартная". Excel выведет первое диалоговое окно "Мастер диаграмм (шаг 1 из 4)". В левом окне "Тип" выбрана "Гистограмма". Щелкните в знак согласия на кнопке Далее диалогового окна "Мастер диаграмм". Следующее диалоговое окно - это догадки Excel о том, как использовать выделенные вами данные. В данном случае он считает, что: данные находятся в строках; размещаемые вдоль оси Х подписи указаны вами в первой строке -блоке F14:L14. Чтобы в этом убедиться перейдите на вкладку "Ряд";
	Поскольку "Мастер диаграмм" правильно определил, как понимать выделенные данные, то менять ничего не надо. Щелкните на кнопке Далее. Если бы у нас было две строки данных и соответственно две диаграммы, то необходимо было бы различать эти диаграммы - дать им названия (легенды). У нас одна диаграмма, поэтому мы не будем придумывать для нее легенду и снимем щелчком мыши значок в окошке "Добавить легенду". Перейдите на закладку "Заголовки" и введите в окно:
"Название диаграммы" текст "Производственные показатели"; "Ось Y (значений)" подпись "(тыс. штук)". Щелкните на кнопке Далее. В последнем окне "Мастера диаграмм" щелкните на кнопке Готово в знак согласия с тем, что диаграмма будет помещена на тот же лист, где находятся данные. Вы получили диаграмму, внедренную в рабочий лист. Если она вам не нравится, то ее можно отредактировать (изменить).

Изменение размеров

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

Сделайте одиночный щелчок по диаграмме. На ее рамке появятся маркеры выделения - маленькие черные квадратики в углах и на серединах сторон рамки.

Установите указатель мыши на маркер справа (указатель мыши при этом изменяет свою форму на двунаправленную стрелку) и растяните ее до столбца О.

"Зацепившись" курсором мыши за рамку, сдвиньте диаграмму на середину столбца F.

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

 

Графики

Для построения графиков функций используют тип диаграммы "График". Этот тип диаграммы требует два ряда значений: X - значения должны быть расположены в левом столбце, а Y - значения - в правом[1]. На одной диаграмме можно построить несколько графиков функций. Эту возможность используют для проведения сравнительного анализа значений Y при одних и тех же значениях X, а также для графического решения систем уравнений с двумя неизвестными.

Для построения графика используем данные файла, созданного ранее. На одной диаграмме построим три совмещенных графика: y₁ = х²-1, y₂= х²+1 и y = 10(y_l/y₂).

 

Откройте файл work2_2.xls. Переместите вспомогательную таблицу под основную, начиная с ячейки A29. Выделите мышью блок B3:E24, содержащий данные, которые должны быть отражены на диаграмме. Щелкните на кнопке "Мастер диаграмм". Укажите тип "Точечная", выберите "гладкую" диаграмму и щелкните мышью на кнопке Далее
	Щелкните мышью на кнопке Далее в следующем окне, так как Excel определяет здесь все правильно. Добавьте название диаграммы "Совмещенные графики" и укажите название по оси X – "X", а по - оси Y – "Y". Перейдите к следующему окну и завершите создание диаграммы типа "График". Для того чтобы получить график, представленный рядом, его необходимо отредактировать.

Рис. Совмещенные графики

 

Подберите размеры области диаграммы и графика:

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

Выберите заливку области диаграммы:

 щелкните правой клавишей мышьи вне графика в пределах области диаграммы и выберите пункт меню Формат области диаграммы;

 перейдите на вкладку "Вид" и щелкните мышью на кнопке Способы заливки;

 выберите заливку "Градиентная".

Отредактируйте название оси Y:

 щелкните на названии оси Y и "перетащите" его на традиционное место (см. рис. "совмещенные графики");

 щелкните правой кнопкой мыши на названии оси Y и сориентируйте ее. Для этого в контекстно-зависимом меню выберите Формат названия оси > Выравнивание > Ориентация > 0 градусов;

Аналогично отредактируйте названия оси Х.

Переместите легенду в поле графика.

Сохраните файл под новым именем work4_4.xls.

 

Работа со списками

Список - это совокупность строк листа, содержащих однотипные данные.

Список, имеющий заголовки столбцов, называют базой данных. Например, область таблицы A2:F7, приведенная на следующем рисунке.

Рисунок 8.1 - База данных отдела кадров

 

Столбцы А, В, С, D, E и F этой таблицы называют полями, а строки с 3-й по 7-ю - записями. Область A2:F2 содержит имена полей.

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

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

 

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

Команды Данные > Фильтр позволяют выделять (фильтровать) записи, удовлетворяющие заданным условиям отбора. Например, список покупателей можно отфильтровать, так чтобы на экране увидеть только тех, кто совершил покупки больше чем на 500 грн.

Фильтрация возможна через Автофильтр и Расширенный фильтр.

 

Автофильтр

При использовании Автофильтра достаточно переместить указатель ячейки в область, содержащую базу данных. Затем необходимо активизировать команды Данные > Фильтр > Автофильтр. На именах полей появятся кнопки списка. Щелкая мышью на кнопках, можно задавать критерии фильтрации. Пункт списка Все отключает фильтрацию, а пункт Условие вызывает диалоговое окно, в котором можно установить параметры фильтрации (см. рис.8.2). Для одного поля могут быть заданы два условия одновременно, связанные либо логическим ИЛИ, либо И.

Рисунок 8.2 - Описание пользовательского фильтра

 

Проиллюстрируем использование Автофильтра на примере поиска фамилий сотрудников, начинающихся на букву "С" или букву "Ю", стаж работы которых менее 15 лет.

Введите данные, как показано на рис. 8.1.

На что хотелось бы обратить внимание:

 на рис. 8.1 колонка "С" скрыта. Данные в ней хранят в формате "Дата". Для того чтобы скрыть её достаточно выделить и выбрать из контекстного меню пункт Скрыть;

 стаж вычисляем по формуле, на примере ячейки D3, =ГОД(СЕГОДНЯ())-ГОД(C3).

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

Активизируйте команды меню Данные > Фильтр > Автофильтр.

Щелкните по появившейся в поле "Ф.И.О." кнопке списка и выберите пункт Условие.

В диалоговом окне задайте критерии согласно рис. 8.2 и щелкните на кнопке ОК.

Результат отбора сотрудников, имена которых начинаются на букву "С" или букву "Ю" приведен ниже.

Щелкните на кнопке списка в поле "Стаж" и выберите пункт Условие.

В диалоговом окне задайте критерий: <15 и щелкните на кнопке ОК.

Результат отбора сотрудников, имена которых начинаются на букву "С" или букву "Ю", стаж работы которых менее 15 лет приведен ниже.

С помощью автофильтра легко просматривать список уникальных элементов столбцов.

Для возврата исходного состояния списка выполните команды Данные > Фильтр > Отобразить все.

Щелкните на появившейся в поле "Сем. полож." кнопке списка и выберите пункт "холост".

Результат фильтрации приведен ниже.

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

В Расширенном фильтре условия отбора описывают на листе книги. Это удобно, так как вам нет необходимости их помнить, а, кроме того, их можно редактировать в дальнейшем.

При использовании Расширенного фильтра необходимо сначала задать две области:

 фрагмента базы, данные которой будем фильтровать;

 условия отбора - фрагмент листа, где описаны критерии фильтрации.

Для выполнения фильтрации используют команды меню Данные > Фильтр > Расширенный фильтр.

Проиллюстрируем использование Расширенного фильтра на примере поиска холостых сотрудников по специальности "слесарь" с окладом менее 170 грн.

Создадим условия отбора:

 скопируйте в 11-ю строку заголовки фильтруемых столбцов: "Специальность", "Оклад" и "Сем. полож.";

 запишите под ними условия отбора [2];

 озаглавьте данную часть листа, как показано ниже.

Приступим к фильтрации:

 поместите указатель ячейки в область базы данных;

 скомандуйте Данные > Фильтр > Расширенный фильтр;

 в диалоговом окне "Расширенный фильтр" в поле "Обработка" активизируйте переключатель "Фильтровать список на месте";

 ведите в поле "Исходный диапазон" ссылку на базу данных, включая заголовки;

 ведите в поле "Диапазон условий" ссылку на диапазон A11:D12 и щелкните мышью на кнопке OK.

Результат фильтрации приведен ниже.

Для возврата исходного состояния выполните команды Данные > Фильтр >Отобразить все.

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

Для фильтрации записей удовлетворяющих:

 одновременно нескольким условиям вводите их в одну строку;

 хотя бы одному из условий - вводите их в разные строки.

 

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

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

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