Конспект лекций по Excell. 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12. Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно
 Скачать 3.95 Mb.
|
|
Мак- рос1, Макрос2 и т.д. 4. Лист диалогового окна. На этом листе создаются диалоговые окна – ори- гинальные элементы интерфейса пользователя. Их имена по умолчанию Диалог1, Диалог2 и т.д. Количество листов в рабочей книге может быть произвольным – оно ог- раничено только наличием свободных ресурсов на компьютере. По умолчанию каждая, вновь создаваемая рабочая книга, содержит три рабо- чих листа – Лист1, Лист2 и Лист3. Каждый рабочий лист состоит из 65 536 = 2 16 пронумерованных строк, начиная с 1, и 256 = 2 8 столбцов, проиндексированных буквами латинского алфави- та, начиная с A, и заканчивая IV. 10 На пересечении строк и столбцов находятся ячейки, общее количество кото- рых равно 16 777 216 = 65 536 × 256. Каждая ячейка имеет адрес (или имя), определяемый по столбцу и стро- ке, на пересечении которых она расположена. Таким образом, адрес первой ячейки листа равен А1 , а последней IV65536. Ячейка – это элементарная (неделимая) и однозначно адресуемая едини- ца информации, обрабатываемой с помощью электронной таблицы. Каждая ячейка рабочего листа может быть пустая, или непустая, т.е. со- держать или константу, или формулу. Константы бывают: 1. Числовые, которые могут состоять только из цифр и, возможно, неко- торых специальных знаков, таких как + - ( ) , / $ % . E e. 2. Текстовые – это константы, которые Excel не смог распознать как константы другого типа. Они могут содержать любые символы из до- пустимого набора, длиной не более 32 767 знаков. Текстовыми кон- стантами могут быть и числа, если при вводе им предшествует апост- роф, например, '1234. Это могут быть, например, табельные номера со- трудников. Естественно, что такие константы не могут участвовать в арифметических вычислениях. 3. Логические – могут принимать только одно из двух взаимоисклю- чающих значений: ИСТИНА или ЛОЖЬ. Они используются как инди- катор наличия или отсутствия какого-либо признака или события, а также могут являться параметрами или возвращаемыми значениями некоторых функций. Во многих случаях вместо этих значений исполь- зуются цифры 1 и 0, соответственно. 4. Ошибки – служат для индикации некоторых нештатных ситуации, возникших во время работы Excel, например деление на нуль. Значе- ния этого типа констант начинаются с символа «#». Более детальную информацию о причине возникновения конкретной ошибки можно по- лучить в справочной системе Microsoft Excel, осуществив поиск по ти- пу отображаемой ошибки. Кроме перечисленных выше основных типов данных Microsoft Excel под- держивает еще два производных: 5. Дата и время суток (или Календарный) – это тот же числовой тип дан- ных, в котором целая часть используется для хранения количества 11 дней прошедших с 1 января 1900 года, а дробная – это часть суток, прошедшая с полуночи. 6. Массив, собственно, не являются конкретным типом данных, а только образует организованное множество ячеек или констант любого типа. В Microsoft Excel массив рассматривается как единый элемент, к которому в целом могут быть применены математические, логиче- ские и другие типы операций. При работе с электронными таблицами довольно часто возникает ситуа- ция, когда с некоторой совокупностью ячеек необходимо работать как с еди- ным целым, например при их удалении, копировании, перемещении и т.д. В этом случае применяются так называемые диапазоныячеек. Для указания диапазона ячеек используется символ двоеточия, помещенный между ссылка- ми на первую и последнюю ячейки диапазона, например, B2:D4 Максимальная ширина столбца в Microsoft Excel – 255 символов, максимальная высота строки – 409 точек, максимальная длина содержимого ячейки для текста и формул – 32 767 символов,при этом отображаются только первые 1024 символа для текста и все символы для формул в строке формул. Формулой вMicrosoft Excel называется последовательность символов, начинающаяся со знака равенства (=). Формулы в MS Excel служат для полу- чения новых значений, т.е. являются основным инструментом преобразования информации. Знак равенства свидетельствует о том, что последующие симво- лы составляют формулу. Элементы, следующие за знаком равенства, являются операндами, разделенными операторами вычислений. Таким образом, всякая формула состоит из 2-х частей: 1. знака равно (=), 2. за которым следует выражение. То есть, синтаксис формулы можно представить следующим образом: = <выражение> . Результат вычисления выражения будет присвоен в качестве значения ячейке, в которую эта формула записана. Например, формула = 1 присваивает в качестве значения ячейки, в ко- торой она записана, константу числового типа 1, а = A1 + 1 – записывает в те- кущую ячейку значение ячейки A1, увеличенное на 1. Выражение, в свою очередь, состоит из: 1) констант, 12 2) адресов (или ссылок на) ячейки, 3) имен функций, 4) знаков операций, 5) круглых скобок. Например: =2*А1+СУММ(В2:В4) Формула вычисляется слева направо, в соответствии с определенным порядком для каждого оператора в формуле. Использование круглых скобок в выражении позволяет изменить стандартный (естественный) порядок выпол- нения операций. Кроме констант или формул с любой ячейкой может быть также связано примечание – дополнительный текст поясняющий, например, ее содержание. Индикатор наличия примечания – маленький красный треугольник в верхнем правом углу ячейки. При перемещении указателя мыши в пределы ячейки, со- держимое примечания отображается в отдельном окне примечания. 1.4. Адресация ячеек Для использования значений, над которыми требуется выполнить неко- торые вычисления, необходимо указать расположение ячеек, которые содер- жат эти значения. Расположение ячеек в Microsoft Excel однозначно определя- ется их адресами (ссылками, координатами и т.д.). Для адресации ячеек в пределах одного рабочего листа используется два стиля адресации, которые, соответственно, называются: 1. A1 – указывается имя столбца и номер строки, 2. R1C1 – указывается символ R (от английского Row – строка), номер строки, символ C (от английского Column – столбец) и номер столбца. Например, ячейка на пересечении 3-й строки и 4-го столбца в стиле A1 имеет адрес D3, а в стиле R1C1 – R3C4. По умолчанию в Microsoft Excel используется стиль адресации ячеек A1. Стиль же ссылок R1C1 довольно часто используется в макросах. Переключение между стилями адресации выполняется по команде: Сервис→Параметры→Общие. После чегонеобходимо установить или сбросить, соответственно, флажок Стиль ссылок R1C1. 13 При изменении стиля ссылок Microsoft Excel автоматически производит соответствующие изменения в ссылках на ячейки во всех формулах ра- бочей книги. Визуальным признаком того, что Excel использует стиль адресации ячеек R1C1, является то, что в качестве заголовков столбцов используются цифры, а не буквы латинского алфавита, как в стиле A1. Кроме этого, в Excel можно задавать ссылки на ячейки других листов той же книги, другой книги, а также на данные из других приложений. Ссылки на ячейки других рабочих книг называются внешними, ссылки на данные из других приложений – удаленными. Ссылки на ячейки в пределах рабочей книги включают имя листа и адрес ячейки на листе. Имя рабочего листа отделяется от адреса ячейки сим- волом восклицательного знака (!). Например: Накладная!D3 – адрес ячейки D3 на рабочем листе Накладная. Если имя листа содержит пробелы или начинается с цифры, оно заклю- чается в одиночные апострофы, в противном случае апострофы не ис- пользуются. Например: ‘Накладная 1’!D3 – адрес ячейки D3 на рабочем листе Накладная 1. При необходимости анализа данных из одних и тех же ячеек на несколь- ких листах одной и той же рабочей книги в Microsoft Excel используются так называемые трехмерные ссылки. Трехмерная ссылка включает в себя ссылку на ячейку, или диапазон ячеек, перед которой указывается диапазон имен лис- тов. В трехмерной ссылке Excel использует все рабочие листы, указанные между первым и последним. Например, формула =СУММ(Лист2:Лист5!A1) суммирует все значе- ния, содержащиеся в ячейке A1 на всех рабочих листах от Лист2 до Лист5 включительно. В Microsoft Excel можно создавать ссылки не только между различными листами одной и той же книги, но и организовать иерархию связанных книг. Если ячейки, на которые имеются ссылки, изменяются, Excel автомати- чески обновляет ссылки только для открытых книг, содержащих связи. Если же зависимая книга закрыта, связи можно обновить вручную, вы- полнив следующие действия: 14 1. Выполнить команду Правка → Связи. Недоступность пункта Связи оз- начает, что данная рабочая книга не содержит связанных данных. 2. В раскрывшемся при этом окне диалога Изменение связей: а) в списке выбрать источник для связанного объекта, для выделения не- скольких связей необходимо выбрать их при нажатой клавише CTRL; б) щелкнуть по кнопке Обновить. Microsoft Excel отображает ссылками в формулах на другие книги двумя способами, в зависимости от состояния исходной книги (той, что предоставля- ет данные для формулы): а) открыта она или б) закрыта. Внешний адрес ячейки в открытой рабочей книге содержит: 1. Имя рабочей книги. 2. Имя рабочего листа. 3. Адрес ячейки. Имя рабочей книги берется в квадратные скобки, имя листа следует за именем книги без разделителей, которое, в свою очередь, отделяется от адреса ячейки восклицательным знаком, например: [Товары.XLS]Накладная!D3 – адрес ячейки D3 на рабочем листе На- кладная открытой рабочей книги Товары.XLS. Когда книга закрыта, ссылка должна включать дополнительно полный путь. Например: ’C:\Мои документы\[Товары.XLS]Накладная’!D3 – адрес ячейки D3 на рабочем листе Накладная рабочей книги Товары.XLS, которая на- ходится в папке Мои Документы на диске С. Если имя рабочего листа, книги или путь содержит символы, не являющиеся буквами, необходимо заключить их в одиночные кавычки. 15 1.5 . Относительная и абсолютная адресация Оба способа адресации указывают на одни и те же ячейки. Их различие проявляется только в двух случаях: 1) при копировании ячеек, содержащих формулы, и при 2) изменении структуры рабочего листа. Относительная адресация основана на том, что ссылки на ячейки с ис- ходными данными создаются относительно ячейки, содержащей формулу. Это означает, что при добавлении или удалении строк, столбцов или отдельных ячеек, а также при копировании формулы в другие ячейки ссылки в каждой копии изменяются таким образом, чтобы сохранились те же соотношения ад- ресов, что и в исходной формуле. То есть, в формуле учитывается «расстоя- ние» между ячейкой, содержащей формулу, и ячейкой, на которую в этой формуле есть ссылка. Например, адрес В2. При абсолютной адресации ссылка на ячейку содержит букву столбца и номер строки, перед которыми стоит знак доллара ($). При этом предполагает- ся, что в результате копирования или изменении структуры рабочего листа ссылка не изменяется, и будет указывать на ту же, что и ранее, ячейку. Например, адрес $В$2. Смешанная адресация, т.е. комбинация относительной и абсолютной предполагает «фиксацию» только одной из двух «координат» ячейки. Цикли- ческое изменение способа адресации во время ввода или редактирования с от- носительного на абсолютный и, далее, на смешанный выполняется путем по- следовательных нажатий на клавишу F4. Например, адреса $В2, В$2. Результаты выполнения копирования ячейки, в которую записана про- стейшая формула (например, =E7), при различных способах адресации пред- ставлены на следующих рисунках. =C5 =D5 =E5 =F5 =G5 =C6 =G6 =C7 =E7 =G7 =C8 =G8 =C9 =D9 =E9 =F9 =G9 =$E$7 =$E$7 =$E$7 =$E$7 =$E$7 =$E$7 =$E$7 =$E$7 =$E$7 =$E$7 =$E$7 =$E$7 =$E$7 =$E$7 =$E$7 =$E$7 =$E$7 16 По умолчанию в стиле A1 используется относительная адресация, в то время как в R1C1 – абсолютная. При работе с электронными таблицами довольно часто возникает ситуа- ция, когда с некоторой совокупностью ячеек необходимо работать как с еди- ным целым, например при их удалении, копировании, перемещении и т.д. В этом случае применяются так называемые диапазоны ячеек. Для указания диапазона ячеек используется символ двоеточия (:), помещенный между ссылками на первую и последнюю ячейки диапазона, например, B2:D4. Диапазон может включать только смежные ячейки таблицы. Чтобы ссылка указывала на целые столбцы или целые строки, следует использовать диапазон, содержащий в обеих частях имена столбцов или номера строк, на- пример, B:C, 3:5. Такое обозначение диапазона позволяет автоматически включать в обработку все ячейки данных строк или столбцов, даже если впо- следствии возникнет необходимость в добавлении или удалении последних. Вместо символа двоеточия при вводе можно использовать и символ точки (.). Для разделения элементов списка, состоящего из отдельных ячеек, и (или) диапазонов ячеек, используется символ «Разделитель элементов спи- ска», например, 2:3,B5. В разных версиях и вариантах локализации операционной системы Micro- soft Windows символ «Разделитель элементов списка» может иметь различ- ное значение. Так, например, в русифицированной версии операционной системе Win- dows XP по умолчанию – это символ точки с запятой (;), а в панъевропейской – запятая (,). Его установка, например, в операционной системе Windows XP осуществляется по такому алгоритму: 1. Выполнить команду Пуск → Панель управления. 2. В раскрывшемся в окне Панель управления, : а) в классическом виде выбрать значение Языки и региональные стандарты, б) с разбивкой по категориям возможностей выбрать – Дата, время языки и региональные стандарты. =C$7 =D$7 =E$7 =F$7 =G$7 =C$7 =G$7 =C$7 =E$7 =G$7 =C$7 =G$7 =C$7 =D$7 =E$7 =F$7 =G$7 =$E5 =$E5 =$E5 =$E5 =$E5 =$E6 =$E6 =$E7 =$E7 =$E7 =$E8 =$E8 =$E9 =$E9 =$E9 =$E9 =$E9 17 б.1) В раскрывшемся окне Дата, время языки и региональные стан- дарты выбрать: • Изменение формата отображения чисел, даты и времени, или • Языки и региональные стандарты. 3. В окне диалога Языки и региональные стандарты нажать кнопку Настройка. 4. В поле со списком Разделитель элементов списка окна диалога Настрой- ка региональных параметров указать необходимый символ-разделитель элементов списка. В этом окне диалоговом можно установить так же символ-разделитель целой и дробной части числа, количество знаков в дробной части числа, количество 18 цифр в группе, и другие параметры отображения числовых величин, даты, времени, а так же денежных единиц для операционной системе Windows в це- лом. Отказаться от использования системных (глобальных) разделителей, и определить свои, можно на вкладке Международные окна диалога Парамет- ры, которое открывается по команде Сервис → Параметры → Междуна- родные: 1.6. Особенности интерфейса После запуска Microsoft Excel на Рабочем столе Windows появляется ос- новное окно этого приложения. По умолчанию в нем открывается единствен- ное окно документа, в котором отображается вновь созданная рабочая Книга1 с тремя рабочими листами – Лист1, Лист2 и Лист2. На рис. 1.1 представлено основное окно приложения Microsoft Excel, в котором одновременно ведется обработка трех рабочих книг. Основное окно Microsoft Excel является типичным окном многодоку- ментного приложения (MDI – Multiple Document Interface). В нем имеется за- головок (1), основное меню (2), панели инструментов (3), дочерние окна от- крытых документов (5, 6, 15) и т.д. – т.е. все, что необходимо для одновремен- ной работы с несколькими документами. Особенностью же этого окна являет- ся наличие в нем двух элементов управления: 1) строки (панели) формул и 2) контекстного меню Строки состояния. 19 1. Заголовок окна приложения 2. Основное меню приложения 3. Панели инструментов Стандартная и Форматирование 4. Строка (панель) формул 5. Окно активного документа 6. Окно неактивного документа 7. Панель задач Создание книги 8. Поле редактирования со списком Задать вопрос 9. Помощник по офису 10. Панель задач Microsoft Windows 11. Строка состояния 12. Кнопки документов запущенных приложений на Панели задач Mi- crosoft Windows |