Изменение структуры электронной таблицы.
Под структурой конкретной электронной таблицы понимается некоторое количество строк и столбцов, занимаемые (заполненные) данной таблицей. Значит, под изменением структуры электронной таблицы понимается вставка/удаление строк и (или) столбцов в/из имеющейся конкретной электронной таблицы.
Добавление новых строк к листу происходит так:
- Выделение строки, находящейся под тем местом, где требуется вставить новую, пустую строку. Строка выделяется щелчком на ее номере.
- Выполнение команды из главного меню Вставка/Строки. Добавление нескольких новых строк к листу отличается от предыдущего тем, что выделяется не одна строка, а столько, сколько строк нужно вставить.
Добавление новых столбцов к листу происходит аналогично, но выделяется столбец, или столбцы, находящиеся справа от того места, где требуется вставить новый столбец или столбцы.
Построение формул в Excel
Формула – это выражение, состоящее из числовых величин, ссылок на ячейки, имен функций с аргументами и операторов, по которым вычисляется новое значение. Отображаемое значение формулы зависит от тех параметров, которые задаются для рабочего листа.
Ввод формул и функций предусматривает общие правила:
• в формулу, кроме числовых величин, могут входить в качестве аргументов адреса ячеек или блоков, а также функции или другие формулы;
• если формула начинается с адреса ячейки, перед первым символом необходимо набрать = (знак равенства) или + (знак плюс);
• в ячейке виден результат вычислении, а сама формула отражается в строке ввода;
• Excel по умолчанию вычисляет формулу каждый раз, когда изменяется содержимое таблицы.
Итак, формулы в Excel всегда начинаются со знака равенства (=), после которого указываются следующие компоненты:
Значение. Число (3.14) или строка («привет»).
Адрес ячейки. В5, А8: С12, лист 2! В15 или имя диапазона ячеек (Моя ячейка).
Функция. Сумм (SUM) т.д.
Символ операции. +, -, *, /, ^, >, = и т.д.
Скобки. Используются для изменения порядка вычисления формулы. =(А7+В4)*2
Имя файла. Используется для задания связи с файлом, пример, [Sample.xls].
Пробелы, символы табуляции и прочие управляющие символы. Используются для придания формуле более читабельного вида.
Формула может содержать функции и математические операторы, порядок выполнения которых соответствует принятому в математике.
Результатом вычисления формул, включающих арифметические операторы, являются числовые значения. Если ячейка содержит формулу, то в такой ячейке отображается только результат формулы. Сама же формула отображается на панели формул после выделения ячейки. Включаемый в формулу текст должен быть заключен в кавычки. Пример: введем в А5 число 3000000, а в В5 формулу: = «мой заработок за август составил» &А5& « рублей». В итоге будет получен результат: Мой заработок за август составил 3000000 рублей. Знак амперсанта (&) используется для конкатенации (т.е. сцепления или слияния) текста в формуле. В данном случае результатом формулы будет текст, а не числовое значение. Если в формуле комбинируется текст и численные значения, то результатом будет текст.
Символы операций выполняют функцию «склеивания» других компонентов формулы. При наличии в формуле нескольких операций, Excel производит вычисления слева направо. Однако у всех операций имеется приоритет, влияющий на порядок вычислений.
Из двух операций первой выполняется та, у которой более высокий приоритет. Операции с одинаковым приоритетом, например операции умножения и деления, выполняются слева направо.
Таблица 1 - Доступные операции Excel в порядке убывания их приоритета
| Символы операций | Операции |
| + - | Задание знака числа |
| % | Вычисление процентного соотношения |
| ^ | Возведение в степень |
| * / | Умножение и деление |
| + - | Сложение и вычитание |
| & | Слияние текста |
| >, <, > =, < =, <> | Больше чем, меньше чем, больше чем или равно, меньше чем или равно, не равно |
Стандартный порядок вычислений можно изменить с помощью круглых скобок.
Пример. = 2+3^2 =11; = (2+3)^2 =25.
= 10-8/2 =6; = (10-8)/2 =1.
Максимальный размер формулы ограничен 1024 символами. Сложная формула может быть достаточно длинной и бывает трудно ее правильно написать и, особенно, понять. Поэтому применятся различные приемы улучшения ввода формул.
Пример. Формула = C15*D25+A25-A30-A35
Использование скобок. =(C15*D25) + (A25-A30-A35)
Использование пробелов. = C15*D25 +A25-A30-A35
Разделение длинной формулы на несколько строк, расположенных в одной ячейке:
=C15*D25 (Alt+Enter)
+A25-A30-A35
Разбивка длинной формулы на несколько ячеек: Ячейка A1: = C15*D25
Ячейка A2: =A25-A30-A35
Ячейка A3: =A1+A2
Для отображения формул на рабочем листе, а не их результатов, нужно выбрать команду Сервис/Параметры и щелкнуть на вкладке Вид. В группе Параметры окна (Windows Options) установить флажок Формулы и щелкнуть на кнопке ОК.
Теперь на рабочем листе будут отображаться формулы, а не их результаты.
Переключаться в этот режим просмотра и из него можно с помощью комбинации клавиш Ctrl+` (это не кавычка, а знак ударения).
Экономические таблицы содержат в пределах одной графы однородные данные, то есть данные одного типа и структуры. Excel предоставляет возможность не заниматься рутинной работой, а копировать формулу из одних ячеек в другие.
Сначала необходимо научиться выделять блоки ячеек, то есть маркировать определенную область экрана, охватывающую интервал ячеек. С помощью мыши блок можно выделить следующим образом. Отметьте первую ячейку в блоке, который нужно выделить. При нажатой левой кнопке мыши протащите указатель по всем оставшимся ячейкам блока. При этом выделенная область будет заключена в рамку. На экране можно увидеть, как увеличивается рамка, следующая за указателем мыши. Отпустите кнопку мыши, и на экране отобразится выделенный блок. Если выполнить щелчок вне выделенного блока, маркировка исчезнет.
В Excel можно выделить целиком столбец (блок столбцов), строку (блок строк) и весь лист целиком. Для выделения столбца или строки следует выполнить щелчок на соответствующем идентификаторе столбца или строки (то есть букве — для столбцов и цифре — для строк).
Для работы с блоками Excel предоставляет большой набор команд: копирование, перемещение, вставка, удаление, форматирование и т. д.
В Excel копирование может производиться несколькими способами.
1) Выделите ячейку, содержащую формулу для копирования. ВыберитеПравка/Копировать. Excel окружит ячейку движущейся рамочкой. Выделите блок ячеек как область вставки. Выберите Правка/Вставить и затем снимите выделение блока. При копировании Excel автоматически корректирует адреса ячеек.
2) С помощью кнопки Копировать в буфер на панели инструментов. Для этого активизируйте ячейку с формулой, щелкните кнопку Копировать в буфер на панели инструментов, выделите нужный блок ячеек, щелкните кнопку Вставить из буфера на панели инструментов, нажмите <Enter> и <Esc>, чтобы выйти из режима копирования, и наконец щелкните кнопкой мыши вне выделенного блока.
Excel позволяет легко скопировать, или дублировать, формулу в соседние ячейки командой Правка/Заполнить. Основная хитрость при работе с подменю Заполнить заключается в том, что его команды автоматически изменяют в формуле ссылки на ячейки так, чтобы они соответствовали строкам и столбцам, в которые происходит копирование. Например, если копировать формулу =А6*В6 на одну ячейку вниз командой Вниз, то Excel автоматически подгонит присутствующие в формуле номера строк так, чтобы получились правильные ссылки на новые ячейки и формула после копирования будет выглядеть так: =А7*В7. Такое изменение индексов называется относительным, а ссылки - относительными.
Относительная и абсолютная адресация.
При копировании формул из одного места в другое происходит относительное изменение адресов ячеек, содержащихся в формулах. Но иногда пользователю это не нужно. Если при копировании формул адреса ячеек не должны изменяться, то используют абсолютные адреса следующим образом: перед соответствующими именем столбца и (или) номером строки поставить знак $. Например, $А$1.
Заголовки и имена в формулах.
Рабочий лист может содержать заголовки, размещенные в начале столбца и слева от строки, с помощью которых описываются данные внутри листа. При ссылке необходимые данные могут использовать эти заголовки в формулах. Кроме этого можно создать имя для ячеек, группы ячеек, формулы или константы не пользуясь заголовками. Пробел между заголовками (=отдел продажа) является оператором пересечения диапазона, который предписывает формуле вернуть значение из ячейки, находящейся на пересечении строки Отдел и столбца Продажа. Для создания заголовков из названий строк или столбцов можно воспользоваться диалоговым окном Заголовки диапазонов из меню Вставка/Имя. Если в качестве заголовка, создаваемого в диалоговом окне, используются года или даты, то при вводе формулы они заключаются в кавычки (`1998`/`1999`).
Если данные не имеют заголовка или размещены на другом рабочем листе книги, то можно создать имя, описывающее ячейку или группу ячеек. Имена можно использовать в любом месте данной книги. Для создания имени можно использовать меню Вставка/Имя/Создать.
Пересчет формул.
Пересчет – это процесс обработки формул и отображение возвращаемых ими значений ячеек, содержащих формулы. По умолчанию Microsoft Excel автоматически пересчитывает все открытые книги. Но процессом вычислений можно управлять. При пересчете Excel обновляет значения ячеек, которые изменяются в результате произведенных корректировок. Такой подход позволяет избежать ненужных вычислений. Кроме того Excel всегда обновляет книги при открытии или сохранении. В процессе пересчета в Excel используются не те значения, которые отображаются на экране, а внутренние значения ячеек. Например, дата 22.6.99 является форматированным представлением некоторого числа и задавая формат ячеек можно изменять изображение на экране, не изменяя внутреннего значения ячеек. Во время пересчета можно выполнять команды и ввод чисел или формул. Для выполнения команд и других действий пересчет прерывается и затем возобновляется вновь.
Для выбора способа пересчета листа или книги необходимо:
1) выбрать команду Сервис/Параметры
2) установить необходимые параметры на вкладке Вычисления
3) если переключатель установлен в положение, отличное от положения Автоматически, нажать клавишу F9 для пересчета (вычисления) всех листов во всех открытых книгах. Чтобы провести пересчет только в активном листе, нужно нажать Shift+F9.
При обновлении связи с листами, на которых пересчет формул выполнялся вручную, на экране появится сообщение о том, что в источнике данных не произведен пересчет формул. Чтобы обновить связь, используя текущие значения ячеек, необходимо нажать ОК, чтобы прервать обновление связи и использовать значения, полученные из источника данных ранее – Отмена.
Циклические ссылки в формулах.
Циклической ссылкой называется последовательность ссылок, когда формула ссылается через другие ссылки сама на себя. Чтобы обработать такую формулу, нужно вычислить значения каждой ячейки, включенную в замкнутую последовательность, используя результат предыдущих операций. По умолчанию вычисления продолжаются до тех пор, пока не будут изменены соответствующие параметры. Вычисления прекращаются после выполнения соответствующей операции или после того, как изменение каждой следующей величины не будет превышать 0,001 за одну итерацию (повторение).
Обработать формулы с циклическими ссылками в режиме обычных вычислений нельзя. Когда встречается формула с циклической ссылкой, появляется предупреждающее сообщение. Если циклическая последовательность ссылок образовалась случайно, то следует нажать ОК. На экране появится панель инструментов Циклические ссылки и стрелки зависимостей, указывающие на каждую ячейку циклической ссылки. Однако циклические ссылки часто используются в научных и инженерных расчетах. Чтобы изменить число итераций, нужно выбрать меню Сервис/Параметры/Вычисления/Итерации и установить максимальное число итераций и относительную погрешность вычислений.
Список кодов распространенных ошибок, возвращаемых Excel при невозможности вычислить результат формулы, следующий:
######. Размеры ячейки недостаточны для размещения результата или числовой константы. Столбец слишком узок для отображения числа – либо расширьте столбец, либо уменьшите размер шрифта, либо измените формат вывода числа. Также эта ошибка может появиться при определении числа дней между двумя датами, при определении количества часов между двумя временными промежутками. В этом случае необходимо проверить правильность ввода формулы, так как в Excel время и дата должны быть положительными.
# ДЕЛ/О! Деление на ноль является недопустимой операцией. Все пустые ячейки содержат нулевые значения.
# ЗНАЧ! Недопустимый тип аргумента. Формула содержит некорректную операцию или аргумент – возможно, вы пытаетесь сложить текстовые и числовые значения. Причины возникновения ошибки: вместо числового или логического (ИСТИНА или ЛОЖЬ) значения введен текст, и Microsoft Excel не может преобразовать его к нужному типу данных; после ввода или редактирования формулы массива нажимается клавиша ENTER (для редактирования формулы укажите ячейку или диапазон ячеек, содержащих формулу массива, нажмите клавишу F2, а затем— клавиши CTRL+SHIFT+ENTER) и др.
# ИМЯ? Неверное имя функции или области. Формула ссылается на неизвестное Excel имя.
# Н/Д. Значение ошибки #Н/Д является сокращением термина «Неопределенные Данные». Это значение помогает предотвратить использование ссылки на пустую ячейку. Введите в ячейки листа значение #Н/Д если они должны содержать данные, но в настоящий момент эти данные отсутствуют. Формулы, ссылающиеся на эти ячейки, тоже будут возвращать значение #Н/Д вместо того, чтобы пытаться производить вычисления. Возможные причины: для функций ГПР, ПРОСМОТР, ПОИСКПОЗ или ВПР задан недопустимый аргумент искомое_ значение; используются функции ВПР или ГПР, просматривающие значение в несортированной таблице; в формуле массива используется аргумент не соответствующий диапазону, определяющимся числом строк и столбцов, который указан в формуле массива и др.
# ПУСТО! Попытка задания пересечения двух областей, которые не имеют общих ячеек.
# ССЫЛКА! Неверная ссылка на ячейку. Возможно, вы удалили ячейку или переместили на ее место другую.
# ЧИСЛО! Ошибка при организации вычислений. Формула использует некорректное число. (Подробнее об этих типах ошибок см. файл Причины возникновения ошибок в Excel на сервере).