Конспект лекций по Excell. 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12. Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно
 Скачать 3.95 Mb.
|
|
Отменить на панели инструментов Стандартная. 56 2. Для того чтобы заменить формулы их значениями в диапазоне ячеек, мож- но выполнить описанную выше процедуру для каждой ячейки в диапазоне отдельно. Однако более продуктивный способ состоит в копировании ре- зультатов вычислений формул «по месту» (или «в себя»), т.е.: а) выделить диапазон, в ячейках которого требуется заменить формулы их значениями; б) скопировать в буфер обмена выделенный диапазон ячеек любым извест- ным способом – например, по команде Копировать из контекстного меню, которое активизируется щелчком правой кнопки мыши в преде- лах выделенного диапазона; в) вставить из буфера обмена только значения: а) щелкнув по кнопке со стрелкой направленной вниз рядом с кнопкой Вставить на панели инструментов Стандартная, и выбрав, за- тем, из раскрывшегося меню пункт Значения, или б) выполнив команду Вставить из контекстного меню, а затем щелкнув по появившейся рядом с выделенным диапазоном кнопке Пара- метры вставки, и в раскрывшемся меню выбрав пункт Только зна- чения; в) активизировав окно диалога Специальная вставка по одноименной команде: • основного меню Правка, или • контекстного меню, или • меню, раскрывающегося по щелчку на кнопке со стрелкой на- правленной вниз рядом с кнопкой Вставить на панели инст- рументов Стандартная, 57 в котором переключатель Вставить уставить в положение значения, и щелкнуть по кнопке OK. 3. Для того чтобы заменить подвыражение (часть формулы) его значением необходимо: а) выделить в формуле полное (законченное) подвыражение, которое необ- ходимо заменить его значением; б) выполнить ручной пересчет выделенного подвыражения – нажав клави- шу F9. В результате выделенное подвыражение в формуле будет заме- нено его значением; в) закончить редактирование с сохранением полученного результата – на- пример, нажав клавишу Enter. 3.5. Обработка ошибок Поскольку электронные таблицы используются, в основном, для обработки сравнительно больших объемов данных (меньшие, просто, можно обработать и в уме, или с помощью карандаша и бумаги), то периодическое появление различного рода ошибок в этом процессе скорее является правилом, чем исключением. Для их локализации, визуализации и исправления в Microsoft Excel предусмотрено несколько специальных воз- можностей, а также способов и приемов их выполнения. Автоисправление формул Это встроенное в Microsoft Excel средство исправления ошибок в формулах. Если в формуле обнаружена ошибка, и Excel предполагает, что может ее исправить, то выдается соответствующее окно сообщения с предложением исправить формулу. Это предложение можно как принять, так и отвергнуть. Например, если при вводе формулы =(A1+A2)/2 допущена ошибка и в конце формулы введена лишняя закрывающаяся скобка, то Excel предложит ее убрать: Но так просто «справиться» с ошибкой удается далеко не всегда. 58 Отображение формул По умолчанию в ячейках рабочего листа отображаются результаты вычисления по формулам, а не сами формулы. Отобразить содержимое ячеек с формулами с целью их просмотра, по- иска ошибок или исправления можно двумя способами: 1. По одной – выделяя требуемую ячейку с формулой, например, щелкая по ней мышью. При этом формула, записанная в ячейку, будет отображаться в поле редактирования строки формул. Таким образом, можно просматривать не только формулы, но и, вообще, любое содержание ячейки, например, не полностью отображаемое символьное значение из-за слишком маленькой ее ширины. Этот способ имеет один существенный недостаток – одновре- менно можно просматривать только одну формулу. 2. Все вместе – когда одновременно отображаются все формулы (а не значения) имеющиеся на рабочем листе. Переключение между режи- мом отображения формул, или значений, на рабочем листе выполняется двумя способами: а) по команде Сервис → Зависимости формул → Режим проверки формул, б) с помощью флажка формулы в области Параметры окна на вкладке Вид в окне диалога Параметры, которое активизируется по команде Сервис → Параметры. Фрагменты одного и того же рабочего листа в режиме отображения значений и формул приведены на рисунке далее: Проверка формул на наличие ошибок в Microsoft Excel выполняется на основании установленных параметров и правил на вкладке Проверка оши- бок в окне диалога Параметры. Она может быть реализована двумя различ- ными способами: 1. В фоновом режиме – т.е. во время простоев Microsoft Excel. Внешне это выглядит, как будто проверка формул на наличие ошибок выполняется не- медленно по ходу работы на листе, т.е. сразу, после завершения ввода. По умолчанию этот режим включен, а ячейки с формулами, содержащими 59 ошибки, помечаются маленьким зеленым треугольником в левом верхнем углу – Для исправления такой ошибки необходимо: а) выделить ячейку с треугольником в левом верхнем углу; б) щелкнуть по появившейся рядом с ячейкой кнопке ; в) в раскрывшемся в результате меню выбрать требуемое действие. Для разных видов ошибок состав пунктов этого меню различен, за исключением двух – первого, который описывает обнаруженную ошиб- ку, и последнего – Показать панель аудита формул – отображающего па- нель инструментов Зависимости, которая используется для наглядного представления с помощью разноцветных линий со стрелками взаимосвязей между ячейками. Назначение же остальных пунктов этого меню, которые меняются в зависимости от характера ошибки, приведено далее – при опи- сании следующего способа обнаружения ошибок. 2. По явному указанию пользователя – при помощи окна диалога Кон- троль ошибок, которое можно активизировать: а) по команде Сервис Проверка наличия ошибок, или б) щелкнув по кнопке Проверка наличия ошибок на панели инстру- ментов Зависимости. И в случае обнаружения какой-либо ошибки раскрывается окно диалога Контроль ошибок, элементы управления которого выполняют такие функ- ции: 1) в области: •••• Ошибка в ячейке отображается адрес и содержание ячейки с формулой, в которой обнаружена ошибку. 60 •••• Ошибка приводится краткое описание обнаруженной ошибки. 2) Кнопки: •••• Справка по этой ошибке активизирует справочную систему Microsoft Excel с подробным описанием ошибки. На разных этапах работы с ошибкой эта кнопка может менять свое название, например, на Про- должить или Трассировка пустых ячеек. •••• Продолжить служит для продолжения работы с текущей ошибкой по- сле временного переключения на выполнение других задач, таких, на- пример, как получение более подробной справки, или изменения со- держимого ячейки. •••• Трассировка пустых ячеек отображает линии со стрелками красного цвета, указывающие на пустые ячейки, которые используются в формуле. •••• Источник ошибки отображает линию со стрелкой красного цвета от текущей ячейки к ячейке, возможно, являющейся причиной ошибки. •••• Пропустить ошибку пропускает текущую ошибку и переходит к сле- дующей. При повторных проверках эта ошибка будет пропускаться до тех пор, пока не будут «восстановлены» все пропущенные таким обра- зом ошибки по нажатию кнопки Сброс пропущенных ошибок на вкладке Проверка ошибок в окне диалога Параметры. •••• Изменить в строке формул вызывает переход в режим редактирова- ния (просмотра и изменения) содержимого текущей ячейки. •••• Далее переход к обработке следующей ошибки. •••• Назад переход к обработке предыдущей ошибки. •••• Параметры вызывает активизацию окна диалога Параметры с единственной вкладкой Проверка ошибок, на которой выполняется настройка параметров и правил проверки ошибок в Microsoft Excel. Эту же вкладку можно активизировать и традиционным способом – по ко- манде Сервис → Параметры → Проверка ошибок. 61 Настройка правил проверки ошибок в формулах Microsoft Excel в окне диалога Параметры на вкладке Проверка ошибок выполняется в двух ее об- ластях с помощью соответствующих элементов управления (в основном флаж- ков) следующим образом: 1. Параметры – служит для установки общих параметров проверки ошибок с помощью таких элементов управления: •••• Флажок Включить фоновую проверку ошибок активизирует режим фоновой проверки ячеек с формулами на наличие ошибок немедленно после завершения ввода в соответствии с установленными ниже пра- вилами. Если в ячейке обнаружена ошибка, то она помечается в левом верхнем углу маленьким треугольником, цвет которого устанавливает- ся с помощью расположенного ниже раскрывающегося списка Цвет отображения ошибок. •••• Раскрывающийся список Цвет отображения ошибок позволяет уста- новить цвет, которым в Microsoft Excel будут помечаться ошибки. Ес- ли выбрано значение Авто, будет использоваться установленный по умолчанию зеленый цвет. •••• Кнопка Сброс пропущенных ошибок сбрасывает в исходное состоя- ние все найденные ранее и отмеченные как пропущенные ошибки на текущем рабочем листе. 2. Проверять наличие следующих ошибок – служит для включения провер- ки наличия ошибки при помощи установки соответствующего флажка и, в случае обнаружения ошибки, помечать как ошибочную ячейку с формулой: •••• Флажок вычисление формулы вызывает ошибку – когда Microsoft Excel не может выполнить обработку формулы и вместо ре- зультата генерирует сообщение об ошибке, которое записывается в ак- тивную ячейку в виде одной из предопределенных констант. Все такие константы обязательно начинается со знака «#». В Microsoft Excel имеются следующие значения обнаруживаемых ошибок: 1) ##### – числовой результат формулы по ширине не помещается в ячейке, 2) #ЗНАЧ! – недопустимый тип параметра или операнда, 3) #ДЕЛ/0! – деление на нуль, 4) #ИМЯ? – недопустимое имя, 5) #Н/Д – недопустимые данные, 6) #ССЫЛКА! – недопустимый адрес ячейки, 7) #ЧИСЛО! – недопустимые числовые данные, 62 8) #ПУСТО! – ошибочная ссылка на ячейку или диапазон. Более развернутую информацию о причине возникновения каждой конкретной ошибки на основании ее значения можно получить из справочной системы. Если ввести в ячейку непосредственно одно из значений ошибки, эта ячейка все рано не будет помечена как содержащая ошибку. •••• Флажок текстовая дата с 2-значным годом – когда текстовый формат представления даты содержит две цифры года, и поэтому он потенци- ально может быть отнесен к неверному веку. Например, =ГОД("25.02.03") или '25.02.03. •••• Флажок число сохранено как текст – когда числовые величины пред- ставлены в текстовом формате, например, '25. Обычно такая ситуация возникает вследствие импорта данных из других источников и может повлечь не правильный порядок сортировки такой информации. Поэто- му подобные данные иногда лучше все-таки преобразовать в числовой тип. •••• Флажок несогласующаяся формула в области – когда она не соответ- ствует образцу других смежных формул. В большинстве случаев это значит, что ссылки в формуле не соответствуют ссылкам, которые ис- пользуются в формулах смежных ячеек. Например, формулы суммиро- вания в строках, которые расположены в ячейках одного столбца, должны, как правило, отличаться только на фиксированный шаг в «ко- ординатах» строк. •••• Флажок формула не охватывает смежные ячейки – когда она не ох- ватывает все смежные непустые ячейки некоторого диапазона обработ- ки. Наиболее часто такая ошибка фиксируется при заполнении смеж- ных с диапазоном обработки ячеек данными уже имеющегося типа. •••• Флажок не заблокирована ячейка, содержащая формулу – когда она разблокирована, т.е. сброшен флажок Защищаемая ячейка на вкладке Защита окна диалога Формат ячеек. Защита ячеек, содержащих фор- мулы, предохраняет их от непреднамеренного изменения и помогает избежать многих ошибок в будущем. Защита для заблокированных ячеек рабочего листа устанавливается по команде Сервис → Защита → Защи- тить лист. По умолчанию все ячейки на рабочем листе заблокированы. •••• Флажок формула ссылается на пустые ячейки – когда она содержат ссылки на пустые ячейки. Используется, например, для выявления та- кой трудно обнаруживаемой ошибки, как вычисление среднего ариф- метического диапазона ячеек, некоторые из которых пустые, а некото- 63 рые – содержат 0, но согласно установленному формату этот 0 не ото- бражается, и ячейки внешне выглядят как пустые. 3.6. Зависимости ячеек В Microsoft Excel имеется возможность проследить зависимости между ячейками посредством их наглядного представления с помощью разноцветных линий со стрелками. Это полезно как для выявления ошибок, так и для более эффективной работы с данными. Ячейка с формулой, данные которой зависят от других ячеек, называется зависимой. А ячейки, данные которых влияют на содержимое активной ячей- ки, называются влияющими. Если изменить данные во влияющей ячейке, то результат вычисления по формуле в зависимой ячейке также изменится. Но прежде чем отображать связи между ячейками, необходимо чтобы переключатель Объекты на вкладке Вид диалогового окна Параметры был установлен в положение отображать или только очертания. Наиболее удобно управлять отображением (или скрытием) стрелок меж- ду влияющими и зависимыми ячейками с помощью кнопок на панели инстру- ментов Зависимости, которую можно активизировать: 1) по команде Сервис → Зависимости формул → Панель зависимостей, или 2) как обычно – выставив «галочку» против ее названия в списке панелей ин- струментов, который раскрывается: а) по команде Вид → Панели инструментов, или б) по щелчку правой кнопкой мыши в области меню, или любой другой открытой панели инструментов. Кнопки на панели инструментов Зависимости имеют следующие назва- ния и выполняют такие функции: Проверка наличия ошибок – запускает проверку ошибок на текущем ра- бочем листе по явному указанию пользователя при помощи окна диалога Контроль ошибок. Влияющие ячейки – отображает стрелки от активной ячейки с формулой к влияющим ячейкам. Повторное нажатие увеличивает глубину трассировки на один уровень. 64 Убрать стрелки к влияющим ячейкам – уменьшает глубину трассировки на один уровень, удаляя стрелки самого нижнего уровня от зависимой ячейки к влияющим. Зависимые ячейки – отображает стрелки от активной ячейки к зависимым. При повторном нажатии глубина трассировки увеличивается на один уровень. Убрать стрелки к зависимым ячейкам – уменьшает глубину трассировки на один уровень, удаляя стрелки самого нижнего уровня от активной ячей- ки к зависимым. Убрать все стрелки – удаляет все стрелки зависимостей на рабочем листе. Источник ошибки – отображает стрелку от активной ячейки к ячейке, воз- можно, являющейся источником ошибки. Для выявления ошибки необхо- димо чтобы текущая ячейка была помечена как содержащая ошибку. Создать примечание – создает примечание для активной ячейки. Обвести неверные данные – обводит овалами красного цвета все ячейки с данными, которые выходят за рамки ограничений, установленных по ко- манде Данные → Проверка. Удалить обводку неверных данных – отменяет обводку ячеек с данными, которые выходят за рамки ограничений, наложенных с помощью команды Данные → Проверка. Показать окно контрольного значения – отображает (убирает) панель инструментов Окно контрольного значения. Вычислить формулу – отображает окно диалога Вычисление формулы для пошагового вычисления формулы, записанной в активную ячейку. Фрагмент рабочего листа со стрелками, указывающими зависимости между ячейками, приведен на следующем ниже рисунке. Стрелками синего цвета отображаются связи между ячейками в преде- лах текущего рабочего листа. Стрелки красного цвета соединяют ячейку, со- держащую ошибку, с возможной ее причиной. Для выявления такой связи необходимо: 1. Щелкнуть по ячейке с ошибкой для ее активизации. 2. На панели инструментов Зависимости нажать кнопку: г) Источник ошибки или д) Влияющие ячейки. Связи ячейки с внешними данными обозначаются с помощью прерыви- стой черной линии, а сами объекты отображаются в виде маленьких таблиц. 65 С помощью двойного щелчка по стрелке можно перемещаться вдоль пу- ти, помеченного стрелками. Отображать (или убирать) стрелки между влияющими и зависимыми ячейками можно также с помощью меню, которое раскрывается по команде Сервис → Зависимости формул, как показано на рисунке далее: Пункты этого меню имеют аналогичные с кнопками панели инструментов Зависимости функции и названия. |