Связь нескольких таблиц с помощью формул

Билет 18.

MS Excel, ввод формул, примеры.

Формулы:

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

· Связь нескольких таблиц с помощью формул

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

Формула может состоять из математических операторов, значений, адресов ячеек и имена функций. Результатом выполнения формулы есть некоторое новое значение, содержащееся в ячейке, где находится формула. Формула начинается со знака равенства "=". В формуле используются арифметические операторы + (сложение), - (вычитание), * (умножение), / (деление). Порядок вычислений определяется обычными математическими законами.

Примеры формул:

=(А4+В8)*С6

=F7*С14+B12

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

Чтобы ввести формулу, вводят сначала знак «=», а затем саму формулу. Активная ячейка и строка формул отображают формулу так, как она введена. Если формула закончена, нажимают клавишу Enter; активная ячейка отобразит результат вычисления формулы. Строка формул показывает саму формулу, когда эта ячейка активна.

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

На вкладке Главнаягруппы Редактированиекоманда Суммаоткрывает список доступных функций (рис. 4.5): Сумма, Среднее, Число, Максимум, Минимум. Для использования данных функций необходимо выделить диапазон ячеек, которые входят в формулу и нажать клавишу Enter.

Рис. 4.5. Пример вычислений, используя встроенные функции

В таблице 4.1. представлено описание функций, используемые в примере (рис. 4.5).

Таблица 4.1.

Название функции	Вид формулы	Значение формулы
Сумма	=СУММ(А2:А5)	суммирование аргументов
Среднее	=СРЗНАЧ(B2:B5)	возвращение среднее арифметическое своих аргументов, которые могут быть числами, именами, массивами и ссылками на ячейки с числами
Число	=СЧЁТ(C2:C5)	подсчет количества ячеек в диапазоне, который содержит числа
Максимум	=МАКС(D2:D5)	возвращение наибольшего значения из списка аргументов
Минимум	=МИН(E2:E5)	возвращение наименьшее значения из списка аргументов

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

При вводе формул возможны следующие ошибки (таблица 4.2).

Таблица 4.2.

Вид ошибки	Значение
# Имя?	использован неправильный адрес ячейки
# Дел/0!	произведено деление на ноль
# Знач!	вместо числа в одной из ячеек находится текст
# Ссылка!	ячейка, к которой обращается формула, была удалена
###########	результат не уместился в границах ячейки, необходимо увеличить ширину столбца

Связь нескольких таблиц с помощью формул

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

Например, задача – построить на отдельных листах три таблицы «Отчет за 2011 год», «Отчет за 2012 год», «Отчет за 2011-2012гг.», произвести необходимые расчеты, связав таблицы.

На рисунке 4.6. представлена таблица «Отчет за 2011 год».

Рис. 4.6. Пример таблицы «Отчет за 2011 год» на листе 1

При создании таблицы используйте команды Объединить ячейки, Ориентация(повернуть текст вверх), Перенос по словами, Заливка, Границы группы Шрифтвкладки Главная. Для заполнения пустых ячеек в таблице нужно воспользоваться формулой суммирования: сделать активной ячейку D3 и выбрать команду Суммав группе Редактированиевкладки Главная. Появиться формула =СУММ(B3:C3), и ячейки В3:С3 будут выделены синей рамкой, если необходимо задать другой диапазон, то он выделяется с помощью мыши (рис. 4.7). После нажатия клавиши Enterв ячейкеD3отобразиться результат – 254. Для заполнения ячейкиD4 можно воспользоваться операцией копирования: сделать активной ячейку, где введена формула (D3), подвести указатель мыши к правому нижнему краю ячейки (он измениться на черный небольшой крестик) и протянуть рамку вниз. Автоматически программа произведет расчет для следующей группы ячеек.

Аналогичным образом заполнить оставшиеся ячейки.

Рис. 4.7. Ввод формулы Сумма

На рисунке 4.8. представлена таблица «Отчет за 2012 год».

Рис. 4.8. Пример таблицы «Отчет за 2012 год» на листе 2

Таблица «Отчет за 2012 год» - это копия предыдущей таблицы с измененными данными, поэтому можно использовать операцию копирования. Выделить таблицу на листе 1, нажать команду Копироватьиз группы Буфер обмена вкладки Главная. Перейти на лист 2, сделать активной ячейку А1 и нажать команду Вставитьиз группы Буфер обмена вкладки Главная. Внести нужные изменения в таблицу.

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

На рисунке 4.9. представлена таблица «Отчет за 2011-2012 гг.».

Рис. 4.9. Пример таблицы «Отчет за 2011-2012 гг.» на листе 3

Данная таблица заполняется с учетом данных таблиц на листах 1 и 2. Чтобы ввести формулу в ячейку В2 необходимо сделать ее активной, поставить знак «=», перейти на лист 1 и выбрать данные за первые два полугодия (ячейка D3), затем поставить знак «+», выбрать ячейку, где подсчитаны данные за третье и четвертое полугодия (ячейка G3), нажать клавишу Enter. На листе 3 в ячейкеВ2 появится значение 445, таким образом были связаны формулами таблицы с разных листов.

Примечание. Если произвести изменения данных в первой таблице (например, по плану за первый квартал 2011 года было 146), то изменения автоматически произойдут и в таблице на листе 3.

Аналогичным образом заполнить ячейки В3, С2, С3. В последнем столбце «Среднее значение» произвести вычисления, используя функцию Среднееиз группы Редактированиевкладки Главная.

2. Определение глобальной информационной сети интернет.

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

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

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

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

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

Существуют также компьютеры, которые непосредственно подключены к глобальной сети. Они называются хост - компьютерами (host - хозяин). Хост – это любой компьютер, являющийся постоянной частью Интернета, т.е. соединенный по Internet – протоколу с другим хостом, который в свою очередь, соединен с другим, и так далее.