Конспект лекций по Excell. 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12. Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно
 Скачать 3.95 Mb.
|
|
Готово, любым известным способом, например, по нажатию клавиши Enter. Для выполнения замены формулы результатом, вычисленным по этой форму- ле, для нескольких ячеек эту процедуру необходимо повторить для каждой из них. В случае же замещения формул их значениями для большого количества ячеек, тоже самое гораздо быстрее можно выполнить с помощью специальной вставки по месту исходного блока ячеек. 4.3. Очистка и удаление ячеек Под очисткой в Microsoft Excel понимается удаление либо всего, что находится в ячейке, либо только отдельных компонентов (или содержимого, 85 или форматов, или примечаний) без сдвига содержимого других ячеек рабоче- го листа. Под удалением же понимается полное удаление ячеек со сдвигом соседних ячеек на место удаленных. Очистить ячейки можно двумя способами: 1) с помощью меню, 2) с помощью клавиатуры. Для того чтобы очистить содержимое одной или нескольких ячеек с помощью меню необходимо: 1. Выделить подлежащие очистке ячейки. 2. С помощью команды Очистить из основного меню Правка, или из контекстного меню, выбрать необходимый вариант очистки. При этом с помощью команд основного меню можно выполнить любой, из предусмотренных, способов очистки, а с помощью контекстного – только ограниченный набор, наиболее часто используемые из них. Так, например, с помощью контекстного меню можно очистить только содержимое, или при- мечания. В последнем случае соответствующий пункт доступен, только если в ячейке есть примечание. Очистку с помощью клавиатуры одной, или нескольких, предвари- тельно выделенных ячеек, можно выполнить: 1) по нажатию клавиши Del, 2) по нажатию сочетания клавиш Shift + Del. В первом случае будет удалено только содержимое, а во втором – полная очи- стка. При этом необходимо иметь в виду, что во втором случае все, что было в ячейке, будет перенесено в буфер обмена, из которого его можно будет, затем, вставить (преднамеренно, или нечаянно). Удаление одной, или нескольких, предварительно выделенных, ячеек выполняется с помощью диалогового окна Удаление ячеек, которое можно активизировать по команде Удалить из контекстного или основного меню Правка. При этом необходимо уточнить вариант удаления – со сдвигом соседних ячеек влево или вверх, удалять ли целиком строки или столбцы, содержащие выделенные ячейки. 86 Наиболее просто добавить ячейки можно по команде Добавить ячейки из контекстного меню. В раскрывшемся в результате окне диалога Добавление ячеек уточнить параметры выполнения операции (добавить целую строку, или столбец, или добавить ячейку, со сдвигом уже имеющихся вниз, или вправо). 4.4. Форматирование и защита информации Профессионально оформленный внешний вид рабочего листа облегчает восприятие данных. Содержащаяся в ячейках Microsoft Excel информация мо- жет быть отображена несколькими различными способами. Для изменения способа отображения, а также доступа, а в некоторых случаях и способа хра- нения информации, в нем предусмотрены различные средства форматирова- ния и защиты. Обычно Excel пытается самостоятельно распознать тип вводи- мой информации, и выбрать, предусмотренный по умолчанию, способ ее представления (отображения) – формат. В большинстве случаев это не вызы- вает никаких недоразумений. Например, если при вводе числа поставить после него обозначение денежной единицы (например, грн.), то такой информации автоматически присвоится формат отображения Денежный, а если знак про- цента (%) – Процентный. Но иногда незнание некоторых деталей может при- вести к неожиданным результатам. Так, например, если в Windows в качестве символа-разделителя элементов списка установлена точка (.), а в некоторой ячейке установлен соответствующий формат отображения дат, то при попытке ввести в нее натуральное число одна целая и одна десятая – 1.1, Excel, в неко- торых, случаях может интерпретировать это как 1 Январь, 2001. Форматирование информации, т.е. изменение способа ее отображения, зависит от объекта форматирования – ячейка, строка, столбец и т.д. Все функ- ции форматирования, для всех объектов Microsoft Excel, вызываются через ос- новное меню Формат. 87 Наиболее же часто используемые из них в виде раскрывающихся спи- сков и кнопок выведены на панель инструментов Форматирование – они всегда должны «быть под рукой». 1. Раскрывающийся список Шрифт 2. Поле редактирования раскрывающе- гося списка Размер 3. Кнопка Полужирный 4. Кнопка Курсив 5. Кнопка Подчеркнутый 6. Кнопка По левому краю 7. Кнопка По центру 8. Кнопка По правому краю 9. Кнопка Объединить и поместить в центре 10. Кнопка Денежный формат 11. Кнопка Процентный формат 12. Кнопка Формат с разделителями 13. Кнопка Увеличить разрядность 14. Кнопка Уменьшить разрядность 15. Кнопка Уменьшить отступ 16. Кнопка Увеличить отступ 17. Кнопка раскрывающегося списка Границы 18. Кнопка раскрывающегося списка Цвет заливки 19. Кнопка раскрывающегося списка Цвет шрифта 20. Кнопка Параметры панелей инст- рументов Рис. 4.1 – Панель инструментов Форматирование При этом важно подчеркнуть, что форматирование изменяет лишь спо- соб отображения, а не содержимое ячейки. В чем легко можно убедиться, если выделить ячейку, то ее содержимое будет отображаться в поле редак- тирования строки формул. Различные примеры отображения одной и той же информации в разнообраз- ных способах ее представления – форматах – будут приведены далее. Форматирование ячеек Поскольку в Microsoft Excel ячейка является основной «хранительни- цей» информации, то среди других объектов форматирования она, естествен- но, обладает наиболее богатым набором возможностей форматирования. С точки зрения применяемых средств и приемов форматирования, ячейка в Microsoft Excel очень похожа на абзац в текстовом документе. Наиболее полно реализовать все возможности по форматированию ячеек можно с помощью диалогового окна Формат ячеек, которое активизируется по команде: 1) Формат → Ячейки из основного меню, или 1 2 3 5 7 9 11 13 15 17 19 4 6 8 10 12 14 16 18 20 88 2) Формат ячеек из контекстного меню. Окно диалога Формат ячеек состоит из 6-ти вкладок: 1. Число, 2. Выравнивание, 3. Шрифт, 4. Граница, 5. Вид, 6. Защита, которые используются для настройки соответствующих групп параметров форматирования. Вкладка Число окна диалога Формат ячеек служит для управления спо- собом отображения числовых величин. А поскольку основной тип информа- ции, содержащейся в ячейках Microsoft Excel, конечно же – числовой, то для его форматирования предусмотрен самый широкий набор таких возможностей – от готовых форматов, до возможности создания оригинальных пользова- тельских представлений. В списке Числовые форматы, который расположен в левой части окна диалога, приведены все, имеющиеся в Microsoft Excel, ка- тегории форматов. При этом некоторые из них могут включать несколько фор- матов, в то время как другие – только один. Содержание же правой части вкладки зависит от выбранной категории в списке Числовые форматы. Од- нако в ее верхней части, в групповой рамке Образец, всегда отображается, как будет выглядеть содержание непустой (!) ячейки после применения к ней вы- бранного формата. 89 Список Числовые форматы состоит из следующих категорий форматов: Общий. Применяется ко всем ячейкам по умолчанию. То есть все, неис- пользовавшиеся ранее, или полностью очищенные ячейки, имеют формат Общий. Он предполагает выравнивание текстовых данных по левому краю ячейки, числовых – по правому, а логических значений и сообщений об ошибках – по центру. Для числовых величин отображается не более 11 символов, без учета знака числа. Хотя, независимо от количества отобра- жаемых разрядов, числа всегда хранятся с точностью до 15 разрядов. При этом если ширины ячейки не хватает для отображения всех знаков числа, оно сначала отображается в экспоненциальном представлении. А если ши- рины ячейки все равно не хватает, даже для отображения в экспоненциаль- ном формате (помещается менее 5-ти символов), то вместо значения ото- бражается ошибка – ячейка, заполненная символом «#». На рис. 4.2 приве- дены различные способы отображения одного и того же числа при посте- пенном уменьшении ширины ячейки. Рис. 4.2 – Отображение числа 25000000 при постепенном уменьшении ширины ячейки При этом содержимое поля редактирования в строке формул остается не- изменным – 25000000 – т.к. значение ячейки при изменении параметров форматирования не меняется. Числовой. Применяется, если необходимо задать конкретные детали ото- бражения числа, такие как: а) число десятичных знаков в дробной части, б) использование символа-разделителя групп разрядов, а также в) способ вывода отрицательных чисел. 90 Денежный. Используется для отображения денежных величин. В нем можно указать: а) число отображаемых десятичных разрядов в дробной части, б) обозначение денежной единицы, а также в) способ вывода отрицательных чисел (например, без знака минус «-», или другим цветом, а также способ горизонтального выравнивания). Финансовый. В основном такой же, как и Денежный. Используется для выравнивания при выводе денежных величин по разделителю целой и дроб- ной части. На рисунке, приведенном далее, при установленном выравнива- нии содержимого ячеек A2:B4 по левому краю, содержимое ячеек, для ко- торых установлен формат Финансовый (B2:B4), все равно выполняется по символу-разделителю целой и дробной части числа. 91 Поскольку содержимое ячейки (A2) не зависит от способа его отображения (10.20 грн.), то в поле редактирования строки формул всегда отображается ее значение (10.2). Дата. Служит для отображения дат. Конкретный же способ отображения выбирается из списка Тип. Время. Используется для отображения времени суток, конкретный способ которого выбирается их списка Тип. Некоторые форматы отображения в списке Тип для значений Дата и Время из списка Числовые форматы совпадают. Чтобы Microsoft Excel правильно распознавал вводимую дату или время, они должны быть пред- ставлены в одном из имеющихся в списке Тип форматов. В этом случае 92 Excel установит формат ячейки, соответствующий вводимой дате или вре- мени. Например, после завершения ввода в ячейку значения 1.3 12:00, в ней будет отображено что-нибудь типа 01.03.2003 12:00:00 – конкретный фор- мат представления даты и времени зависит от региональных стандартов Microsoft Windows. Если же для этой ячейки установить затем формат Об- щий, то в ней будет отображаться число 37681.5 – именно сколько дней (37681) прошло с 1 января 1900 года, а 12:00:00 – это ровно 0.5 суток. Процентный. Значение ячейки умножается на 100, и выводится на экран вместе с символом процента (%). Дробный. Значение ячейки отображается в виде обыкновенной дроби, спо- соб вывода которой установлен в поле Тип. Экспоненциальный. Используется, в основном, для отображения, или дос- таточно больших, или, наоборот, достаточно малых, чисел. Число выводит- ся в виде произведения двух составляющих – мантиссы, которая находится в диапазоне 1 ÷ 10, и порядка – показателя степени числа 10, разделенных символом E (или e). Например, число 123 в экспоненциальном представле- нии будет иметь вид 1.23E+02 (т.е. 1.23*10 2 ). 93 Текстовый. Применяется для того, чтобы некоторые числовые данные, та- кие, например, как код товара, интерпретировались как текст. Дополнительный. Служит для отображения числовых величин специаль- ного назначения, таких, например, как телефонные номера, почтовые ин- дексы, табельные номера сотрудников и др. Как было отмечено ранее, формат ячейки и ее содержание – это две раз- ные вещи, из чего вытекает одно, очень важное, следствие – при округлении 94 чисел не всегда достаточно ограничиваться только указанием соответст- вующего формата. В некоторых случаях, если не учитывать это следствие, можно столкнуться с так называемой «проблемой округления». Например, если в ячейке A1 записано число 10.3, в ячейке A2 – 20.4, а в ячейке A3 вычисляет- ся их сумма – СУММ(A1:A2), и для всех этих ячеек установлен формат с ок- руглением до целых, то в ячейке A3 будет отображаться число 31, вместо ожи- даемого 30. Такой «неожиданный» результат является следствием того, что в вычислениях участвуют значения, хранимые в ячейках, а не представления, отображаемые по определенному формату. Наглядная демонстрация этого примера представлена на рис. 4.3, где один и тот же фрагмент таблицы показан при установленном формате Общий (а) и Числовой с округлением до целых (б). Рис. 4.3 – Фрагмент таблицы при установленных форматах: (а) – Общий и (б) – Числовой с округлением до целых Предотвратить такое «неконтролируемое со стороны пользователя ок- ругление» можно двумя способами: 1) с помощью функции ОКРУГЛ(), и 2) установив флажок точность как на экране в области Параметры книги на вкладке Вычисления окна диалога Параметры. Каждое из этих решений имеет как свои достоинства, так и определен- ные недостатки, а отсюда – и свою область наиболее предпочтительного при- менения. Использование функции ОКРУГЛ() является очень гибким решени- ем, но требует довольно много дополнительного ввода с клавиатуры, что, в свою очередь, является еще одним источником потенциальных ошибок. На- пример, в приведенном выше примере в ячейку A3 следовало бы записать формулу =СУММ(ОКРУГЛ(A1,0)+ОКРУГЛ(A2,0)). Понижение же точности вычислений с внутреннего представления (до 15 знаков после запятой) до точ- ности, используемой при отображении ячеек на экране, с помощью флажка точность как на экране осуществляется довольно просто. Но такое решение имеет один, весьма существенный недостаток – после понижения точности внутреннего представления, до точности отображения на экране обратный а ) б ) 95 процесс, т.е. повышение точности отображения до точности внутреннего представления, не возможен для всех констант, на всех рабочих листах, те- кущей рабочей книги. (все форматы). Служит для просмотра, создания и применения как стан- дартных (встроенных) форматов Microsoft Excel, так и нестандартных – пользовательских. Для просмотра любого формата необходимо: 1. С помощью списка Числовые форматы (и, если необходимо, – Тип) указать требуемый формат. 2. В списке Числовые форматы выбрать значение (все форматы). В результате в поле редактирования Тип будет отображаться «содержимое» выбранного формата. Создание нового, или изменение уже существующего, пользовательско- го формата выполняется в поле редактирования Тип. При этом если в этом поле изначально отображался один из стандартных форматов Microsoft Excel, то он подвергаться изменениям не будет – вместо этого будет создана его ко- пия, которая и будет, затем, редактироваться. Все пользовательские форматы сохраняются вместе с рабочей кни- гой, в которой они были созданы. Для удаления любого, более не нужного, пользовательского формата необходимо: 1. Выделить его в списке Тип. 2. Щелкнуть по кнопке Удалить. После удаления формата, все ячейки отформатированные с его применением, будут иметь установленный по умолчанию формат Общий. Удалить встроен- 96 ные форматы Microsoft Excel не возможно – при выборе любого из них стано- вится недоступной кнопка Удалить. Любой формат Microsoft Excel (и встроенный, и созданный пользователем) может состоять не более чем из 4-х секций, разделенных точкой с запятой (;): <секция1>;<секция2>;<секция3>;<секция4>. По умолчанию первая секция служит для указания способа отображения положительных чисел, вторая – отрицательных, третья – нулевых, а четвертая – текста. Если формат содержит только две секции, то первая из них использу- ется для отображения положительных и нулевых значения, а вторая – отрица- тельных. Если же формат содержит единственную секцию, то она использует- ся для вывода всех чисел. Секция для отображения текстовых значений всегда должна быть четвертой. При этом если какая-либо секция пропускается (кроме последней, 4-й секции), то вместо нее в формате ставится точка с запятой (;). Каждая секция формата состоит из символов-шаблонов, с помощью ко- торых формируется маска отображения содержимого ячейки. Все допустимые в Microsoft Excel символы-шаблоны, а также их назначение приведены в табл. 4.1. Таблица 4.1 – Назначение символов-шаблонов отображения Информации в ячейках Microsoft Excel Шаблон (маска) Отображает Основной Отображает содержимое ячейки в неформатированном виде, т.е. в том виде, в котором данные были введены. .(,) Символ-разделитель целой и дробной части числа. Зависит от региональных стандартов операционной системы Windows. При этом если дробная часть чис- ла содержит цифр больше, чем формат содержит символов-шаблонов, число округляется так, чтобы количество разрядов соответствовало количеству шаблонов. Если же целая часть числа содержит цифр больше, чем формат содержит символов-шаблонов, отображаются все значащие разряды. 0 Отображает цифру или 0 в данной позиции. Исполь- зуется, если необходимо, чтобы все числа состояли из одинакового количества цифр. Например, если ячейка содержит число 12.34, то по формату 0 оно будет ото- бражаться как 12, по формату .0 или 0.0 – как 12.3, по формату 0. – как 12., а по формату 000.000 – как 012.340. |