В чем заключается преимущество использования ссылок формулах
Математические операторы и ссылки на ячейки в формулах Excel
Одним из самых мощных инструментов Excel является возможность производить расчеты при помощи формул. Именно формулы делают электронные таблицы такими гибкими и полезными. Как и калькулятор, Excel может складывать, вычитать, умножать и делить. В данном уроке мы рассмотрим основные математические операторы, используемые в Excel, а также познакомимся с преимуществами использования ссылок на ячейки в формулах.
Математические (арифметические) операторы
Excel использует стандартные операторы для формул, такие как: знак плюс для сложения (+), минус для вычитания (-), звездочка для умножения (*), косая черта для деления (/) и циркумфлекс для возведения в степень (^).
Все формулы в Excel должны начинаться со знака равенства (=). Это связано с тем, что Excel приравнивает данные хранящиеся в ячейке (т.е. формулу) к значению, которое она вычисляет (т.е. к результату).
Основные сведения о ссылках
Несмотря на то, что в Excel можно создавать формулы, применяя фиксированные значения (например, =2+2 или =5*5), в большинстве случаев для создания формул используются адреса ячеек. Этот процесс называется созданием ссылок. Создавая ссылки на ячейки убедитесь, что формулы не содержат ошибок.
Использование ссылок в формулах дает ряд преимуществ, начиная от меньшего количества ошибок и заканчивая простотой редактирования формул. К примеру, вы легко можете изменить значения, на которые ссылается формула, без необходимости ее редактировать.
Используя математические операторы, совместно со ссылками на ячейки, можно создать множество простых формул. На рисунке ниже приведены несколько примеров формул, которые используют разнообразные комбинации операторов и ссылок.
В Excel существует несколько видов ссылок. Изучите уроки раздела Относительные и абсолютные ссылки, чтобы получить дополнительную информацию.
ГДЗ по информатике 11 класс учебник Босова параграф 1
1. Что понимают под табличным процессором и электронными таблицами?
2. Сравните интерфейс известных вам текстового и табличного процессоров. Что у них общего? Чем они различаются?
Я пользовался только MS Excel поэтому сравнить не с чем, но могу сказать что отличия в функционале.
3. Что такое адрес (имя) ячейки ЭТ? Как задаётся адрес ячейки, адрес диапазона ячеек?
Адресом ячейки любой электронной таблицы является пересечение ее строки и столбца.
Диапазон используемых ячеек может быть указан двумя путями: либо непосредственным набором с клавиатуры начального и конечного адресов ячеек, формирующих диапазон, либо выделением соответствующей части таблицы при помощи мыши или клавиш управления курсором. Удобнее задавать диапазон выделением ячеек.
4. Выясните, куда в табличном процессоре перемещается табличный курсор при нажатии клавиш Home, End, PageUp, PageDown. Куда перемещается табличный курсор при нажатии комбинации клавиш: Ctrl + →, Ctrl + ↓, Ctrl + ←, Ctrl + ↑, Ctrl + Home, Ctrl + End? Проведите аналогию с перемещениями текстового курсора в текстовом процессоре.
Home – в первую ячейку строки
PageUp – на 30 строк вверх
PageDown – на 30 строк вниз
Ctrl + ↓- до первой непустой ячейки вниз
Ctrl + ←- до первой непустой ячейки влево
Ctrl + ↑- до первой непустой ячейки вверх
Ctrl + Home – в ячейку А1
Ctrl + End – переход на последнюю ячейку, содержащую данные.
5. Какие типы данных могут быть занесены в ячейку ЭТ?
6. Какие существуют особенности ввода числовых значений в ЭТ?
7. Вспомните основные правила ввода формул в ЭТ. Где вы уже встречались с аналогичными правилами ввода арифметических выражений?
8. В чём суть принципа относительной адресации в ЭТ? Что происходит при копировании формул, содержащих относительные ссылки?
9. В каких случаях в формулах используются абсолютные ссылки?
Абсолютные ссылки: не изменяются при копировании.
10. В чём заключается преимущество использования ссылок в формулах?
Не нужно каждый раз вносить значения, можно казать ссылку на них.
11. На основании чего можно судить о том, что табличный процессор интерпретировал введённые в ячейку данные как текст? Как число?
Числа выравниваются по правому боку ячейки, текст по левому.
12. Сравните приёмы копирования и вставки данных в текстовом и табличном процессорах. Что у них общего? Чем они различаются?
В эксель при копировании можно копировать формулу или значение, в ворде так сделать нельзя. Общее в том что копируются и стили.
13. Как осуществляется автозаполнение ячеек?
14. Как ввести следующее четверостишие А. Ерикеева в одну ячейку электронной таблицы?
Необходимо вводить в одну ячейку без переходов.
15. Значение переменной х находится в ячейке А1, значение переменной у — в ячейке А2, значение переменной z — в ячейке АЗ. Запишите формулы для вычисления в электронных таблицах значений выражений:
16. Только путём ввода последовательностей составьте таблицу умножения:
За сколько операций вам удалось это сделать?
17. Дан фрагмент электронной таблицы:
Чему будет равно значение ячейки С2, если скопировать в неё формулу из ячейки С1?
18. В ячейке ВЗ записана формула =C$2+$D3+2. Какой вид приобретёт формула после копирования её в ячейку В2?
19. Измерьте длину, ширину и высоту кухни, прихожей и жилых комнат вашей квартиры. Создайте в табличном процессоре таблицу с результатами измерений. Вычислите площадь пола, площадь стен и объём каждого из помещений, а также общую площадь всех помещений.
20. В табличном процессоре создайте таблицу вида:
Занесите в таблицу информацию о десяти странах, имеющих самую большую численность населения. Введите в соответствующие ячейки формулы для вычисления:
1) общей площади и общего количества населения этих десяти стран (предусмотрите соответствующие ячейки под созданной таблицей с данными);
2) плотности населения в каждой из этих стран;
3) процентов, которые составляет население каждой из этих стран по отношению к общему количеству населения в мире.
22. Подготовьте краткое сообщение о первых электронных таблицах.
Идея создания электронной таблицы возникла у студента Гарвардского университета (США) Дэна Бриклина (Dan Bricklin) в 1979 г. Выполняя скучные вычисления экономического характера с помощью бухгалтерской книги, он и его друг Боб Франкстон (Bob Frankston), который разбирался в программировании, разработали первую программу электронной таблицы, названную ими VisiCalc.
VisiCalc скоро стала одной из наиболее успешных программ. Первоначально она предназначалась для компьютеров типа Apple II, но потом была трансформирована для всех типов компьютеров. Многие считают, что резкое повышение продаж компьютеров типа Apple в то время и было связано с возможностью использования на них табличного процессора VisiCalc. В скоропоявившихся электронных таблицах-аналогах (например, SuperCalc) основные идеи VisiCalc были многократно усовершенствованы.
Разработчикам Excel удалось найти золотую середину, максимально облегчив пользователю освоение программы и работу с ней. Благодаря этому Excel быстро завоевала популярность среди широкого круга пользователей. В настоящее время, несмотря на выпуск компанией Lotus новой версии электронной таблицы, в которой использована трехмерная таблица с улучшенными возможностями, Excel занимает ведущее место на рынке табличных процессоров.
Имеющиеся сегодня на рынке табличные процессоры способны работать в широком круге экономических приложений и могут удовлетворить практически любого пользователя.
Ссылки в Excel
Ячейка таблицы, на которой стоит указатель ячейки является активной. Для активизации необходимо щелкнуть кнопкой мыши по ячейке.
Группа ячеек может быть выделена для выполнения различных операций. Если выделяемая область таблицы имеет прямоугольную форму, то технология ее выделения обычная — либо мышью с удержанием левой кнопки, либо клавиатурой — при нажатой клавише Shift. Если выделяются несмежные ячейки таблицы, то сначала следует выделить первую область, а затем, удерживая Ctrl, мышью выделить другие.
При необходимости ссылки на прямоугольную область таблицы говорят о диапазоне ячеек. В общем случае диапазон записывается как две ссылки на верхнюю левую и нижнюю правую ячейки прямоугольной области, разделенные двоеточием, например D5:F13. Диапазоном может быть частичный столбец (например, R4:R25) или частичная строка (например, A12:Z12). Если двоеточием разделены только номера строк или столбцов (например, A:F или 5:13), то в диапазон входят все ячейки таблицы, ограниченные этими строками или столбцами.
Для ввода данных в ячейку, ее надо активизировать, набрать данные с клавиатуры и нажать 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. Как скопировать выделенную область на текущем листе?