Возможности форматирования электронных таблиц

Федеральное государственное образовательное бюджетное учреждение

Высшего образования

«ФИНАН

«ФИНАНСОВЫЙ УНИВЕРСИТЕТ ПРИ ПРАВИТЕЛЬСТВЕ

РОССИЙСКОЙ ФЕДЕРАЦИИ»

Департамент анализа данных, принятия решений и финансовых технологий

Я.Л. Гобарева, О.Ю. Городецкая, А.В. Золотарюк

Введение в Excel

Учебно-методические рекомендации для проведения

семинара №1 по компьютерному практикуму

Для бакалавров направления 38.03.01 «Экономика»

Электронное издание

Москва 2017

Общая характеристика табличногопроцессораMSExcel

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

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

Примерный вид окна табличного процессора с описанием его элементов показан на рис. 1.

Рис. 1. Элементы окна Excel

Электронная книга, в зависимости от версии процессора, по умолчанию состоит из одного или трех электронных листов (максимально возможное количество листов – 65535 (2¹⁶-1), зависит от объема оперативной памяти ПК). Каждый лист книги – совокупность клеток (ячеек), образованных 16 384 столбцами (2¹⁴) и 1 048 576 строками (2²⁰). Столбцы именуются латинскими буквами (от Aдо XFD, строки – цифрами. Это позволяет адресовать ячейки. Например, левая верхняя ячейка листа имеет адрес A1, правая нижняя –XFD1048576.

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

Функциональная клавиша [F5] → В поле Ссылка ввести адрес ячейки → ОК

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

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

На листе диапазоны ячеек выделяются перемещением мыши при нажатой ее левой кнопке(или при удерживании клавиши [Shift]и нажатии на клавиши перемещения курсора). Для выбора несмежных диапазонов дополнительно удерживают клавишу [Ctrl].Для выделения трехмерного диапазона предварительно выделяют несколько листов книги, выполнив щелчки левой кнопкой мыши по ярлычкам листов при удерживаемой клавише [Ctrl].

При записи адресов диапазонов указывают адрес левой верхней ячейки и адрес правой нижней ячейки, разделяя их двоеточием. Группы несмежных ячеек разделяют точкой с запятой (рис.2).

Ввод информации в активную ячейку завершается нажатием клавиши [Enter] (или клавиш перемещения курсора). В смежный диапазон (а также часть несмежного диапазона с активной ячейкой) ввод данных во все ячейки одновременно фиксируется комбинацией клавиш [Ctrl+Shift+Enter].

Максимальный объем данных, который может быть введен в каждую из ячеек книги – 32767 (2¹⁵-1) байт (символов) Правда, отображены в ячейке могут не все символы: одна из причин – ограниченность видимой ширины столбца и наличие данных в ячейке справа. Да в этом и нет необходимости: принцип структурированного заполнения данных предполагает в отдельные ячейки помещать только взаимосвязанные, желательно неделимые данные, принадлежащие к двум категориям – значение или формула (рис.3).

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

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

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

Рис. 3. Типы данных Excel

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

При копировании формул относительные адреса соответствующим образом изменяются; абсолютные адреса остаются неизменными.

Характерным признаком абсолютного адреса являются знаки$ перед именами столбцов и строк. Например, $A$1 и $XFD$1048576 — соответственно абсолютные адреса первой (левой верхней) и последней (правой нижней) ячеек электронного листа.

Абсолютные адреса могут быть непосредственно набраны с клавиатуры либо преобразованы из относительных адресов после их выделения и нажатия функциональной клавиши [F4]. При преобразовании следует быть внимательным: повторное нажатие [F4] может сделать адрес снова относительным либо смешанным, когда, например, зафиксирован только столбец или только строка: $D4, G$5:F$8.

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

Один из способов присвоения имен ячейкам:

Выделить ячейку или диапазон → Открыть меню щелчком правой кнопки мыши →Команда Присвоить имя…→ Ввести имя → ОК

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

[Книга2]Лист4!G5:L10 [Отчет]Квартал_1!$A$5:$Z$129

Если имена листов или книг содержат пробелы, то какие имена обрамляются апострофами:

'[Книга 1]Лист 3’!$E$8 ‘[Квартальный отчет]Филиал 3’!$D$2:$K$120

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

Возможности форматирования электронных таблиц

Операции форматирования представим схематично.

· Изменение ширины столбца путем захвата границы между наименованиями столбцов (рис. 4).

Рис. 4. Увеличение ширины столбца E

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

· Изменение высоты строк выполняется по аналогии (рис. 5).

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

· Для объединения ячеек можно воспользоваться как возможностями окна Формат ячеек (см. рис. 6), так и с помощью инструментальной кнопки в группе Выравнивание вкладки Главная (рис. 7).

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

· Изменение гарнитуры шрифта, размера и цвета символов, заливки и обрамления ячеек выполняется с помощью инструментальных кнопок вкладки Главная(рис. 8) и возможностей окна Формат ячеек (рис. 9).

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

Рис. 6. Окно изменения форматирования данных в ячейках

Рис. 7. Объединение ячеек с помощью инструментальной кнопки

Рис. 8. Инструментальные возможности изменения параметров шрифта и заливки ячеек

Рис. 9. Возможности задания границ ячеек электронной таблицы

Особенности работы с большими таблицами

Копирование (табулирование) данных (формул, последовательностей, констант) вниз по столбцу большой таблицы:

Выделить ячейку (две ячейки для распространения последовательности) → Захватить мышкой правый нижний уголок выделенной области → Выполнить двойной щелчок левой кнопкой мыши по черному плюсику

Данные в столбце автоматически протабулируются до конца таблицы (пока в левом столбце будет какая-то информация).

Отметим также некоторые комбинации клавиш, полезные при работе с таблицами:

· [Ctrl+Shift+↓] – выделение столбца таблицы от текущей ячейки по направлению стрелки (вниз).

· [Ctrl+Shift+→] – выделение строки таблицы от текущей ячейки по направлению стрелки (вправо).

· [Ctrl+Shift+End] – выделениечасти таблицы до последней используемой ячейки (справа внизу).

· [Ctrl+C] – копирование выделенных ячеек в Буфер обмена.

· [Ctrl+V] – вставка данных их Буфера обмена (в активную ячейку с распространением вниз и вправо).

· [Ctrl+Shift+F]– изменение параметров шрифта и ячеек выделенном диапазоне с помощью окна Формат ячеек.

· [Ctrl+Shift+:] – вставка текущего времени.

· [Ctrl+Shift+;] – вставка текущей даты.

· [Ctrl+Shift+&] – вставка внешних границ в выделенный диапазон ячеек.

Задание 1.

История Финансового университета отсчитывается со 2 марта 1919 г., когда в МФЭИ – первом финансово-экономическом институте нашей страны – начались занятия.

Разработать таблицуExcel, содержащую сведения, в какой день недели отмечалась (будет отмечаться) годовщина вуза, начиная с 1919 года по 2019 год.

Выделить ячейки с юбилейными датами – 10 лет, 25 лет, 50 лет, 75 лет, 100 лет.

В какой день недели отмечалась годовщина вуза в 1975 и 2000 годах?

Сохранить файл под именем ВашеФИО_Задание1 в личную папку на своей группы на сетевом диске.

Порядок решения.

1. Открыть книгу Excel.

2. В ячейку А1 ввести текст: Финансовый университет при Правительстве Российской Федерации.

3. В ячейку А3 ввести текст: Год существования.

4. В ячейку В3 ввести текст: Дата.

5. В ячейку С3 ввести текст: Порядковый день недели.

6. В ячейку D3 ввести текст: День недели.

7. В ячейку А4 ввести число 0, в ячейку А5 – число 1.

8. В ячейку В4 ввести дату: 02.03.1919, в ячейку В5 – 02.03.1920.

9. В ячейку С4 ввести формулу: =ДЕНЬНЕД(B4;2). Для появления окна (рис. 10) необходимо нажать на кнопку f_x левее строки формул.

Рис. 10. Окно задания параметров функции ДЕНЬНЕД (день недели по дате)

10. В ячейки диапазона-столбца G4:G10 соответственно ввести названия дней недели – Понедельник, Вторник, Среда, Четверг, Пятница, Суббота, Воскресенье.

11. В ячейки диапазона-столбца F4:F10 соответственно ввести числа от 1 до 7.

12. Выделить диапазон F4:G10 → Вкладка Формулы → Определенные имена → Присвоить имя → Ввести имя Дни_недели → ОК(рис. 12).

13. В ячейку D4 ввести формулу: =ПРОСМОТР(C4;Дни_недели) (рис. 13).

Рис. 12. Присвоение имени диапазону ячеек

14. Выделить ячейки А4:А5. Захватив левой кнопкой мыши правый нижний уголок, когда курсор мыши станет маленьким черным плюсом, протабулировать данные до числа 100 (ячейки А104); снять выделение щелчком левой кнопки мыши по свободной ячейке.

15. Выделить ячейки В4:В5. Подведя курсор мыши к правому нижнему уголку выделенной области и, как тот примет вид черного плюсика, выполнить по нему двойной щелчок левой кнопкой мыши. Данные в столбце автоматически протабулируются до ячейки В104 (пока в левом столбце будут данные).

Рис. 13. Ввод формулы со встроенной функцией ПРОСМОТР (искомое значение Массив)

16. Выделить диапазон С4:D4. Аналогично, захватив правый нижний уголок, протабулировать его до конца таблицы (рис. 14). Расчет таблицы будет завершен.

Рис. 14. Подготовка к табуляции формул в столбцах С и D

17. Выделить ячейки А1:D1 и выполнить их объединение с переносом по словам. Изменить параметры шрифта.

18. Выделить ячейки А3:D3 и изменить их формат, задав значения по центру и перенос по словам.

19. При необходимости изменить ширину столбцов таблицы (А:D).

20. Для выделения всей таблицы и задания границ, обрамляющих ячейки, выполнить:

Поместить табличный курсор в ячейку А3 →

Выделить шапку таблицы комбинацией клавиш [Ctrl+Shift+→] →

Выделить всю таблицу комбинацией клавиш [Ctrl+Shift+↓] →

Задать обрамление ячеек: Все границы

21. Выполнить заливку данных по юбилейным датам.

22. Отметить дни недели празднования юбилея вуза в 1975 и 2000 годах.

23. Сохранить файл под требуемым именем на сетевой диск в личную папку студента.

Дата добавления: 2018-04-05; просмотров: 475; Мы поможем в написании вашей работы!

Поделиться с друзьями:

Мы поможем в написании ваших работ!