Форматирование столбцов и строк

⇐ ПредыдущаяСтр 3 из 3

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

Часто приходится изменять ширину столбца и высоту строки.

Если данные в ячейке не умещаются в пределах одного столбца, (заходят в другой), то нужно дважды щелкнуть мышью на правой границе заголовка столбца. Ширина столбца будет соответствовать самой длинной ячейке.

Изменить ширину столбца и высоту строки можно также путем перетаскивания мышью правой границы заголовка столбца и нижней границы заголовка строки

С помощью командыСтолбец \ Автоподбор ширины из меню Формат определение оптимальной ширины столбца можно поручить программе. Ширина определяется с учетом длины содержимого ячеек. При этом для каждого столбца устанавливается своя оптимальная ширина.

Единицей измерения ширины столбцов является значение, равное ширине символа стандартного шрифта листа. По умолчанию ширина столбца равна 8, 5 символа. Изменить это значение можно в диалоговом окнеШирина столбца, которое открывается вследствие вызова командыСтолбец \ ШиринаменюФормат.

При недостаточной высоте строки содержимое ячеек (как числа, так и текст) урезается. В этом случае можно задать автоматический подбор оптимальной высоты строки с помощью командыСтрока \ Автоподбор высоты из менюФормат.

Точное значение высоты строки устанавливается в диалоговом окне Высота строки, которое открывается вследствие вызова командыСтрока \ Высота менюФормат.

В Excel существует возможность скрывать столбцы и строки таблицы. Благодаря этой функции можно отменять отображение секретных сведений, повышать наглядность сложных таблиц, выводя на экран только самые важные данные. Скрытая информация на печать не выводится.

Чтобы скрыть строки или столбцы, необходимо выделить их, а затем активизировать командуСкрыть из подменюСтрока илиСтолбец меню Формат. Для отмены отображения целого листа предназначена команда Скрыть из подменюЛист менюФормат.

Для восстановления отображения строки или столбца нужно выделить две смежные ячейки соседних строк или столбцов и выбрать команду Отобразить в соответствующем подменю менюФормат.

Автоматическое форматирование

При использовании функции автоформатирования Excel автоматически создает красивую и наглядную таблицу. Для выполнения автоформатирования предназначена командаАвтоформат из менюФормат.

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

Параметры, присвоенные таблице перед автоматическим форматированием, можно сохранить, исключив соответствующие параметры из автоформата.

Если выделить диапазон и активизировать командуАвтоформат из менюФормат, откроется одноименное диалоговое окно. В полеСписок форматов этого окна следует выбрать нужный формат(при выделении названия формата в полеОбразец появляется образец таблицы, оформленной с использованием выбранного формата).

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

Автоформатирование можно отменить в любой момент. Для этого следует выделить автоматически сформатированный диапазон, активизировать командуАвтоформат из менюФормат и выбрать элементНет в полеСписок форматов.

Копирование форматапроизводится так. Сначала выделяется ячейка или блок ячеек, откуда будет копироваться формат. Затем нужно щелкнуть на кнопке Копировать формат панели инструментов и выделить ячейку или блок ячеек, куда формат будет перенесен.

Другой вариант копирования формата состоит в следующем: щелкнуть на кнопке Копировать в буфер панели инструментов и выделить ячейку или блок ячеек, куда формат будет копироваться. Затем выбрать команду Правка \ Специальная вставка.Появляется диалоговое окно Специальная вставка, в котором нужно отметить пункт Форматыи нажать кнопку OK.

Примечания

В ячейках рабочего листа может находиться самая разнообразная информация. Поэтому часто возникает необходимость в дополнительных пояснениях к содержимому ячеек. Чтобы не нарушить структуру таблицы, дополнительную информацию можно оформлять в виде примечаний.

Для вставки примечаний предназначена командаПримечание из меню Вставка. В результате ее активизации на рабочем листе появляется поле для ввода текста примечания. Признаком примечания является маленький красный треугольник в верхнем правом углу ячейки (индикатор примечания).

Есть три режима отображения примечаний: только индикатор, примечание и индикатор, не отображать. Эти режимы устанавливаются на вкладкеВиддиалогового окнаПараметры.

Изменить режим отображения примечаний позволяют также команды Скрыть примечание илиОтобразить примечание контекстного меню ячейки с индикатором примечания. Эти команды служат для установки первых двух описанных режимов.

Защита ячеек и листов

Чтобы установить защиту листа, в менюСервис нужно последовательно активизировать командыЗащитаиЗащитить лист. Для защиты целой книги предназначена командаЗащитить книгу.В результате активизации команды Защитить лист открывается одноименное диалоговое окно для установки параметров защиты.

Снять защиту рабочего листа позволяет командаСнять защиту листаподменюЗащита менюСервис.

Если необходимо снять защиту только с отдельных ячеек (вернее – предотвратить блокировку некоторых ячеек при установке защиты листа), следует выполнить следующие действия.

· Выделить ячейки, блокировку которых нужно предотвратить.

· Активизировать командуЯчейки менюФормат, чтобы открыть диалоговое окноФормат ячеек.

· На вкладке Защита выключить опциюЗащищаемая ячейка.

Эту операцию можно выполнить только после отмены защиты рабочего листа. При последующей установке защиты листа выделенные ячейки не будут заблокированы.

Вычисления

Создание формул

Формулы при вводе их в ячейки обязательно должны начинаться со знака равенства. Левая часть равенства не пишется: предполагается, что она – адрес ячейки (ссылка на ячейку). Если в формуле предполагается использовать содержимое каких-либо ячеек, то формула должна содержать ссылки на эти ячейки.

Формула может содержать функции и математические операторы, порядок вычисления которых соответствует принятому в математике. Результатами вычисления формул, включающих арифметические операторы, являются числовые значения, а формул с операторами сравнения — логические значения True (Истина) или False (Ложь).

Так, чтобы получить в ячейке Н11 сумму значений, содержащихся в ячейках H4, H9 и D2, следует активизировать H11, ввести знак равенства, а затем — адреса ячеек Н4, Н9 и D2, соединенные оператором сложения (+). В результате введенная в ячейку Н11 формула должна иметь следующий вид:

=Н4+Н9+D2

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

Ввод каждой формулы завершается нажатием клавиши Enter. После этого в ячейке появляется результат вычислений. Однако фактическим содержимым ячейки по-прежнему является формула, которая будет отображаться в строке формул при каждой активизации этой ячейки, а также в режиме редактирования содержимого ячейки (установить этот режим позволяет функциональная клавиша F2 или двойной щелчок на ячейке).

Другой способ создания формулы состоит в следующем. Надлежит ввести знак равенства и активизировать ячейку, которая должна быть указана в ссылке первой (Н4). В результате вокруг этой ячейки появится пунктирная движущаяся рамка, а в итоговой ячейке — адрес. Затем следует ввести оператор сложения “+”, активизировать следующую ячейку и т.д.

Знаки сложения и вычитания, как и знак равенства, служат для идентификации формулы, т.е. Excel рассматривает как формулу данные, начинающиеся со знака сложения или вычитания. После ввода формулы, в начале которой стоит оператор сложения или вычитания, и нажатия клавиши Enter Excel автоматически вставляет перед формулой знак равенства.

Вместо адресов ячеек можно вводить и числовые значения, соединенные с помощью математических операторов. Возможна комбинация адресов ячеек и числовых значений.

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

Автоматическое вычисление

Если выделить блок ячеек и щелкнуть на кнопке АвтосуммированиеСтандартной панели инструментов, то под выделенным блоком появится строка, в ячейках которой будут суммы соответствующих столбцов.
Если выделить блок с дополнительным пустым столбцом, то произойдет и вычисление сумм строк. Результаты появляются в дополнительном столбце.

Если выделить блок с дополнительным пустым столбцом и строкой, то произойдет вычисление и сумм строк, и сумм столбцов. Результаты появляются в дополнительных столбце и строке.

Для вычисления некоторых промежуточных значений или проверки правильности вычислений необязательно создавать формулы. Достаточно выделить нужный диапазон ячеек, открыть контекстное меню функции автовычисления, выполнив щелчок правой кнопкой мыши в строке состояния, и указать, какую именно информацию о данных вы хотите получить. Команды указанного контекстного меню следующие.

Команда	Назначение
СУММ	Определение суммы всех числовых значений из ячеек выделенного диапазона
МАКС	Определение максимального значения в ячейках выделенного диапазона
МИН	Определение минимального значения в ячейках выделенного диапазона
СЧЕТ	Подсчет количества числовых значений в выделенном диапазоне
СЧЕТЕСЛИ	Подсчет количества ячеек в выделенном диапазоне, которые содержат числовые данные
СРЗНАЧ	Вычисление среднего арифметического всех числовых значений, расположенных в выделенном диапазоне

Рис. 8.1. Команды автовычисления

Формулы массива

В Excel для выполнения вычислений с использованием данных из диапазона можно применять одну общую формулу — формулу массива.

Пусть требуется вычесть данные столбца G4: G9 из столбца A2: A7.

Для этого нужно выделить ячейки под результат (например B3: B8) и в активной ячейке (B3) ввести формулу

= A2: A7 – G4: G9.

Удобно вводить массивы их выделением. В этом случае с клавиатуры придется ввести только “=” и ”-”.

Чтобы действие введенной формулы распространялось на все указанные ячейки, следует завершить ее ввод нажатием комбинации клавиш Ctrl+Shift+Enter. В выделенных ячейках появляется результат вычислений, а в строке формул – такая формула массива:

{= A2: A7 - G4: G9}.

Фигурные скобки являются признаком формулы массива.

Ячейки массива обрабатываются программой как единое целое. При попытке изменить одну из ячеек интервала массива Excel выдает сообщение о недопустимости операции. Наиболее простой выход из ситуации с этим постоянно повторяющимся сообщением – нажатие кнопки Отменаслева от строки формул.

Изменить формулу массива можно в режиме редактирования. Для подтверждения изменений в формуле массива следует использовать комбинацию клавиш Ctrl+Shift+Enter.

В приведенном фрагменте Excel-таблицы (рис. 8.1) показаны некоторые результаты использования формулы массива. Столбцы С2: C4 и D1: D3 – исходные данные, E1: E3 – их разность, а G1: G3 – произведение. I1: I4 – результат операции {=C2: C4 - D1: D2} (#НД означает “нет данных”; массивы C и I состоят из 3 элементов каждый, а массив D имеет 2 элемента. Если под результат выделить столбец с “лишними” ячейками, то в них тоже появится #НД).

Рис. 8.1. Вычисления с использованием формулы массивов

По умолчанию после ввода новых данных Excel обеспечивает автоматический пересчет значений во всей рабочей книге.

При вычислениях итерационного типа в автоматическом пересчете нет необходимости. Отменить его можно путем активизации переключателя Вручную, расположенного на вкладкеВычисления диалогового окнаПараметры(рис.8.2), которое открывается выбором меню Сервис \ Параметры. Если установлен этот переключатель, вычисления во всем листе выполняются только после нажатия клавиши F9.

Рис. 8.2. Вкладка Вычисления диалогового окна Параметры

В случае активизации переключателяВручную значение в зависимой ячейке не обновляется при изменении содержимого влияющей ячейки. При этом в строке состояния появляется сообщение Вычисление, указывающее пользователю на то, что в рабочем листе произошли изменения и для обновления данных надлежит вручную выполнить пересчет. После нажатия клавиши F9 сообщение в строке состояния исчезает.

В результате установки опцииПересчет перед сохранением данные пересчитываются при каждом сохранении документа.

Копирование формул

Приведенный ниже фрагмент электронной таблицы (рис. 8.3) имеет необычный вид: во всех ячейках отображены формулы, а не результаты вычислений по этим формулам. Сделать так можно, выбрав Сервис \ Параметры \ Вид и активизировав параметр Формулы.

Рис. 8.3. Копирование формул

В ячейку D2 введена формула =B2-C3. Затем было осуществлено копирование формулы в ячейки C1, C3 и в массивы C5: C7 и E5: F7. Копирование можно выполнять любым известным способом. Ссылки в формуле указывают на позицию ячеек относительно активной ячейки. Таким образом, адреса ячеек в ссылках при копировании формулы автоматически изменяются. Такие ссылки называются относительными ссылками на ячейку.

В ячейке H2 записана формула, у которой вычитаемое приведено с абсолютной, а уменьшаемое – со смешанной ссылками. Символ $ предотвращает изменение при копировании той координаты, перед которой он введен. Результаты копирования формулы из ячейки H2 поясняют сказанное. Вставить символ $ можно в ячейке в режиме редактирования или в строке формул, используя клавишу $ или щелкая клавишу F4. Последний вариант предпочтительнее, так как не требует точной установки курсора и позволяет щелчками устанавливать символ $ в нужной позиции или убирать его. Формулы удобно копировать и посредством функции автоматического заполнения. Для этого нужно активизировать ячейку, которая содержит копируемую формулу, и установить указатель мыши на маркере заполнения в правом нижнем углу ячейки. В результате указатель приобретет вид маленького черного крестика. Маркер заполнения нужно переместить в последнюю ячейку блока, в который копируется формула. Если теперь отпустить левую кнопку мыши, выделенные ячейки заполнятся формулами, и в каждой из них автоматически будет вычислен результат. Задать автоматическое заполнение можно и с помощью меню. Для этого следует, предварительно выделив нужный диапазон ячеек, выбрать в меню Правка командуЗаполнить, а в появившемся подменю — команду, определяющую направление заполнения, напримерВправо. При вызове функции автоматического заполнения с помощью меню появляется возможность использовать данную функцию для заполнения несмежных диапазонов.

Ряды данных

В Ехсеl интегрирована экстраполирующая функция, позволяющая автоматически продолжать ряды данных. В ней сочетаются функция автозаполнения и специальная функция вычисления. Например, если ввести в ячейку А4 число 1, выделить диапазон ячеек А4: А9 и активизировать команду Заполнить\Прогрессия из менюПравка, то в результате откроется диалоговое окно Прогрессия (рис. 8.4), в котором следует указать тип прогрессии и ее шаг. После нажатия OKвыделенный массив заполнится элементами прогрессии. Если диапазон ячеек не выделять, то в диалоговом окне следует выбрать расположение прогрессии (по строкам или по столбцам), указать шаг и предельное значение.

Рис. 8.4. Диалоговое окно Прогрессия

Имена в формулах

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

Для присвоения имени диапазону ячеек сначала следует выделить этот диапазон. Затем выполняется щелчок в поле имени строки формул, в котором находится адрес текущей ячейки. В результате в этом поле появится курсор ввода, а адрес ячейки будет выделен. Теперь нужно ввести имя диапазона и нажать клавишу Enter. Присваивать имена можно и несмежным диапазонам, а также ячейкам, расположенным в диапазоне, которому уже дано имя.

Есть другой способ именования диапазонов: надлежит активизировать командуИмя\Присвоить из менюВставка и ввести имя диапазона в поле Имя диалогового окнаПрисвоение имени(рис. 8.5).Для удаления имен служит кнопкаУдалить в этом диалоговом окне.

Рис. 8.5. Диалоговое окно Присвоение имени

Имя, присвоенное диапазону ячеек, можно использовать в качестве адреса в формулах и в качестве аргумента функции. Если в формуле указывается несуществующее или некорректное имя, то в ячейке с формулой вместо результата вычислений появляется значение ошибки #Имя?. Чтобы выделить диапазон, которому присвоено имя, следует рядом с полем имени щелкнуть на кнопке Вниз и в открывшемся диалоговом окне списка имен выделить имя нужного диапазона.

Функции в формулах

Excel имеет множество специальных функций, которые можно применять в вычислениях. Аргументы функции указываются после ее названия в круглых скобках. Скобки вводятся с клавиатуры. Адрес диапазона ячеек, содержимое которых должно использоваться в качестве аргументов функции, вводится с клавиатуры или вставляется в результате выделения диапазона с помощью мыши.

Аргументами функции могут быть адреса как смежных, так и несмежных диапазонов. Адрес смежного диапазона состоит из адресов первой и последней ячеек, разделенных двоеточием. Адреса несмежных ячеек разделяются точкой с запятой. Оба символа разделения вставляются в формулу автоматически при указании диапазона ячеек с помощью мыши.

В качестве аргументов допускается использование ссылок на диапазоны из других листов и книг, например:

=СУММ(С7: С9; Лист2! ВЗ: В15; [Книга4]Лист1! $A$2: $А$6)

Здесь первый аргумент – диапазон С7: C9 текущего рабочего листа, второй – диапазон B3: B15 листа 2 текущей рабочей книги, третий – диапазон А2: A6 рабочего листа 1 книги 4.

Функция может служить аргументом другой функции. Такие функции называются вложенными. Пример вложенной функции:

=МАКС(МИН(B4: B10); МИН(C4: C10); МИН(E4: E10)).

Для упрощения работы с функциями служит панель формул. При выполнении щелчка на кнопке со знаком равенства в строке формул и последующего щелчка на стрелке Вниз в левой части строки формул раскрывается список, содержащий 10 функций, которые были использованы последними. Следует выбрать нужную функцию. В результате откроется панель формул (рис. 8.6).

Рис. 8.6. Панель формул

Если нужной функции в списке не окажется, следует выбрать элемент Другие функции, вследствие чего откроется окноМастер функций. Это окно открывается также в результате вызова командыФункция менюВставка или нажатия кнопкиВставка функции стандартной панели инструментов (рис.8.7).

Рис. 8.7. Диалоговое окно Мастер функций

Все функции Excel 97 сгруппированы по категориям, имена которых отображаются в спискеКатегория. В полеФункция приводится перечень функций выбранной категории. В нижней части окна отображается краткое описание отмеченной функции и ее синтаксис. Если этой информации недостаточно для ввода функции, нужно нажать в диалоговом окне кнопку вызоваПомощник (или клавишу F1). На экране отобразится страница справочной подсистемы с информацией о выделенной функции.

После выбора функции нужно выполнить щелчок на кнопкеОК, чтобы вернуться на панель формул, и задать аргументы. На панели формул для ввода каждого аргумента функции предусмотрено отдельное поле. В качестве аргумента можно задавать числовое значение, адрес ячейки (абсолютный или относительный), адрес или имя диапазона.

По завершении ввода аргументов следует нажать кнопкуОК, и окончательный результат отобразится в текущей ячейке.

Если используется формула массива, то после окончания ввода аргументов нужно сделать щелчок в строке формул и нажать сочетание клавиш Ctrl+Shift+Enter. После этого в предварительно выделенном диапазоне отобразится результат.

Пример 1. Пусть требуется найти произведение двух матриц размером 2х3 и 3х4, сформированных генератором случайных чисел (функция СЛЧИС) в диапазонах A13: C14 и E13: H15 соответственно. Результат нужно поместить в диапазон A16: D17 (рис. 8.8).

Рис. 8.8. Пример умножения двух матриц

Порядок вычислений следующий. Для формирования первой матрицы выделяем предназначенный для нее диапазон и открываем диалоговое окно Мастер функций. В списке Категории выбираем пункт Полный алфавитный перечень, а в списке Функции – функцию СЛЧИС. Щелчок на ОК приводит к появлению Панели формул, соответствующей функции СЛЧИС, аргументы которой не требуются. Теперь остается сделать щелчки на ОКи в строке формул, а затем набрать с клавиатуры Ctrl+Shift+Enter.

При формировании второй матрицы поступаем так же, но окно Мастер функций можно не открывать, а сразу открыть Панель формул (функция СЛЧИС там уже имеется).

Для умножения матриц следует выделить диапазон для произведения, найти в окне Мастер функций функцию МУМНОЖ и перейти в Панель формул. В поля Массив 1 и Массив 2 вводим с клавиатуры (или выделяя мышью) диапазоны первого и второго сомножителей. В заключение нужно сделать щелчки на ОКи в строке формул, а затем нажать Ctrl+Shift+Enter.

Пример 2. Решим систему линейных уравнений. В матричном виде система линейных уравнений задается так:

A X= B,

где А –	матрица коэффициентов при неизвестных;
X –	вектор неизвестных;
B –	столбец свободных членов.

Вектор неизвестных определяется по матричной формуле
X=A^-1B, где A^-1 – матрица, обратная матрице A. Следовательно, для нахождения X нужно обратить матрицу A и полученную матрицу умножить слева на B. На рис. 8.9 исходные данные занесены в диапазон A2: C4 (матрица X) и в диапазон F2: F4 (вектор B). В диапазоне A6: C8 с помощью функции МОБР получена матрица A^-1, а в диапазоне F6: F8 с помощью функции МУМНОЖ – искомые неизвестные X (произведение A^-1B).

Рис. 8.9. Решение системы линейных уравнений

Пример 3. Найдем зависимость по экспериментальным данным.

Пусть проведены исследования влияния независимой переменной Х на зависимую переменную Y. Результаты экспериментов приведены на рис.8.10 (диапазоны B2: B10 и D2: D10). Требуется найти зависимость Y=f(X) (найти уравнение регрессии).

Будем считать, что искомое уравнение имеет вид полинома
Y=b₀+b₁ X+b₂X²+….

Наша задача – определить коэффициенты b₀, b₁, b₂, …. Чем более “длинное” уравнение будем подбирать, тем меньше значения, вычисленные по этому уравнению, будут отличаться от экспериментальных значений Y_Э.

Суть метода наименьших квадратов состоит в том, чтобы общая сумма квадратов отклонений Y, полученных по уравнению, от Y_Э, полученных в результате эксперимента, была минимальной. Это гарантирует максимальную близость экспериментальных точек и соответствующих точек, полученных по уравнению (при данной степени полинома). Оставим в стороне вопрос о том, когда следует “остановиться” в усложнении уравнения, и будем сравнивать отличия Y от Y_Э “на глазок”.

Метод наименьших квадратов дает следующее выражение для вычисления вектора B коэффициентов уравнения:

B=(X^тX)^-¹X^тY_Э,

где X^т – транспонированная матрица X.

Значения Y, полученные по уравнению, вычисляются по формуле

Y=XB.

Таким образом, процесс нахождения BиYсостоит из 6 операций (рис. 8.10):

1) транспонирования X (получения X^т), результат в диапазоне A13: J15;

2) умножения X^тX, диапазон F2: H4;

3) обращения (X^тX)^-1, диапазон F7: H9;

4) умножения X^тY_Э, диапазон I2: I4;

5) умножения B=(X^тX)^-1X^тY_Э, диапазон J2: J4;

6) умножения Y=XB, диапазон E2: E11.

При каждой попытке подобрать новое уравнение будет меняться матрица X. Но во всех случаях первый столбец X будет единичным. При попытках определить коэффициенты для уравнений Y=b₀+b₁X, Y=b₀+b₁X+b₂X², Y=b₀+b₁X+b₂X²+ b₃X³и т.д. столбцами матри-
цы X будут соответственно (1, X), (1, X, X²), (1, X, X², X³) и т.д.

Рис. 8.10. Нахождение зависимости по экспериментальным данным

Ошибки в формулах

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

Первым символом значения ошибки является диез (#), за которым следует текст. Текст значения ошибки может завершаться восклицательным знаком или знаком вопроса. Установить причину возникновения ошибки позволяет командаЗависимости \ Источник ошибок из меню Сервис(рис. 8.11).

Рис. 8.11. Ошибка в формуле

Чтобы избежать появления ошибок при вводе данных в таблицу, для отдельных ячеек можно установить ограничения на допустимые значения. Ограничения могут накладываться как на тип данных в ячейке, так и на диапазон возможных значений. Необходимо выделить ячейки, а затем в менюДанные активизировать командуПроверка. Откроется окноПроверка вводимых значений, в котором нужно перейти на вкладкуПараметры (рис. 8.12). В полеТип данныхможно определить допустимый тип данных.В поле Значение можно выбрать критерий проверки: больше, не равно и т.д.

Рис. 8.12. Диалоговое окно Проверка вводимых значений

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

Построение диаграмм

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

Рассмотрим построение диаграммы на примере.

На рис. 9.1 в столбцах A, B и C приведены значения независимой переменной X и функций Y=sinX и Y=X–1 соответственно. Выбор Вставка \ Диаграмма или щелчок на кнопке Мастер диаграмм панели инструментов приводит к появлению диалогового окна Мастер диаграмм (шаг 1 из 4), в котором выбираются нужные тип и вид диаграммы. Выбору помогает нажатие и удерживание в нажатом состоянии кнопки Просмотр результата.

Рис. 9.1. Диалоговое окно Мастер диаграмм (шаг 1 из 4)

Щелчок на кнопке Далее открывает следующее диалоговое окно Мастер диаграмм (рис. 9.2).

Рис. 9.2. Вкладки Диапазон данных и Ряд Мастера диаграмм

На вкладке Диапазон данных следует отметить (выделением мышью или введением диапазона с клавиатуры) столбцы, в которых расположены значения Y (см. рис.9.2, поле Диапазон). Значения Y расположены на рабочем листе в столбцах, что должно быть зафиксировано выбором Ряды в: столбцах. По умолчанию отметки на оси X соответствуют номерам строк таблицы. Переход на вкладку Ряд позволяет правильно разбить горизонтальную ось, введя в поле Подписи оси X (с клавиатуры или с помощью выделения мышью) диапазон значений X. Щелчок на кнопке Далее открывает следующее окно Мастер диаграмм (рис. 9.3).

Это окно имеет 6 вкладок, позволяющих нужным образом оформить диаграмму. На рис. 9.3 показано заполнение вкладки Заголовки и результат. Вкладка Таблицы данных позволяет под диаграммой разместить таблицу значений X и Y.

Щелчок на кнопке Далее приводит к появлению последнего диалогового окна Мастер диаграмм, где можно выбрать место размещения диаграммы: на отдельном или на имеющемся листе. После соответствующего выбора остается нажать кнопку Готово.

Рис. 9.3. Назначение параметров диаграммы

На экране возникают построенная диаграмма и панель инструментов Диаграммы(если панель инструментов отсутствует, ее можно вызвать выбором Вид \ Панели инструментов \ Диаграммы). Щелчком можно выделять область диаграммы (см. рис. 9.4), область построения диаграммы и легенду. Выделенные таким образом области можно перемещать (“тащить”за любую внутреннюю точку), изменять размеры области (“тащить”за маркеры). Для удаления какой либо функции нужно выделить соответствующую линию щелчком на ней и нажать клавишу Delete. Любой ряд данных можно из таблицы на рабочем листе вставить в диаграмму (выделив ряд данных и перетащив его за рамку в область диаграммы).

Рис. 9.4. Фрагмент рабочего листа с диаграммой

Кнопки на панели инструментов Диаграммы позволяют вносить изменения в построенную диаграмму.

Макросы

При работе с Microsoft Excel некоторые действия могут быть повторяющимися. Это, например, выделение интервалов, ввод одинаковых данных, выполнение сложных операций. Иногда всю работу приходится повторять в дальнейшем.

В таких случаях целесообразно создать макрос. Макрос – это особый вид программы на языке VBA (Visual Basic for Application). То, что делает пользователь с помощью клавиатуры и мыши, может быть записано макрорекордером. Записанный макрос можно сохранить для последующего использования, снабдить комментариями, изменить.

Макросы как автоматически создаваемые программы очень помогают при написании программ на языке VBA и облегчают изучение самого языка.

Запись макроса

1. Для начала записи нужно выбрать Сервис \ Макрос \ Начать запись. Открывается диалоговое окно Запись макроса (рис.10.1).

Рис. 10.1. Диалоговое окно Запись макроса

В поле Имя макроса следует ввести нужное (если не подходит имя по умолчанию Макрос№).При желании можно что-нибудь записать в поле Описание. По имени макроса его можно будет в дальнейшем отыскать, а по описанию вспомнить. Пользователь может определить Сочетание клавиш для быстрого запуска макроса с клавиатуры нажатием комбинации Ctrl + < введенная буква>.

В поле со списком Сохранить в: нужно указать, где будет сохранен макрос. Возможны варианты: Эта книга, Новая книга и Личная книга макросов. Особенность личной книги макросов (PERSONAL.XLS) в том, что она автоматически загружается при запуске Excel. Однако она невидима, и пользователь не замечает, что она открыта. Макрос, сохраненный в личной книге, делает его доступным для всех книг.

2. После нажатия кнопки OK диалоговое окно закрывается; появляется панель инструментов Остановить запись с двумя кнопками: Относительнаяссылка и Остановить запись. Если панель не появилась на экране, нужно поставить флажок у соответствующего пункта меню Вид \ Панели инструментов. Кнопка Остановить запись имеется также на панели инструментов
Visual Basic, которую можно вызвать на экран таким же способом (рис. 10.2).

Рис. 10.2. Панели инструментов Остановить запись и Visual Basic

Начиная с этого момента, все, что делает пользователь с помощью клавиатуры и мыши, протоколируется в окне Модуль, которое пока скрыто от пользователя.

Для прекращения записи макроса нужно нажать кнопку Остановитьзапись.

Запуск макроса

Для запуска макроса пригоден любой из трех вариантов.

1. Запуск из диалогового окна Макрос. Если выбрать Сервис \ Макрос \ Макросы, то откроется диалоговое окно Макрос
(рис. 10.3). Сначала нужно указать местоположение макроса в раскрывающемся списке Находится в: . После этого в списке макросов следует выделить имя нужного макроса (оно затем появится в поле над списком).

В поле Описание появляется информация, записанная ранее в диалоговом окне Запись макроса. Становятся доступными все кнопки диалогового окна.

Рис. 10.3. Диалоговое окно Макрос

Для запуска макроса нужно нажать кнопку Выполнить.

Нажатие кнопок Войти и Изменить приводит к открытию окна Microsoft Visual Basic, в ча

⇐ Предыдущая 1 23