В чем заключается достоинство использования ссылок и формул со ссылками
Ссылки в Excel
Ячейка таблицы, на которой стоит указатель ячейки является активной. Для активизации необходимо щелкнуть кнопкой мыши по ячейке.
Группа ячеек может быть выделена для выполнения различных операций. Если выделяемая область таблицы имеет прямоугольную форму, то технология ее выделения обычная — либо мышью с удержанием левой кнопки, либо клавиатурой — при нажатой клавише Shift. Если выделяются несмежные ячейки таблицы, то сначала следует выделить первую область, а затем, удерживая Ctrl, мышью выделить другие.
При необходимости ссылки на прямоугольную область таблицы говорят о диапазоне ячеек. В общем случае диапазон записывается как две ссылки на верхнюю левую и нижнюю правую ячейки прямоугольной области, разделенные двоеточием, например D5:F13. Диапазоном может быть частичный столбец (например, R4:R25) или частичная строка (например, A12:Z12). Если двоеточием разделены только номера строк или столбцов (например, A:F или 5:13), то в диапазон входят все ячейки таблицы, ограниченные этими строками или столбцами.
Для ввода данных в ячейку, ее надо активизировать, набрать данные с клавиатуры и нажать Enter или щелкнуть мышью в другой ячейке.
Данные, вводимые в ячейку, могут быть текстом, формулой или ссылкой. Excel может определять тип данных автоматически. Если введен текст, он обычно выравнивается по левому краю ячейки. Если длина текста превышает ширину ячейки, но ячейка справа пуста, текст на экране будет занимать эту ячейку. Если же ячейка справа занята, то на экране текст ограничивается размером ячейки. Фактически же ячейка содержит полный текст, в чем можно убедиться, просмотрев ее содержимое в строке формул.
Вводимые числа выравниваются по правому краю ячейки. Для разделения целой и дробной частей десятичных чисел используется запятая. Если целая часть числа не умещается по ширине ячейки, на экран выводятся знаки «####».
Запись формул и ссылок на другие ячейки начинается со знака «=», за которым следует выражение или ссылка. Если выражение синтаксически правильно, то после завершения ввода формулы в ячейке размещается результат вычисления, иначе — сообщение типа «#ИМЯ?».
Достоинство использования ссылок и формул со ссылками в том, что при изменении данных в исходных ячейках, результат в ячейке с формулой будет скорректирован автоматически.
Математические операторы и ссылки на ячейки в формулах Excel
Одним из самых мощных инструментов Excel является возможность производить расчеты при помощи формул. Именно формулы делают электронные таблицы такими гибкими и полезными. Как и калькулятор, Excel может складывать, вычитать, умножать и делить. В данном уроке мы рассмотрим основные математические операторы, используемые в Excel, а также познакомимся с преимуществами использования ссылок на ячейки в формулах.
Математические (арифметические) операторы
Excel использует стандартные операторы для формул, такие как: знак плюс для сложения (+), минус для вычитания (-), звездочка для умножения (*), косая черта для деления (/) и циркумфлекс для возведения в степень (^).
Все формулы в Excel должны начинаться со знака равенства (=). Это связано с тем, что Excel приравнивает данные хранящиеся в ячейке (т.е. формулу) к значению, которое она вычисляет (т.е. к результату).
Основные сведения о ссылках
Несмотря на то, что в Excel можно создавать формулы, применяя фиксированные значения (например, =2+2 или =5*5), в большинстве случаев для создания формул используются адреса ячеек. Этот процесс называется созданием ссылок. Создавая ссылки на ячейки убедитесь, что формулы не содержат ошибок.
Использование ссылок в формулах дает ряд преимуществ, начиная от меньшего количества ошибок и заканчивая простотой редактирования формул. К примеру, вы легко можете изменить значения, на которые ссылается формула, без необходимости ее редактировать.
Используя математические операторы, совместно со ссылками на ячейки, можно создать множество простых формул. На рисунке ниже приведены несколько примеров формул, которые используют разнообразные комбинации операторов и ссылок.
В Excel существует несколько видов ссылок. Изучите уроки раздела Относительные и абсолютные ссылки, чтобы получить дополнительную информацию.
Джизак 2005 С. М. Жумабоев, Г. И. Набираева
В чем заключается достоинство использования ссылок и формул со ссылками
Организация вычислений
Формулы и их запись
Запись формулы в ячейку начинается со знака «=». Далее записывается арифметическое выражение с использованием знаков математических операций, круглых скобок, ссылок и стандартных функций Excel. Правила записи — обычные математические.
Рассмотрим организацию простейших вычислений на примере таблицы стоимости автоперевозок.
Будем считать, что себестоимость перевозок складывается из затрат на топливо и других затрат, пропорциональных весу автомобиля с грузом и пройденному расстоянию. Тогда затраты X можно вычислить по формуле:
Формула для вычисления стоимости перевозки до Корочи в ячейке F9 имеет вид:
Суммарный вес перевезенного груза и суммарную стоимость перевозок можно рассчитать по формулам =СУММ(E9:E13) и СУММ(F9:F13).
Работа с Мастером функций
Работа с Мастером функций начинается с нажатия кнопки 
или обращения к меню Вставка/Функция и выполняется в два этапа: выбор функции и задание аргументов функции. Очень часто аргументами функций являются табличные данные, поэтому они должны быть подготовлены до обращения к Мастеру функций.
1 этап — выбор функции
2 этап — задание аргументов функции
Панель для задания аргументов выводится автоматически после завершения первого этапа. Она содержит одно или несколько однострочных полей, снабженных специальной кнопкой для сворачивания панели. Если количество аргументов функции может быть произвольным, то после ввода аргументов в очередное поле, на панели появляется дополнительное поле и т.д. При активизации поля в нижней части панели появляется пояснение его назначения.
Ошибки в формулах
Для облегчения поиска ошибок в формулах целесообразно перейти в режим показа формул в ячейках через Сервис/Параметры/Вид и установить флажок «Формулы». При этом ширина ячеек таблицы будет автоматически увеличена и вместо результатов и сообщений об ошибках будут показаны формулы.
Вычисления с массивами
В диапазоне A1:B5 задана матрица. Требуется вычислить произведение исходной матрицы на транспонированную.
Сначала выполним ее транспонирование. Для этого выделим D1:H2 и вызовем Мастера функций. В категории «Ссылки и массивы» найдем функцию ТРАНСП(), зададим ее аргумент A1:B5 и нажмем Shift+Ctrl+Enter. Умножим исходную матрицу на транспонированную. Для этого выделим любой интервал размером 2х2 ячейки, например, D4:E5. Запишем формулу =МУМНОЖ(A1:B5; D1:H2) и нажмем Shift+Ctrl+Enter. Результат показан на рисунке.
В чем заключается достоинство использования ссылок и формул со ссылками
Excel — табличный процессор, входящий в комплект Microsoft Office и предназначенный для обработки информации, представленной в табличной форме. В отличие от текстового процессора Word, предназначенного для оформления текстовых документов, Excel специализирован для выполнения вычислений с табличными данными. Excel имеет большое количество встроенных функций для математических, статистических, финансовых и других вычислений. С другой стороны, Excel — это среда, ориентированная на непрограммирующего пользователя, что делает его популярным среди экономистов, бухгалтеров и других специалистов, обрабатывающих табличные данные.
Элементы рабочего окна Excel
Excel имеет стандартный интерфейс Windows. Поэтому рассмотрим только те элементы рабочего окна, которые отличают Excel от Word.
Строка меню отличается от меню в Word заменой пункта «Таблица» на пункт «Данные», предназначенный для выполнения операций сортировки, фильтрации и некоторых других над табличными данными.
На панели форматирования следует отметить кнопку «Объединить и поместить в центре», а также «Денежный формат» и «Процентный формат».
Строка формул состоит из поля адреса, управляющих кнопок и поля содержимого ячейки. При активизации ячейки таблицы в этих полях появляется соответствующая информация. Управляющие кнопки выполняют: отмену редактирования ячейки (кнопка с крестом); принятие редактирования (кнопка с галочкой — соответствует нажатию Enter); вызов Мастера функций (кнопка «=»). Редактировать содержимое ячейки можно непосредственно в самой ячейке или в поле содержимого ячейки. В первом случае надо выполнить двойной щелчок по ячейке или нажать клавишу F2, во втором — активизировать ячейку и щелкнуть в поле содержимого строки формул.
Указатель ячейки — рамка, выделяющая ячейку.
Ячейки Excel
Допускаются ссылки на другой лист, другую книгу (внешние ссылки) и другое приложение (удаленные ссылки).
Группа ячеек может быть выделена для выполнения различных операций. Если выделяемая область таблицы имеет прямоугольную форму, то технология ее выделения обычная — либо мышью с удержанием левой кнопки, либо клавиатурой — при нажатой клавише Shift. Если выделяются несмежные ячейки таблицы, то сначала следует выделить первую область, а затем, удерживая Ctrl, мышью выделить другие.
Для ввода данных в ячейку, ее надо активизировать, набрать данные с клавиатуры и нажать Enter или щелкнуть мышью в другой ячейке.
Данные, вводимые в ячейку, могут быть текстом, формулой или ссылкой. Excel может определять тип данных автоматически. Если введен текст, он обычно выравнивается по левому краю ячейки. Если длина текста превышает ширину ячейки, но ячейка справа пуста, текст на экране будет занимать эту ячейку. Если же ячейка справа занята, то на экране текст ограничивается размером ячейки. Фактически же ячейка содержит полный текст, в чем можно убедиться, просмотрев ее содержимое в строке формул.
Вводимые числа выравниваются по правому краю ячейки. Для разделения целой и дробной частей десятичных чисел используется запятая. Если целая часть числа не умещается по ширине ячейки, на экран выводятся знаки «####».
Запись формул и ссылок на другие ячейки начинается со знака «=», за которым следует выражение или ссылка. Если выражение синтаксически правильно, то после завершения ввода формулы в ячейке размещается результат вычисления, иначе — сообщение типа «#ИМЯ?».
Достоинство использования ссылок и формул со ссылками в том, что при изменении данных в исходных ячейках, результат в ячейке с формулой будет скорректирован автоматически.
Форматирование таблиц и вычисления в Excel
Лабораторная работа №7
Форматирование таблиц и вычисления в Excel
Цель работы: приобретение навыков построения и форматирования таблиц в табличном процессоре Excel, выполнения вычислений в таблицах, приобретения навыков копирования и автозаполнения ячеек таблицы.
Введение в Excel
Excel — табличный процессор, входящий в комплект Microsoft Office и предназначенный для обработки информации, представленной в табличной форме. В отличие от текстового процессора Word, предназначенного для оформления текстовых документов, Excel специализирован для выполнения вычислений с табличными данными. Excel имеет большое количество встроенных функций для математических, статистических, финансовых и других вычислений. С другой стороны, Excel — это среда, ориентированные на непрограммирующего пользователя, что делает его популярным среди экономистов, бухгалтеров и других специалистов, обрабатывающих табличные данные.
Документ Excel называется рабочей книгой, состоящей из набора рабочих листов. Книга хранится в виде файла с расширением. xls. Одна книга может содержать до 256 рабочих листов.
Элементы рабочего окна Excel
Excel имеет стандартный интерфейс Windows. Поэтому рассмотрим только те элементы рабочего окна, которые отличают Excel от Word.
Строка меню отличается от меню в Word заменой пункта «Таблица» на пункт «Данные«, предназначенный для выполнения операций сортировки, фильтрации и некоторых других над табличными данными.
На панели форматирования следует отметить кнопку «Объединить и поместить в центре«, а также «Денежный формат» и «Процентный формат«.
Строка формул состоит из поля адреса, управляющих кнопок и поля содержимого ячейки. При активизации ячейки таблицы в этих полях появляется соответствующая информация. Управляющие кнопки выполняют: отмену редактирования ячейки (кнопка с крестом); принятие редактирования (кнопка с галочкой — соответствует нажатию Enter); вызов «Мастера функций» (кнопка «=»). Редактировать содержимое ячейки можно непосредственно в самой ячейке или в поле содержимого ячейки. В первом случае надо выполнить двойной щелчок по ячейке или нажать клавишу F2, во втором — активизировать ячейку и щелкнуть в поле содержимого строки формул.
Столбцы и строки таблицы имеют заголовки. Для столбцов заголовки — буквы латинского алфавита; для строк — целые числа. При большом количестве столбцов их заголовки состоят из двух латинских букв, например: AD, BF. Всего на рабочем листе можно разместить 256 столбцов и 65536 строк.
Указатель ячейки — рамка, выделяющая ячейку.
Ячейки Excel
Допускаются ссылки на другой лист, другую книгу (внешние ссылки) и другое приложение (удаленные ссылки).
Ячейка таблицы, на которой стоит указатель ячейки является активной. Для активизации необходимо щелкнуть кнопкой мыши по ячейке.
Группа ячеек может быть выделена для выполнения различных операций. Если выделяемая область таблицы имеет прямоугольную форму, то технология ее выделения обычная — либо мышью с удержанием левой кнопки, либо клавиатурой — при нажатой клавише Shift. Если выделяются несмежные ячейки таблицы, то сначала следует выделить первую область, а затем, удерживая Ctrl, мышью выделить другие.
При необходимости ссылки на прямоугольную область таблицы говорят о диапазоне ячеек. В общем случае диапазон записывается как две ссылки на верхнюю левую и нижнюю правую ячейки прямоугольной области, разделенные двоеточием, например D5:F13. Диапазоном может быть частичный столбец (например, R4:R25) или частичная строка (например, A12:Z12). Если двоеточием разделены только номера строк или столбцов (например, A:F или 5:13), то в диапазон входят все ячейки таблицы, ограниченные этими строками или столбцами.
Для ввода данных в ячейку, ее надо активизировать, набрать данные с клавиатуры и нажать Enter или щелкнуть мышью в другой ячейке.
Данные, вводимые в ячейку, могут быть текстом, формулой или ссылкой. Excel может определять тип данных автоматически. Если введен текст, он обычно выравнивается по левому краю ячейки. Если длина текста превышает ширину ячейки, но ячейка справа пуста, текст на экране будет занимать эту ячейку. Если же ячейка справа занята, то на экране текст ограничивается размером ячейки. Фактически же ячейка содержит полный текст, в чем можно убедиться, просмотрев ее содержимое в строке формул.
Вводимые числа выравниваются по правому краю ячейки. Для разделения целой и дробной частей десятичных чисел используется запятая. Если целая часть числа не умещается по ширине ячейки, на экран выводятся знаки «####».
Запись формул и ссылок на другие ячейки начинается со знака «=», за которым следует выражение или ссылка. Если выражение синтаксически правильно, то после завершения ввода формулы в ячейке размещается результат вычисления, иначе — сообщение типа «#ИМЯ?».
Достоинство использования ссылок и формул со ссылками в том, что при изменении данных в исходных ячейках, результат в ячейке с формулой будет скорректирован автоматически.
Организация вычислений формулы и их запись
Запись формулы в ячейку начинается со знака «=». Далее записывается арифметическое выражение с использованием знаков математических операций, круглых скобок, ссылок и стандартных функций Excel. Правила записи — обычные математические.
Рассмотрим организацию простейших вычислений на примере таблицы стоимости автоперевозок.
Будем считать, что себестоимость перевозок складывается из затрат на топливо и других затрат, пропорциональных весу автомобиля с грузом и пройденному расстоянию. Тогда затраты X можно вычислить по формуле:
Формула для вычисления стоимости перевозки до Корочи в ячейке F9 имеет вид:
Суммарный вес перевезенного груза и суммарную стоимость перевозок можно рассчитать по формулам =СУММ(E9:E13) и СУММ(F9:F13).
Отметим следующие особенности оформления рассмотренной таблицы и выполнения вычислений.
· Некоторые ячейки в таблице являются объединенными. Если на такую ячейку делается ссылка, то ее координатой считается верхняя левая ячейка объединенной области. Пример — ссылка на С4.
· Данные, являющиеся аргументами формул, должны помещаться в отдельные ячейки.
· Результаты в ячейках F9:F14 выровнены по правому краю ячеек. Для задания видимого отступа справа для этих данных введен дополнительный столбец G и убрано вертикальное обрамление между F9:F14 и G9:G14.
· Разрядность десятичной части результатов в ячейках F9:F14 задана через меню «Формат/Ячейки/Число/Число десятичных знаков» — 2.
· Дата в ячейке F6 вставлена с помощью «Мастера функций» (Стандартная панель инструментов) — функция СЕГОДНЯ(), группа «Дата и время». Для редактирования формы представления даты необходимо выполнить «Формат/Ячейки/Число» и выбрать нужный формат из списка.
Ошибки в формулах
Для облегчения поиска ошибок в формулах целесообразно перейти в режим показа формул в ячейках через «Сервис/Параметры/Вид» и установить флажок «Формулы». При этом ширина ячеек таблицы будет автоматически увеличена и вместо результатов и сообщений об ошибках будут показаны формулы.
· #REF! — ссылка на несуществующую ячейку;
· #DIV/0 — деление на ноль;
· #NUM! — нарушение математических правил, например, корень из отрицательного числа;
· #ИМЯ? — ошибочное имя функции;
· #ЗНАЧ! — аргумент недопустимого типа.
Если формула в ячейке содержит ссылки на другие ячейки, то для облегчения поиска связей формулы, следует выполнить «Сервис/Зависимости/Влияющие ячейки». Текущая ячейка будет соединена синими линиями со стрелками со всеми влияющими ячейками. Через «Сервис/Зависимости/Зависимые ячейки» можно установить на какие ячейки влияет активная ячейка.
Общие принципы форматирования
Главный принцип форматирования в Microsoft Excel такой же, как и у других приложений Windows: сначала выделить форматируемую область, затем – применить инструменты форматирования. Основные инструменты форматирования вынесены на панель форматирования и большинство из них совпадают с инструментами текстового процессора Word. Среди новых следует отметить инструмент «Объединить и поместить в центре» и кнопки для задания денежного и некоторых числовых форматов.
Выделение отдельной прямоугольной области таблицы выполняется либо протяжкой указателя мыши, либо клавишами со стрелками при удержании клавиши Shift. При этом среди выделенных одна ячейка является активной – цвет ее заливки остается белым. Если выполнить щелчок мышью по отдельной ячейке, она будет одновременно выделенной и активной.
Для выделения нескольких несмежных прямоугольных областей следует удерживать нажатой клавишу Ctrl. При этом активной будет только одна ячейка, как показано на рисунке. 
Операции копирования и перемещения
Операции копирования и перемещения можно выполнить протяжкой мыши за границу выделенной области. При удержании клавиши Ctrl будет выполняться копирование, без нее – перемещение.
Для копирования формул в правом нижнем углу выделенной области имеется специальный маркер (см. раздел «Формулы и их запись»).
В соответствие с номером варианта, заданным в приложении, оформить таблицу аналогично примеру, приведенному в разделе «Теоретические сведения», как показано в разделе, включая заливку и толщину обрамления. Значения в ячейках выделенных голубым цветом, должны быть вычислены по формулам.
1). Внимательно ознакомиться с теоретическими сведениями, приведенными в лабораторной работе;
— открыть табличный процессор Microsoft Excel;
— создать в нем новый документ и сохранить его в своей личной папке под именем Выполнение№07_N.xls, где N – номер вашего варианта;
— организовать на листе Excel таблицу, выбранную согласно своему варианту:
а). ввести все необходимые данные, кроме данных из ячеек, выделенных голубым цветом;
б). произвести вычислить в ячейках, выделенных голубым цветом, по соответствующим формулам;
в). В двух произвольных ячейках, значение которых высчитывается по формуле, заменить в этих формулах ссылки на ячейки, на аналогичные, но абсолютные по строкам и по столбцам;
3). Копирование и автозаполнение ячеек:
— Выделить две ячейки, содержащие абсолютные ссылки и скопировать их в другое место листа, начиная с ячейки А20;
— В ячейке А25 ввести номер своего варианта;
— Используя автозаполнение, заполнить строку с номером 25 последовательностью из девяти чисел с шагом 2, начиная со значения, введенного в ячейке А25. Автозаполнение производиться либо вручную, по двум значениям последовательности, либо автоматически, используя окно «Прогрессия» меню «Правка/Заполнить»
— Открыть окно «Формат ячейки» через меню «Формат\Ячейки…». Изучить содержимое двух его вкладок: «Границы» и «Вид»;
— Используя вкладку «Вид» окна «Формат ячейки» произвести в ячейках таблицы заливку цветом, повторив цветовое оформление таблицы по вашему варианту;
— Используя вкладку «Граница» окна «Формат ячейки произвести добавление границ ячеек и настроить их толщину. Толщина границ должна совпадать с соответствующей границе ячейки, в таблице из «Вариантов заданий к работе»;
1). Титульный лист, оформленный согласно применяемому ранее шаблону;
2). Цель лабораторной работы;
3). Лист Excel, содержащий результаты выполнения пунктов 2-4 раздела «Порядок выполнения»:
1. Для чего предназначен табличный процессор Excel?
2. Каковы основные элементы рабочего окна Excel?
3. Как называется документ Excel?
4. Из чего состоит рабочая книга?
5. Для чего предназначен пункт меню «Данные«?
6. Из чего состоит строка формул?
7. Как выполняется редактирование информации в строке формул?
8. Как выглядят заголовки строк и столбцов?
9. Что такое указатель ячейки?
10. Для чего используются ссылки на ячейку?
11. Какими могут быть ссылки по форме? По своим свойствам?
12. Чем отличаются относительные ссылки от абсолютных?
13. Как задаются относительные ссылки? Абсолютные?
14. Как можно выделить группу ячеек в таблице?
15. Как записывается диапазон ячеек?
16. Какого типа данные могут быть введены в ячейку?
17. В чем заключается достоинство использования ссылок и формул со ссылками?
18. Каковы правила записи формулы в ячейку таблицы Excel?
19. Как выделить диапазон ячеек в таблице?
20. Как задать диапазон ячеек?
21. Как скопировать формулу в указанный диапазон ячеек таблицы? Что произойдет при этом с относительными ссылками?
22. Перечислите особенности оформления таблицы и выполнения вычислений в рассмотренном примере таблицы стоимости перевозок?
23. Как перейти в режим показа формул?
24. Каковы основные типичные ошибки в формулах?
25. Как облегчить поиск связей формулы с ячейками, на которые формула содержит ссылки?
26. Что такое автозаполнение ячеек?
27. Как на листе Excel выделить несколько несмежных диапазонов?
29. Как переместить выделенную область на текущем листе?
30. Как скопировать выделенную область на текущем листе?