Закрепление изученного

· Электронная таблица Excel 2007 Informatics.ssga.ru Attribution-ShareAlike (by-sa) — Лицензия «С указанием авторства — Копилефт» stanislav.katsko.name

· 2. Общие сведения об интерфейсе MS Excel 2007 Файлы MS Excel 2007 Строка формул Структура документа MS Excel 2007 Перемещение по книге и листу Выделение листов и фрагментов листа Отмена и возврат действий Ввод данных с помощью клавиатуры Ввод чисел, дат, времени О Формулах Содержание лекции

· 3. Операторы Использование ссылок в формулах: абсолютные, относительные, смешанные ссылки Проверка ошибок Математические вычисления Статистические вычисления Работа с примечаниями Защита данных Содержание лекции

· 4. Лента – главный элемент интерфейса. Удалить ленту, заменить панелями инструментов из предыдущих версий MS Excel нельзя! Общие сведения

· 5. Ленту можно свернуть или восстановить двойным нажатием по названию вкладки или Ctrl+F1. Содержание ленты для каждой вкладки постоянно и неизменно. Общие сведения

· 6. По умолчанию отображается 7 постоянных вкладок: Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид. Дополнительная вкладка: Разработчик. Вкладки

· 7. Стандартный набор вкладок заменяется при переходе из режима создания документа в другой режим: Предварительный просмотр. Вкладки

· 8. Контекстные вкладки – появляются автоматически при переходе в соответствующий режим либо при выделении объекта или установке на него курсора. Используются для работы с таблицами, рисунками, диаграммами и т.п. Вкладки

· 9. Те же самые, что и в MS Word! Кнопки; раскрывающиеся кнопки; списки; раскрывающиеся списки; счетчики; кнопки с меню; флажки; значки (кнопки) группы. Элементы управления

· 10. При нажатии кнопки отображается: – меню основных команд для работы с файлами; – список последних документов; – команда для настройки параметров приложения. Кнопка Office

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

· 12. Содержит основные наиболее часто используемые элементы для оформления данных. Не отображается автоматически при выделении фрагмента листа. Для отображения панели щелкнуть правой кнопкой по выделенной области. Мини-панель инструментов

· 13. Для просмотра и редактирования содержимого выделенной ячейки можно увеличить высоту строки формул. Для этого щелкните по кнопке Развернуть строку формул. Строка формул

· 14. Новый формат файлов, основанный на языке XML. Файлы, которые сохраняются с расширением xlsx, не могут содержать макросы, написанные на языке VBA или элементы управления ActiveX. Только файлы с расширением xlsm могут содержать макросы на языке VBA и элементы управления ActiveX, хранящиеся в обособленных частях файла. Файлы MS Excel 2007

· 15. Книга (рабочая книга) – документ MS Excel. Книга состоит из листов. По умолчанию – 3. Мин. число листов – 1, максимальное – не ограничено. Произвольный порядок размещения. Каждый лист имеет уникальное имя. Ярлыки листов находятся в нижней части окна. Листы содержат таблицы, диаграммы, рисунки и другие объекты. Могут быть листы, содержащие только диаграмму. Структура документа MS Excel 2007

· 16. Лист состоит из ячеек, объединенных в столбцы и строки. 16834 столбцов, именуются буквами английского алфавита. Первый столбец – А, последний – XFD. 1048576 строк, именуются арабскими цифрами. Адрес ячейки (ссылка) состоит из заголовка столбца и заголовка строки (А1, XFD1048576). Ячейка (или диапазон ячеек) может иметь имя. Ячейка может содержать данные (текстовые, числовые, даты, время и т.п.) и формулы. Ячейка может иметь примечание. Структура документа MS Excel 2007

· 17. Режим Обычный – по умолчанию. Для выполнения большинства задач – ввод и обработка данных, форматирование данных и ячеек, вычисления, построение диаграмм и т. д. Режим Разметка страницы позволяет изменять данные и при этом видеть их так, как они будут напечатаны на бумаге. В этом режиме обычно создают и оформляют колонтитулы. Режим Страничный – отображается только таблица и границы страниц. Остальные ячейки листа не отображаются. Режим Во весь экран – скрытие большинства элементов окна. Режимы просмотра листа MS Excel 2007

· 18. Минимальный масштаб – 10% Максимальный масштаб – 400% Масштаб отображения листа

· 19. Можно разделить лист по горизонтали и по вертикали на отдельные области. Одновременный просмотр частей листа

· 20. Используется при просмотре больших таблиц, чтобы при прокрутке листа закрепленные строки и столбцы оставались в окне. Закреплять можно только строки, только столбцы или и строки и столбцы одновременно. Используется кнопка Закрепить области группы Окно вкладки Вид. Закрепление областей листа

· 21. Временно ненужные для работы столбцы и строки листа можно скрыть. 1. Выделить один или несколько столбцов / строк. 2. В контекстном меню выбрать команду Скрыть. Для отображения: выделить столбцы слева и справа от скрытых, в контекстном меню выбрать Отобразить. Скрытие и отображение столбцов и строк

· 22. Для перехода к нужному листу книги щелкнуть мышью по соответствующему ярлыку листа в нижней части окна книги. Если необходимый ярлык не отображен в строке ярлыков, то для перехода к ярлыку нужного листа можно воспользоваться кнопками прокрутки ярлыков. Переход между листами с помощью клавиатуры: 1. Ctrl + Page Up – переход к листу, расположенному слева от активного листа. 2. Ctrl + Page Down – к листу, расположенному справа. Перемещение по книге

· 23. Полный список листов книги: 1. Щелкнуть правой кнопкой мыши по любой кнопке прокрутки ярлыков. 2. В контекстном меню выбрать нужный лист. Если в книге более 15 листов, для перехода к листу: 1. В контекстном меню выбрать команду Все листы 2. В диалоговом окне Переход к листу текущей книги выбрать нужный лист. Перемещение по книге

· 24. Чтобы облегчить поиск нужных ярлыков, их можно пометить разными цветами. 1. Щелкнуть по ярлыку листа правой кнопкой мыши. 2. В контекстном меню выбрать команду Цвет ярлычка, затем выбрать цвет в палитре цветов. Перемещение по книге

· 25. 1. С использованием мыши. 2. С использованием клавиатуры: ®,, ­, ¯, Page Up, Page Down, Home; Ctrl + ® – переход к правому краю текущей области; Ctrl + – к левому краю; Ctrl + ¯ – к нижнему краю; Ctrl + ­ – к верхнему краю; Ctrl + Home – переход в начало листа; Ctrl + End – переход в последнюю ячейку на листе (в самой нижней используемой строке крайнего справа используемого столбца; Alt + Page Up – переход на экран влево; Alt + Page Down – на экран вправо. Перемещение по листу

· 26. 3. С использованием средств Microsoft Excel. Раскрывающийся список Имя в строке формул содержит имена ячеек и диапазонов ячеек. – открыть список, выбрать требуемое имя или – ввести с клавиатуры адрес нужной ячейки и нажать клавишу Enter. Перемещение по листу

· 27. Использование ярлыков листа. У выделенного листа цвет ярлыка ярче, чем у остальных листов. Выделенным всегда является текущий лист. Выделение нескольких рядом расположенных листов: – щелкнуть мышью по ярлыку первого листа из группы; – при нажатой клавише Shift щелкнуть мышью по ярлыку последнего листа из группы. Выделение листов

· 28. Выделение нескольких произвольно расположенных листов: – щелкнуть мышью по ярлыку первого листа из группы; – при нажатой клавише Ctrl щелкайте мышью по ярлыкам необходимых листов. Выделение всех листов книги: – щелкнуть правой кнопкой мыши по ярлыку любого листа; в контекстном меню выбрать команду Выделить все листы. Выделение листов

· 29. Всегда выделена хотя бы одна ячейка. Эта ячейка обведена толстой линией. Одиночный выделенный фрагмент листа также обведен толстой линией. Ячейки затенены, кроме одной, как правило, самой левой верхней ячейки. Цвет шрифта, цвет заливки и рамок ячеек при выделении не изменяются. Выделение фрагментов листа

· 30. Выделение одной ячейки с помощью мыши: – навести на нее указатель мыши в виде белого креста; – один раз щелкнуть левой кнопкой мыши. Выделение диапазона смежных ячеек: – навести указатель мыши в виде белого креста на крайнюю ячейку выделяемого диапазона; – нажать на левую кнопку мыши; – не отпуская ее, распространить выделение на смежные ячейки. Описание диапазона смежных ячеек – через двоеточие левая верхняя и правая нижняя ячейки, например, А1:С12. Выделение фрагментов листа

· 31. Выделение нескольких несмежных ячеек: – выделить первую ячейку; – выделить каждую следующую – при нажатой клавише клавиатуры Ctrl. Выделение несколько несмежных диапазонов: – первый диапазон выделяется обычным образом; – каждый следующий диапазон – при нажатой клавише клавиатуры Ctrl. Описание диапазона несмежных ячеек – через точку с запятой каждый диапазон, например, А1:С12; Е4:Н8. Выделение фрагментов листа

· 32. Один раз щелкнуть левой кнопкой мыши по прямоугольнику левее заголовков столбцов. Выделение всех ячеек листа

· 33. Отмена последнего выполненного действия: нажать кнопку Отменить в Панели быстрого доступа. Отмена сразу нескольких последних действий: 1. Щелкнуть по стрелке кнопки Отменить. 2. Выбрать действие, начиная с которого следует отменить все действия, расположенные выше него в списке. Отмена не более 100 действий! Отмена и возврат действий

· 34. Возврат последнего отмененного действия: нажать кнопку Вернуть в Панели быстрого доступа. Возврат сразу нескольких последних действий: 1. Щелкнуть по стрелке кнопки Вернуть. 2. Выбрать действие, начиная с которого следует отменить все действия, расположенные выше него в списке. Отмена и возврат действий

· 35. Ввод данных в ячейку или в строку формул. 1. Выделить ячейку. 2. Ввести данные с клавиатуры непосредственно в ячейку или в строку формул. 3. Подтвердить ввод: – нажать Enter или Tab; – нажать кнопку Ввод (галочка) в строке формул; – выделить другую ячейку на листе (нельзя при вводе формул). Ввод данных с использованием клавиатуры

· 36. При вводе неправильного символа – удаление BackSpace. По умолчанию все данные в ячейке вводятся одной строкой. Переход к новой строке (новому абзацу) в ячейке – Alt + Enter. Ввод данных с использованием клавиатуры

· 37. Отказ от ввода данных в ячейку – клавиша Esc или кнопка Отмена (крестик) в строке формул. Содержание в ячейке – до 32767 символов. Длина записи для формулы – 8192 символа. Текст ячейки не помещается по ширине столбца, а ячейки справа не содержат данных – текст отображается на листе на соседних ячейках справа. Ячейки справа содержат какие-либо данные – текст на листе не отображается. Текст в строке формул отображается полностью. Ввод данных с использованием клавиатуры

· 38. Первые символы, вводимые в ячейку, совпадают с символами записи, ранее введенной в этом столбце – недостающая часть записи может быть введена автоматически. Нажать клавишу Enter. Только для записей, которые содержат текст или текст в сочетании с числами. Использование автозавершения

· 39. Первоначальный ввод числа в ячейку – отображается число из 11 цифр. Ширина столбца может автоматически увеличиться. Ввод большего числа – число в экспоненциальном формате. Ширина столбца уменьшена, число не помещается в ячейке – вместо числа отображаются символы # Ввод чисел

· 40. Наибольшее число в ячейке – 9,99999999999999*10 307. Точность представления чисел – 15 разрядов. Десятичные дроби от целой части числа отделяют запятой. Числа с простыми дробями – простую дробь от целой части числа отделяют пробелом. В строке формул простая дробь отображается как десятичная. Ввод чисел

· 41. При вводе числа в формате текста, они не должны распознаваться как числа. Например, номера банковских счетов, номера телефонов, коды, почтовые индексы, номера домов, квартир и т. п. В Microsoft Excel перед числом необходимо ввести знак апострофа ('). Этот знак отображается только в строке формул, но не отображается в ячейке. Ввод чисел

· 42. Тип "дата" – начиная с 1 января 1900 года. Тип "текст" – до 1 января 1900 года. Ввод даты: число месяца, месяц, год. Разделители: точка (.), дефис (-), дробь (/). Пример: Ввод даты 12 августа 1918 г. 12.8.1918 или 12-8-1918 или 12/8/1918 Независимо от способа ввода, первоначально дата в ячейке отображается в полном формате! Ввод дат и времени

· 43. При вводе года можно ограничиться двумя цифрами, если речь идет о датах с 1 января 1930 года по 31 декабря 2029 года. Пример: Ввод даты 12 апреля 1961 г. 12.4.61 или 12-4-61 или 12/4/61 Независимо от способа ввода, первоначально дата в ячейке отображается в полном формате! Ввод дат и времени

· 44. Для дат первого десятилетия XXI века год можно указывать одной цифрой. Пример: Ввод даты 2 ноября 2002 г. 2.11.2 или 2-11-2 или 2/11/2 Независимо от способа ввода, первоначально дата в ячейке отображается в полном формате! Ввод дат и времени

· 45. При вводе даты текущего года можно ограничиться вводом только числа месяца и месяца. Пример: Ввод даты 2 ноября сего года. 2.11 или 2-11 или 2/11 Дата в ячейке первоначально будет отображена в кратком формате! Ввод текущей даты – комбинация клавиш Ctrl +; Ввод дат и времени

· 46. Порядок ввода времени: час, минуты, секунды. Секунды вводить не обязательно. Разделители – двоеточие (:) Пример: Ввод времени 14 часов 12 минут 00 секунд. 14:12 Ввод текущего времени – комбинация клавиш Ctrl + Shift +; Ввод дат и времени

· 47. Замена содержимого: выделить ячейку, ввести новые данные и подтвердить ввод. Редактирование содержимого: непосредственно в ячейке или в строке формул. 1. Щелкнуть по ячейки два раза левой кнопкой мыши или выделить ячейку и нажать клавишу F2. 2. Щелкнуть в строке формул левой кнопкой мыши. После редактирования обязательно подтвердить ввод! Правка содержимого ячеек

· 48. Excel проверяет только орфографию! Используется кнопка Орфография во вкладке Рецензирование. Проверка правописания в документе

· 49. Формулы – выражения, по которым выполняются вычисления. Формула всегда начинается со знака равно (=). Формула может включать: – функции; – ссылки на ячейки или имена; – операторы; – константы. О формулах

· 50. =СУММ(В2:В8)*30 СУММ() – функция; В2 и В8 – ссылки на ячейки;: (двоеточие) и * (звездочка) – операторы; 30 – константа. О формулах

· 51. Функции – заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Структура функции: – имя функции; – открывающая скобка; – список аргументов, разделенных точками с запятой; – закрывающая скобка. Аргументы функции: – число, текст, логическое значение, массив, значение ошибки, ссылка на ячейку; – константы, формулы, или функции. О формулах

· 52. Ссылка указывает на ячейку или диапазон ячеек листа. Ссылки на ячейки других книг называются связями. Оператор – знак или символ, задающий тип вычисления в формуле. Типы операторов: – математические; – логические; – операторы сравнения; – операторы ссылок. Константа – постоянное (не вычисляемое) значение. О формулах

· 53. Арифметические операторы служат для выполнения арифметических операций (сложение, вычитание, умножение). Операции выполняются над числами. сложение А1 + А2 вычитание А1 - А2 отрицание - А1 умножение А1 * А2 деление А1 / А2 процент 50 % возведение в степень А1 ^ 2 Операторы

· 54. Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ. равно А1 = А2 больше А1 > А2 меньше А1 < А2 больше или равно А1 >= А2 меньше или равно А1 <= А2 не равно A1 <> A2 Операторы

· 55. Текстовый оператор конкатенации – амперсанд (&) используется для объединения нескольких текстовых значений. Объединение последовательностей знаков в одну последовательность: &quot;Фамилия&quot; & &quot;Имя&quot; & &quot;Отчество&quot; Операторы

· 56. Операторы ссылок используют для описания ссылок на диапазоны ячеек.: (двоеточие) ставится между ссылками на первую и последнюю ячейки диапазона B5: B15; (точка с запятой) – оператор объединения B5:B15; D5:D15 (пробел) Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов B7:D7 C6:C8 Операторы

Ссылки на ячейки других листов и книг При создании формулы переходим на другой лист текущей книги или в другую книгу и выделяем там необходимую ячейку. Имя листа и адрес ячейки автоматически разделяются служебным символом! (восклицательный знак). Использование ссылок в формулахЗнакомство с программой Microsoft Excel 2007

Чтобы запустить Excel, выполните Пуск -> Все программы -> Microsoft Office -> Microsoft Office Excel 2007. Поскольку Excel является программой, входящей, как и Word, в состав пакета Microsoft Office, интерфейс этих приложений во многом схож. Главное меню также представлено в виде вкладок, на ленте которых находятся группы инструментов, предназначенных для форматирования ячеек и обработки данных. Некоторые из них вам хорошо знакомы из предыдущих статей о Word 2007, большинство же являются совершенно новыми. К изучению важнейших опций мы приступим чуть позже, а сейчас рассмотрим структуру окна Excel (рис. 1).

 

Рис. 1. Окно Microsoft Excel 2007

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

Строки образованы горизонтальными рядами ячеек и пронумерованы числами (1, 2, 3…). Максимальное количество строк в листе — 1 048 575. Нумерацию можно видеть в заголовках строк в левой части окна Excel. Столбцы обозначаются латинскими буквами (A, B, C, D, … Z, AA, АВ, АС, AD … AZ, BA, BB, BC, …, BZ…. AAA, AAB, AAC и т.д.), которые находятся в заголовках столбцов под лентой. Максимальное количество столбцов в листе — 16 384.

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

Рис. 2. Простейший пример учета финансов в Excel

Каждая ячейка таблицы имеет уникальный адрес, который легко узнать, посмотрев на номер строки и столбца, на пересечении которых она расположена. Иначе положение ячейки принято называть ссылкой, что более правильно при использовании формул. Так, на рис. 2 ячейка, содержащая итоговую стоимость купленных товаров, имеет адрес D10, а ячейка, в которой набрана цена зубной пасты — В3.

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

В ячейках столбца А набраны текстовые данные — наименование товара. Столбцы В и С содержат введенные с клавиатуры числовые данные — цену и количество товара. Во всех, за исключением последней, ячейках последнего столбца D помещены формулы, по которым Excel перемножает данные, находящиеся в двух предыдущих столбцах. В самих ячейках мы видим только результат перемножения. Чтобы увидеть формулу, которая применялась для расчетов, нужно выполнить на ячейке щелчок. При этом выражение отобразится в строке формул (рис. 3).

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

Рис. 3. Расчетная формула выделенной ячейки

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

В ячейку D2 (см. рис. 3) была введена формула, указывающая системе на то, что нужно отобразить результат перемножения чисел, находящихся в ячейках В2 и С2. Для распространения формулы перемножения на все ячейки последнего столбца был применен специальный прием копирования. В Excel существует множество способов ускоренного ввода данных и набора формул. О них мы обязательно поговорим ниже.

Чтобы получить суммарную стоимость товаров, в последней ячейке D10 была введена формула =СУММ(D2:D9), которая отвечает за суммирование содержимого вышележащих ячеек данного столбца.

Прежде чем переходить к вопросу проведения расчетов в Excel, рассмотрим правила ввода и редактирования данных.