Установка границ с помощью диалогового окна Формат ячеек

Оформление границ

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

Для оформления таблиц необходимые инструменты (рис. 2.1) находятся во вкладке Главная.

 

Рис. 2.1. Инструменты для оформления таблиц

 

Установка границ с помощью панели инструментов

Для установки границ следует выделить на листе обрамляемые ячейки, щелкнуть по стрелке во вкладке Шрифт - Границы (рис. 2.2.) и выбрать тип устанавливаемых границ.

 

Рис. 2.2. Установка границ с использованием кнопки "Границы"

 

Некоторые типы границ можно совмещать. Например, сначала выбрать тип все границы, а затем - толстая внешняя граница.

 

Установка границ рисованием

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

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

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

Для окончания установки обрамления необходимо нажать клавишу клавиатуры Esc.

 

Установка границ с помощью диалогового окна Формат ячеек

Для установки границ необходимо выделить обрамляемые ячейки, выполнить команду Шрифт – Границы – Другие границы или щелкнуть по выделенной области правой кнопкой мыши и выполнить команду контекстного меню Формат ячеек, после чего перейти во вкладку Граница диалогового окна Формат ячеек (Рис. 2.3.).

 

 

Рис. 2.3 Установка границ во вкладке "Граница"

диалогового окна "Формат ячеек"

 

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

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

Автостили

Для оформления границ можно использовать готовые стили. Для этого в панели инструментов Стили выбрать вкладку Форматировать как таблицу (Рис. 2.2) и соответственно стиль. В появившемся окне (Рис. 2.4) выбрать диапозон данных таблицы.

Рис.2.4. Окно «Форматирование таблицы»

 

 

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

Рис.2.5. Стиль таблицы Темный 2.

 

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

 

Удаление границ

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

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

Для окончания удаления границ необходимо нажать клавишу клавиатуры Esc.

 

Оформление заголовков

Для оформления заголовков необходимы стандартные инструменты вкладки Главная или диалогового окна Формат ячеек:

1. Панель инструментов Шрифт - Выбор шрифта (по умолчанию, Times New Roman), размера шрифта, стиля (Жирный, Курсив, Подчеркнуты)
2. Панель инструментов Выравнивание текста – Определение положения и направления текста в ячейке (Рис. 2.6), объединения ячеек для формирования нестандартных таблиц (Объединение выделенных ячеек в одну большую ячейку и выравнивание содержимого, Рис. 2.7), и перенос текста (отражение всего содержимого ячейки на нескольких строках, Рис. 2.7).

Рис.2.6. Меню «Ориентация». Рис. 2.7. Меню «Объединить и поместить»

3. Панель инструментов – Редактирование – определяет для ячейки заголовка фильтр по содержимому (Фильтр).

Задание:

Выделить внутренние и внешние границы таблицы, шапку оформить по образцу:

 

1.3. Ссылки:

Большинство ссылок в формулах записываются в относительной форме (рис. 5, ссылки на ячейки Е6, F6, G6 - (столбец)(строка))

Относительными называются ссылки, которые при копировании в составе формулы в другую ячейку (рис. 5, H6 в H7) автоматически изменяются (рис. 5, ссылки в формуле автоматически изменились на Е7, F7, G7)

При копировании формулы с относительной ссылкой (столбец)(строка) на n строк ниже и на m столбцов правее ссылка изменяется на (столец+m)(строка+n)

Относительные ссылки задаются в виде А1.

Рис. 5. Относительная адресация

 

Абсолютными называются ссылки, которые при копировании в составе формулы в другую ячейку не изменяются (Рис.6. I31 в I32, ссылка на значение среднего балла в ячейке E35 не изменилась).

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

Абсолютный адрес ячейки имеет формат $A$1.

Рис. 6. Относительная адресация.

При помощи символа абсолютной адресации $ можно менять способ адресации ячеек. Например, $B11 обозначает, что при копировании формул будет изменяться только адресация строки ячейки, а при обозначении B$11 - только столбца. Такая адресация называется смешанной.

Для переключения режимов адресации можно:

1. Вручную вставить знак доллара в ссылке. Для этого в режиме редактирования формулы (в строке формул ) установить курсор перед указателем столбца или/и строки ссылки и вставить знак $.

2. Использовать функциональную клавишу <F4>. Для этого в режиме редактирования формулы установить курсор непосредственно за ссылкой и нажать клавишу <F4>.

 

Задание:

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

1. Посчитать среднее значение балла по математике, русскому, физике, общий:

Для ячейки под столбцом «Математика» (Е35) вводим в строку формул СРЗНАЧ(), в скобках надо указать диапазон чисел (E5:E32), для этого выделяем ячейки соответствующего столбца, для которого хотим посчитать среднее значение. Копируем содержимое ячейки для столбцов «Русский» (F35), «Физика» (G35), «Средний балл» (H35). Последняя ячейка будет означать «Общий средний балл».

1. Сравнить значение балла по математике абитуриентов с общим средним баллом:

В строку формул для ячейки (I5) соответствующего абитуриента вводим формулу сравнения среднего балла с общим средним баллом:

ЕСЛИ(GH5>=H35, “Выше среднего”, “Ниже среднего”)

Для того чтобы, при копировании для следующих ниже ячеек, ссылка на «Общий средний балл» не сместилась, надо сделать ее абсолютной, для этого, ставим курсор в строке формул после соответствующей ссылки H35, нажимаем F4, получаем:

ЕСЛИ(GH5>=$H$35, “Выше среднего”, “Ниже среднего”). Копируем содержимое ячейки в оставшиеся ячейки (I6:I32).

Упражнение 3. Cортировка, условия в формулах

1. Отсортируем данные в исходной таблице по фамилии учеников.

1. Выделите диапазон. На вкладке Данные выберите Сортировка – настраиваемая сортировка. Введите следующие параметры

Рис 3.1 Параметры сортировки

 

3. Добавим ячейку проходной балл.

4. Добавим к данной таблице столбец Зачисление. Заполним его: если средний балл больше проходного, то абитуриент зачислен, иначе не зачислен. Для этого воспользуемся функцией ЕСЛИ() из группы «логические». Выделим первую ячейку столбца и выберем функцию ЕСЛИ ().

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

Заполните появившееся диалоговое окно(Рис.3.3):

Рис 3.3 Задание аргументов функции ЕСЛИ

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

5. Посчитаем количество зачисленных и незачисленных абитуриентов. Для этого создадим таблицу.

Количество поступивших
Количество не поступивших

 

 

Используем функцию СЧЕТЕСЛИ() из группы «логические». В качестве диапазоне выбираем исходную таблицу данных. В качестве критерия значения ячейки Зачисление: для первого случая – зачислен, для второго – не зачислен (Рис 3.4).

Рис 3.4 Аргументы функции СЧЕТЕСЛИ

Упражнение 4. Работа с листами, сводная таблица

Добавим к нашей таблице «Абитуриент» ещё один столбец - «балл относительно группы», который разобьем ещё на 2 столбца («по физике» и «по математике»). Сбоку от таблицы вычислим средний бал по математике и по физике в группе при помощи формулы из группы «автосумма»- СРЕДЗНАЧ(). После выполнения этих действий должна получиться таблица как на рис. 4.1.

Рис.4.

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

Рис.4.2

 

При выборе функции ЕСЛИ() появится диалоговое окно (рис.4.3) где имеются три поля: Лог_выражение, Значение_если_истина, Значение_если_ложь. В первом поле мы проверяем истинность выражения – «балл по физике первого в таблице абитуриента больше либо равен среднему балу по физике в группе?». Во втором поле мы заносим значение в случае истинного логического выражения (т.е. если балл по физике первого в таблице абитуриента больше либо равен среднему баллу по физике в группе то этот абитуриент имеет балл Выше среднего по физике). В третьем поле мы заносим значение в случае ложного логического выражения (т.е. если балл по физике первого в таблице абитуриента меньше среднего балла по физике в группе то этот абитуриент имеет балл Ниже среднего по физике). После ввода значений как показано на рис.4.3 нажимаем «ОК» и так как у первого абитуриента в таблице балл по физике больше среднего балла в группе(85 >79), то значение первой ячейки столбца «По физике» будет – «Выше среднего»

Рис.4.3

Перед применением этой формулы ко всем абитуриентам сделаем ссылку на ячейку со значением среднего была по физике(L4) – абсолютной, для этого перед именем столбца и номером строки поставим знак «$» как показано на рис.4.4.

Рис.4.4

Аналогичные действия проделываем с баллами по математике.

На этом формирование таблицы закончим. Приступим к формированию сводной таблицы.

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

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

Создание сводной таблицы.

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

Для этого вызовем мастер построения сводных таблиц.

Активизируем меню Вставка и выберем команду Сводная таблиц, щелкнув на неё мышкой, как показана на рис.4.5.

 

 

Рис.4.5

В результате активизации откроется диалоговое окно — первое окно мастера сводных таблиц рис.4.6.

 

Рис.4.6

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

 

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

Нажмем на кнопку в окне диапазон и выделим область на листе1 с B4 по J31(рис.4.7) и нажмем Enter.

Рис.4.7

Теперь, когда диапазон ячеек с данными для сводной таблицы указан укажем куда будет выгружена наша сводная таблица (на новый лист лист 4 ячейка А1).

 

После нажатия кнопки «ОК» на листе 4 появится заготовка сводной таблицы.

Эта область будет отображена на новом листе, если закрыть диалоговое окно Создание сводной таблицы.

На одной стороне находится область макета, где будет создан отчет сводной таблицы, а на другой — Список полей сводной таблицы. В этом списке показаны заголовки столбцов исходных данных. Каждый из заголовков представляет собой поле, например «Фамилия», «Средний бал по физике» и так далее.

Рис 4.8 Макет сводной таблицы

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

Если щелкнуть мышью вне области макета (отчета сводной таблицы), список полей сводной таблицы исчезнет. Чтобы снова вывести список полей на экран, щелкните область макета сводной таблицы или отчет.