Конспект лекций по Excell. 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12. Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно

• Название: Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно
• Анкор: Конспект лекций по Excell
• Дата: 15.02.2022
• Размер: 3.95 Mb.
• Имя файла: 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12.pdf
• Тип: Конспект лекций; #363174
• страница: 8 из 24

Циклические ссылки
Циклической ссылкой называется последовательность ссылок, при кото- рой формула прямо или косвенно, ссылается сама на себя. Используя обычные вычисления, Microsoft Excel, в большинстве случаев, не может правильно обработать формулы с циклическими ссылками.

66
Однако некоторые научные и инженерные расчеты требуют формул, в
которых используются циклические ссылки. Microsoft Excel не может
автоматически обрабатывать формулы во всех открытых рабочих книгах,
если хотя бы одна из них содержит циклическую ссылку.
Наиболее наглядно проследить циклические ссылки можно с помощью панели инструментов Циклические ссылки, которая активизируется двумя способами:
1.
Автоматически – при записи в ячейку формулы, «замыкающей» цикличе- скую ссылку. При этом появляется окно сообщения следующего вида:
2.
Вручную – выставив «галочку» против названия Циклические ссылки в списке панелей инструментов, который можно отобразить, щелкнув, на- пример, правой кнопкой мыши в области меню, или любой другой откры- той панели инструментов.
В результате активизации панели инструментов Циклические ссылки стрелки слежения укажут на все ячейки, образующие цикл. Обходить ячейки в цикле с целью переопределения формул, или логики вычислений, и таким образом, возможно, разорвать его можно двумя способами, с помощью:
1)
раскрывающегося списка адресов, образующих циклические ссылки,
Найти циклическую ссылку на панели инструментов Циклические
ссылки, или
2)
двойного щелчка по стрелкам между ячейками, образующими цикл.
До тех пор, пока существует хотя бы одна циклическая ссылка, в строке со- стояния Microsoft Excel будет отображаться слово Цикл, с адресом ячейки, послужившей причиной его образования. Если слово Цикл отображается без ссылки, то данная циклическая ссылка содержится не на текущем рабочем листе.
Окно контрольного значения
Это средство, которое позволяет наблюдать на одноименной панели ин- струментов за формулами и значениями ячеек даже тогда, когда они не ото- бражаются или не являются активными. Панель инструментов Окно кон-
трольного значения построена в виде таблицы, в которой параметры каждой

67 наблюдаемой ячейки представляют отдельную строку. Она, как и любая дру- гая панель инструментов, может быть «пристыкована» к одной из сторон ос- новного окна приложения или находиться в «плавающем» состоянии, как по- казано на рисунке далее.
В каждой строке таблицы на панели инструментов Окно контрольного
значения отображаются следующие параметры ячейки:
1)
название книги,
4) адрес ячейки,
2)
название листа,
5) значение,
3)
имя ячейки,
6) формула.
Для того чтобы добавить строку в эту таблицу необходимо:
1.
Щелкнуть по кнопке Добавить контрольное значение на панели инстру- ментов Окно контрольного значения.
2.
В поле редактирования Выберите ячейки, значения которых нужно про-
смотреть раскрывшегося при этом окна диалога Добавление контрольно-
го значения указать ссылку на требуемую ячейку.
Наиболее просто и быстро ссылку на ячейку можно указать, щелкнув по ней мышью. Выделив при этом не одну ячейку, а один, или несколько, блоков можно одновременно добавить несколько строк в таблицу. По умолчанию в окне диалога Добавление контрольного значения отобра- жается адрес активной ячейки, или предварительно выделенного диапазона.
3.
Щелкнуть по кнопке Добавить.
Для удаления же строк из таблицы необходимо:
1.
Выделить их в таблице.
2.
Щелкнуть по кнопке Удалить контрольное значение на панели инстру- ментов Окно контрольного значения.

68
Двойной щелчок в пределах строки с информацией о параметрах ячейки
вызывает ее активизацию и отображение.
Панель инструментов Окно контрольного значения можно активизи- ровать одним из способов:
1.
По команде Сервис → Зависимости формул → Показать окно кон-
трольного значения.
2.
Выставив «галочку» против ее названия в списке панелей инструментов, который раскрывается по щелчку правой кнопкой мыши в области меню, или любой другой видимой панели инструментов, или по команде Вид →
Панели инструментов.
3.
щелкнув по кнопке
Показать окно контрольного значения на панели инструментов Зависимости.
Пошаговое вычисление сложных формул позволяет просмотреть зна- чения различных частей сложной формулы, вычисляемые в соответствии с приоритетом операций. Для того чтобы воспользоваться этим средством, на- пример, для пошагового вычисления формулы =ОКРУГЛ(ПИ()*A1^2,A2), за- писанной в ячейке A3, необходимо:
1.
Выделить ячейку с формулой (A3), которую необходимо вычислить в пошаговом режиме.
За один раз можно проанализировать только одну ячейку с формулой.
2.
Активизировать окно диалога Вычисление формулы:
а)
выполнив команду Сервис → Зависимости формул → Вычислить
формулу, или
б)
щелкнув по кнопке
Вычислить формулу на панели инструментов
Зависимости (если она, конечно, отображается).
3.
В раскрывшемся при этом окне диалога Вычисление формулы:
а)
после надписи Ссылка отображается адрес активной ячейки;
б)
после надписи Вычисление отображается содержимое ячейки, на которую указывает Ссылка. При этом последний полученный ре- зультат вычисления отображается курсивам, а компоненты выражения, которые будут вычислены на текущем шаге – подчеркнуты;
в)
в нижней части отображается состояние текущего шага вычислений, и допустимые при этом действия;
г)
кнопки:
Вычислить – служит для вычисления подчеркнутого подвыражения.
После завершения вычислений по формуле ее название меняется на
Заново.

69
Заново – служит для повторного вычисления формулы.
Шаг с заходом – доступна, если подчеркнутым подвыражением яв- ляется ссылка на ячейку.
Щелчок по ней вызывает:
1)
смену текущей ячейки на подчеркнутую,
2)
отображение (добавление) еще одного, более низкого, уровня ие- рархии выражении после надписи Ссылка, и
3)
отображение в поле Вычисление содержания текущей ячейки как показано на рисунке далее:
Кнопка Шаг с заходом недоступна для ссылки: появляющейся в фор-
муле повторно, и на ячейку в другой книге.

70
Шаг с выходом – доступна для всех уровней подвыражений в дереве выражения, кроме первого, и вызывает:
1)
вычисления текущего подвыражения,
2)
возврата на один уровень вверх,
3)
смену текущей ячейки, в соответствии с подвыражением, которое будет вычисляться на текущем шаге.
Закрыть – служит для закрытия окна диалога Вычисление формулы и возврата «фокуса ввода» в текущую ячейку.
3.7.
Пересчет формул
Пересчет – это процесс обработки формул и отображения возвращае- мых ими значений в ячейках, содержащих эти формулы. По умолчанию
Microsoft Excel автоматически пересчитывает все формулы во всех открытых рабочих книгах только при изменении значения ячеек, влияющих на эти фор- мулы. Такой способ пересчета позволяет избежать лишних вычислений. При открытии или сохранении рабочих книг также производится пересчет всех формул, которые в них имеются.
Если рабочая книга содержит большое число формул или рабочие листы содержат таблицы данных, а также автоматически вычисляемые функции, то процесс пересчета может затянуться на долгое время. Также процесс вычисле- ний может потребовать существенно больше времени, если рабочий лист со- держит связи с другими рабочими листами или книгами. Но процессом пере- счета формул можно управлять с помощью переключателя Вычисления на одноименной вкладке окна диалогового Параметры, выставив один из сле- дующих режимов:
автоматически (действующий по умолчанию),
автоматически кроме таблиц, или
вручную.
В последнем случае можно также установить (или сбросить) флажок пересчет
перед сохранением.
После установки режима ручного пересчета формул вычисление любой формулы будет выполняться только непосредственно после ее ввода или из- менения, или после выполнения специальной команды по пересчету. Для ини- циализации пересчета всех формул на всех рабочих листах всех открытых в данный момент рабочих книг необходимо нажать на кнопку Вычислить на вкладке Вычисления окна диалога Параметры, или более быстро – нажать клавишу F9.

71
В тех случаях, когда открытые в данный момент рабочие книги содер- жат много рабочих листов с большим количеством сложных формул, иногда используется более щадящий режим пересчета – только формулы активного рабочего листа. Этот режим пересчета инициализируется с помощью кнопки
Пересчет листа на той же вкладке того же диалогового окна, или более быстро – с помощью сочетания клавиш Shift + F9.
3.8.
Функции рабочего листа
Все встроенные (стандартные) функции Microsoft Excel сгруппированы в следующие основные категории:
1.
Финансовые,
2.
Дата и время,
3.
Математические,
4.
Статистические,
5.
Ссылки и массивы,
6.
Работы с базой данных,
7.
Текстовые,
8.
Логические,
9.
Проверки свойств и значений.
Эта группировка отображается в раскрывающемся списке Категория первого окна Мастера функций. В списке же Выберите функцию, отображается име- на функций, составляющих выделенную категорию. Самую подробную ин- формацию о каждой из них, ее параметрах и возвращаемом значении можно получить с помощью справочной системы Microsoft Excel.
Если нужная функция не доступна, следует установить надстройку
Пакет
анализа, а затем включить ее с помощью диспетчера надстроек.

72
ЛЕКЦИЯ № 4.
РЕДАКТИРОВАНИЕ И ФОРМАТИРОВАНИЕ
ЭЛЕКТРОННЫХ ТАБЛИЦ
Прежде, чем непосредственно приступить к редактированию и форматированию, как электронных таблиц, так и их компонентов, эти объекты предварительно необходимо выделить (пометить, маркировать, указать и т.д.).
4.1.
Выделение объектов
Способы выделения объектов, как и навигация по ним, зависят от типа, подлежащих выделению, объектов.
Выделение ячеек. Существует несколько способов (и их комбинаций) выделения ячеек:
1)
с помощью мыши,
2)
с помощью клавиатуры,
3)
в режиме выделения.
Наиболее простой и интуитивно понятный способ выделения прямо- угольного диапазона ячеек – с помощью мыши. Заключается он в перетаскивании (буксировке) указателя при нажатой левой кнопке из одного угла диапазона в противоположный. Во время перетаскивания указатель все время должен иметь форму большого белого креста ( ). Выделенный диапазон будет обведен более широкой темно-серой линией, а также изменит свой цвет, за исключением ячейки с которой началось выделение – она останется актив- ной. Заголовки строк и столбцов ячеек, входящих в выделенный диапазон, также изменяют свой цвет.
Для перемещения активной ячейки в следующий по часовой стрелке угол
выделенного диапазона служит сочетание клавиш Ctrl + «.» (точка).
Таким способом можно выделить диапазон любого размера.
При перетаскивании указателя мыши во время выделения через границу окна его содержимое будет прокручиваться в соответствующем направлении.
Следующий способ выделения прямоугольного диапазона ячеек заключа- ется в:
1)
однократном щелчке по любой угловой ячейке выделяемого диапазона,
2)
нажатии клавиши Shift, и
3)
не отпуская клавише Shift, однократном щелчке по ячейке в противополож- ном углу выделяемого диапазона.
При этом если ячейка, по которой необходимо произвести щелчок, не видна на экране, то ее необходимо предварительно отобразить с помощью

73 прокрутки рабочего листа в нужном направлении.
Выделить прямоугольный диапазон ячеек, относительно текущей, можно также с помощью клавиш-стрелок управления курсором, при нажатой клавише Shift. В этом способе для продолжения выделения при нажатой кла- више Shift можно использовать не только клавиши-стрелки управления кур- сором, но и другие клавиши перемещения курсора, а также все допустимые их сочетания.
Вместо постоянного удержания клавиши Shift во время расширения гра- ниц выделяемого диапазона можно временно перевести Excel в режим выде- ления. Для этого необходимо:
1.
Активизировать одну из угловых ячеек подлежащего выделению диапазона любым известным способом.
2.
Перевести Excel в режим выделения, нажав клавишу F8.
3.
Расширить границы выделяемого диапазона любым известным способом (с помощью мыши, клавиатуры и т.д.).
4.
Вернуть Excel в обычный режим работы по нажатию клавиши F8.
Режим выделения в строке состояния индицируется надпись «ВДЛ»; в
обычном режиме эта надпись отсутствует.
Выделение непрямоугольного диапазона производится путем выделе- ния составляющих его прямоугольных диапазонов, и отдельных ячеек, при нажатой клавише Ctrl. Отдельные поддиапазоны при этом будут обведены более широкой линией светло-серого цвета, в отличие от единственного выде- ленного прямоугольного диапазона, который обводится линией темно-серого цвета.
Над
диапазонами
непрямоугольной
формы,
по
сравнению
с прямоугольными, можно выполнять только ограниченный набор действий.
Так, например, их нельзя копировать, перемещать и т.д. В случае же попыт-
ки выполнения над ними недопустимой операции – будет выдано соответст-
вующее предупреждение.
Выделить диапазон ячеек произвольной формы, и одновременно сделать активной первую ячейку в нем, можно с помощью диалогового окна Переход, которое активизируется:
1)
по команде основного меню Правка → Перейти, или
2)
по нажатию клавиши F5.
В этом окне в поле редактирования Ссылка необходимо задать границы вы- деляемого диапазона. При этом если выделение прямоугольного диапазона ве-

74 дется относительно текущей ячейки, то в поле Ссылка можно указать только ад- рес противоположного угла и щелкнуть по кнопке OK при нажатой клавише Shift.
Аналогично рассмотренному ранее перемещению по блокам данных, в
Excel существует возможность выделения по границам горизонтальных и вертикальных блоков данных. Сочетание клавиш Shift и Ctrl, вместе с клавишами-стрелками управления курсором, вызывает расширение прямо- угольного диапазона от текущей ячейки, или диапазона, до ближайшей грани- цы блока данных в соответствующем направлении. Схожим образом двойной щелчок по границе выделенного блока (указатель мыши при этом меняет фор- му с большого белого креста на стрелку), при нажатых клавишах Shift и (или)
Ctrl, также вызывает его расширение в соответствующем направлении. Так же как и при переходе по границам блоков данных с помощью мыши, существу- ют некоторые различия в выделении указанным способом по сравнению с вы- делением по блокам данных с помощью клавиатуры. В частности, с помощью клавиатуры можно выделить диапазон до внешнего края рабочего листа, а с помощью мыши – нет.
Чтобы выделить единичную ячейку, достаточно сделать ее активной.
Другими словами – активная ячейка всегда является выделенной.
Выделение ячеек в соответствии с их содержимым. Для выделения ячеек в соответствии с их содержимым необходимо:
1.
Выполнить команду Перейти из основного меню Правка (или нажать кла- вишу F5).
2.
В раскрывшемся диалоговом окне Переход нажать кнопку Выделить.
3.
В раскрывшемся диалоговом окне Выделение группы ячеек выставить необходимые признаки выделения
4.
Нажать кнопку OK.
В окне диалога Выделение группы ячеек можно выставить такие параметры

75 как выделять только формулы, или только константы (соответствующего типа), или только пустые ячейки, и т.д.
В большинстве случаев, выделение ячеек в соответствии с их содержимым, по умолчанию выполняется в пределах рабочего листа. Для того чтобы ограни- чить диапазон выделения ячеек в соответствии с их содержимым, иногда необ- ходимо предварительно его выделить одним из приведенных ранее способов.
Выделение целых строк или столбцов. Способы выделения строк и столбцов целиком схожи со способами выделения отдельных ячеек.
Они также выполняются при помощи мыши, клавиатуры и их сочетаний.
Для того чтобы выделить единичную строку или столбец достаточно один раз щелкнуть по заголовку требуемой строки или столбца, соответствен- но. Несколько, подряд идущих, строк или столбцов можно выделить:
1)
методом перетаскивания указателя мыши по их заголовкам при нажатой левой кнопке,
2)
щелкая по заголовкам строк или столбцов при нажатой клавише Shift.
Несмежные строки и столбцы выделяются при помощи одинарного щелчка по их заголовкам при нажатой клавише Ctrl.
Для того чтобы выделить целую строку, содержащую активную ячейку, необходимо нажать сочетание клавиш Shift +  (пробел), а столбец – Ctrl + 
(пробел).
Весь же активный рабочий лист выделяется по нажатию кнопки