Ввод формул и вычисления в Excel

Ввод формул всегда начинается с ввода знака равно «=». Формулы могут содержать числа, адреса ячеек, функции и знаки математических операций. В формулу не может входить текст и специальные символы. Необходимо учитывать порядок выполнения арифметических операций: =(11,8+F4)^2/cos(D7). По умолчанию ссылки в формулах рассматриваются как относительные, то есть при копировании формулы адреса в ссылках автоматически изменяются.

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

<имя функции> (<арг1>;<арг2>;…<аргn>)

Например, = СРЗНАЧ(А1;А2;В4:В8;F5)

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

· математические (СУММ, КОРЕНЬ, ЦЕЛОЕ, SIN, EXP и т.д.);

· статистические (MAX, MIN,СРЗНАЧ, СЧЕТ и т.д);

· логические (ЕСЛИ и т.д);

· финансовые

· текстовые

· базы данных и т.д

После выбора категории выбирается функция и вводятся аргументы.

Существует несколько способов ввода формул в ячейки. Например, надо подсчитать сумму в ячейках В4,В5,В6, В7 и В8.

1сп. = В4+В5+В6+ В7+В8

2 сп. = СУММ(В4:В8)

3сп. Выделить блок В4:В8 и нажать в панели нстр-в значок «автосумма» .

4 сп. , выбрать категорию «математическая», выбрать функцию СУММ и выделить блок В4:В8.

Можно задавать следующие числовые форматы (Формат, Ячейки, Число): о общий, числовой, денежный, процентный, экспоненциальный.

46. Автоматический пересчет ссылок при копировании формул в Ехсеl. Относительные и абсолютные ссылки.

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

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

Относительные и абсолютные ссылки

Ссылки в формуле указывают на позицию ячеек относительно активной ячейки. Таким образом, адреса ячеек в ссылках при копировании формулы автоматически изменяются. Такие ссылки называются относительными ссылками на ячейку. Например, запишем в ячейку А3 формулу =А1+А2, скопируем эту формулу из А3 в ячейку В3. В результате в ячейке В3 мы увидим формулу =В1+В2, т.е. ссылки изменились автоматически. Таким образом, относительная ссылка означает, что при копировании формулы в другие ячейки вдоль по строке (столбцу) в формулу будут подставляться данные из ячеек, сдвинутых относительно начальной настолько, насколько изменилось местоположение копируемой формулы.

Если ссылка при копировании не должна изменяться, то используют так называемые абсолютные ссылки на ячейку. В этом случае указывается позиция ячейки на рабочем листе. Поэтому при копировании или перемещении формул указанная в абсолютной ссылке ячейка не изменяется. Признаком абсолютной ссылки является знак доллара ($). Вернемся к рассмотренному выше примеру. Если мы изменим формулу в ячейке А3 следующим образом: =А1+$A$2, то при копировании в ячейке В3 обнаружим: =В1+$A$2, т.е. относительная ссылка автоматически изменилась, а абсолютная – нет.

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

Примечание. В режиме редактирования вид ссылки можно изменять с помощью клавиши F4: при однократном нажатии относительная ссылка превращается в абсолютную (абсолютная – в относительную), а при повторном нажатии – в смешанную.

Ссылки на листы и книги

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

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

В ссылке на другой лист имя листа указывается перед адресом ячейки и отделяется от него восклицательным знаком, например: Лист2!А1.

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

Если удалить лист, на содержимое которого существует ссылка в формуле, соответствующие ссылки заменятся значением ошибки #ССЫЛКА, а результат вычислений не будет отображаться. После удаления содержимого влияющей ячейки ее значение при вычислениях будет считаться равным 0.

Ссылка на ячейку из другой рабочей книги (внешняя ссылка) создается аналогичным образом.

Вопрос 47.
Функция в Excel – это предустановленная формула, которая выполняет вычисления, используя заданные значения в определенном порядке. С помощью функций можно ускорять выполнение задач, упрощать формулы и реализовывать вычисления.
Классификация функций в Excel:

1. логические (если, еслиошибка, и, или, истина, ложь, не)
2. математические (знак, корень, кореньпи, мобр, округл, пи, произвед, промежуточные.итоги, радианы, степень, сумм, суммесли, суммеслимн, суммкв, суммквразн, суммпроизвцелое, частное)
3. статистические (fрасп, fраспобр, pearson, zтест, бетаобр, бетарасп, биномрасп, вейбулл, вероятность, гамманлог, гаммаобр, гаммарасп, гипергеомет, дисп, диспа, диспр, диспра, доверит, квадроткл, квартиль, квпирсон, ковар, коррел, критбином, лгрфприбл, линейн, логнормобр, логнормобр, логнормрасп, макс, макса, медиана, мин, мина, мода, наибольший, наименьший, наклон, нормализация, нормобр, нормрасп, нормстобр, нормстрасп, отрбиномрасп, отрезок, перест, персентиль, предсказ, процентранг, пуассон, ранг, рост, скос, сргарм, сргеом, срзнач, срзнача, срзначесли, срзначеслимн, сроткл, стандотклон, стандотклона, стандотклонп,стандотклонпа, стошух, стьюдрасп, стьюдраспобр, счёт, счётесли, счётеслимн, счётз, считатьпустоты, тенденция, ттест, урезсреднее, фишер, фишеробр, фтест, хи2обр, хи2расп, хи2тест, частота, экспрасп, эксцесс)
4. финансовые (аморув, аморум, апл, асч, безраспис, бс, всд, ддоб, длит, днейкупон, днейкупондо, днейкупонпосле, доход, доходкчек, доходпервнерег, доходпогаш, доходпослнерег, доходскидка, инорма, кпер, купонпред, купонслед, мвсд, мдлит, накопдоход, накопдоходпогаш, номинал, общдоход, общплат, осплт, плт,получено, процплат, прплт, пс, пуо, равнокчек, рубль.дес, рубль.дробь, скидка, ставка, фуо, цена, ценакчек, ценапервнерег, ценапогаш, ценапослнерег, ценаскидка, числкупон, чиствндох, чистнз, чпс, эффект)
5. текстовые (баттекст, длстр, заменить, значен, кодсимв, левсимв, найти, печсимв, повтор, подставить, поиск, правсимв, прописн, пропнач, пстр, рубль, сжпробелы, символ, совпад, строчн, сцепить, т, текст, фиксированный)
6. инженерные (бессель.i, бессель.j,бессель.k,бессель.y, восьм.в.дв, восьм.в.дес, восьм.в.шестн, дв.в.восьм, дв.в.дес, дв.в.шестн, дельта, дес.в.восьм, дес.в.дв, дес.в.шестн, дфош, комплексн, мним.abs, мним.cos, мним.exp, мним.ln, мним.log10, мним.log2, мним.sin, мним.аргумент, мним.вещ, мним.дел, мним.корень, мним.произв, мним.разн, мним.сопряж, мним.степень, мним.сумм, мним.часть, порог, преобр, фош, шестн.в.восьм, шестн.в.дв, шестн.в.дес)
7. аналитические (кубзначение, кубмнож, кубпорэлемент, кубсвойствоэлемента, кубчислоэлмнож, кубэлемент, кубэлементкип)
8. дата и время (времзнач, время, год, дата, датазнач, датамес, день, деньнед, дней360, долягода, конмесяца, месяц, минуты, номнедели, рабдень, сегодня, тдата, час, чистрабдни)
9. ссылки и массивы (адрес, впр, выбор, гиперссылка, гпр, двссыл, дрв, индекс, области, поискпоз,получить.данные.сводной.таблицы, просмотр, смещ, столбец, строка, трансп, числстолб, чстрок)
10. проверка свойств и значений (елогич, енд, енетекст, енечёт, еош, еошибка, епусто, ессылка, етекст, ечётн, ечисло, информ, нд, тип, тип.ошибки, ч, ячейка)
11. работа с базой данных (бддисп, бддиспп, бдпроизвед, бдсумм, бизвлечь, бсчёт, бсчёта, дмакс, дмин, дсрзнач, дстандоткл, дстандотклп)

Для корректной работы, функция должна быть написана в определенной последовательности, которая называется синтаксис. К базовому синтаксису функции относятся знак равенства (=), имя функции (например, СУММ) и один или более аргументов. Аргументы содержат информацию, которую необходимо вычислить. В Excel существуют функции, которые не содержат ни одного аргумента. К примеру, функция СЕГОДНЯ() возвращает текущую дату из системного времени вашего компьютера.

Вопрос 48.

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

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

1. Поставьте курсор-ячейку в любое место.

2. Перейдите в меню «Формулы».

3. Нажмите «Вставить функцию».

4. В списке выберите «МАКС» или «МИН».

5. В окне «Аргументы» введите адреса диапазона, максимальное\минимальное значение которого вам нужно узнать.

6. Вместо адреса можно написать несколько чисел. Тогда система покажет самое большее из них.

7. Нажмите «OK». В клетке, в которой стоял курсор, появится результат.

Среднее

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

2. Перейдите на вкладку «Формулы».

3. Нажмите на «Вставить функцию».

4. Выберите «СРЗНАЧ».

.

Вопрос 49.

Статистическая функция СЧЕТ подсчитывает количество ячеек в списке аргументов, которые содержат только числовые значения.
Если необходимо подсчитать количество непустых ячеек в диапазоне, то можно воспользоваться статистической функцией СЧЕТЗ. Непустыми считаются ячейки, содержащие текст, числовые значения, дату, время, а также логические значения ИСТИНА или ЛОЖЬ.
Статистическая функция СЧЕТЕСЛИ позволяет производить подсчет ячеек рабочего листа Excel с применением различного вида условий. СЧЕТЕСЛИ позволяет подсчитывать ячейки, содержащие текстовые значения.
Логическое условие функции СЧЕТЕСЛИ может содержать групповые символы: * (звездочку) и? (вопросительный знак). Звездочка обозначает любое количество произвольных символов, а вопросительный знак – один произвольный символ.
Если необходимо подсчитать количество ячеек, которые содержат ровно четыре символа, то используйте эту формулу:

Если одного условия Вам будет недостаточно, Вы всегда можете воспользоваться статистической функцией СЧЕТЕСЛИМН. Данная функция позволяет подсчитывать ячейки в Excel, которые удовлетворяют сразу двум и более условиям.

Вопрос 50.

Функции Дата и время позволяют работать со значениями даты и времени в формулах. Например, можно использовать в формуле текущую дату, воспользовавшись функцией СЕГОДНЯ .
"Классическая" форма- 3.10.2006
Сокращенная форма-3.10.06
С использованием дефисов-3-10-6
С использованием дроби- 3/10/6

Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.).
Функция ВРЕМЯ не может возвращать значение от 24 и выше, т.е. если для аргумента «Часы» задать число 25, то результатом будет 1 час.
Если для аргумента «День» задать число, превышающее количество дней в указанном месяце, то лишние дни будут переданы на следующие месяцы, т.е. если для января указать 32 дня, то функция вернет 1 февраля.
Время вводится в ячейки с использованием двоеточия. Например 16:45

По желанию можно дополнительно уточнить количество секунд - вводя их также через двоеточие: 16:45:30

И, наконец, никто не запрещает указывать дату и время сразу вместе через пробел, то есть

27.10.2012 16:45

Вопрос 51.

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

Количество дней между двумя датами

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

Вопрос 52.

Логические функции предназначены для проверки одного или нескольких условий. Например, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое, если оно ложно.
Логические выражения используются для записи условий, в которых сравниваются числа, функции, формулы, текстовые или логические значения. Любое логическое выражение должно содержать по крайней мере один оператор сравнения, который определяет отношение между элементами логического выражения.
Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).
Функция ЕСЛИ

Функция ЕСЛИ имеет следующий синтаксис :=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь).
Функция И является связующим звеном между несколькими условиями. Только при выполнении всех условий, которые связывает данная функция, она возвращает значение ИСТИНА. Если хотя бы один аргумент сообщает значение ЛОЖЬ, то и оператор И в целом возвращает это же значение. Общий вид данной функции: =И(лог_значение1;лог_значение2;…).
Функция ИЛИ, наоборот, возвращает значение ИСТИНА даже в том случае, если только один из аргументов отвечает условиям, а все остальные ложные. Её шаблон имеет следующий вид: =И(лог_значение1;лог_значение2;…).
Функция НЕ имеет всего лишь один аргумент. Она меняет значение выражения с ИСТИНА на ЛОЖЬ в пространстве указанного аргумента. Общий синтаксис формулы выглядит следующим образом: =НЕ(лог_значение).

Вопрос 53.

Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.

=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)
Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.
Операторы IF очень надежные и как основа много моделей электронной таблицы, но они также причину многих проблем электронной таблицы. В идеале Если вычисляются следует применять на минимальными условий, например мужского/женщина Да/Нет или возможно, лишь некоторые из них, но иногда может потребоваться оценить более сложные сценарии, требующие вложенности * более 3 функций Если вместе.

* "Вложенность" означает объединение нескольких функций в одной формуле.

Вопрос 54.

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

· Мастер диаграмм;

· Панель Диаграммы.

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

Типы диаграмм

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

· линейчатые диаграммы и гистограммы могут быть использованы для иллюстрации соотношения отдельных значений или показа динамики изменения данных за определенный период времени;

· график отражает тенденции изменения данных за определенные промежутки времени;

· круговые диаграммы предназначены для наглядного отображения соотношения частей и целого

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

· диаграмма с областями подчеркивает величину изменения данных во времени, показывая сумму введенных значений, а также демонстрирует вклад отдельных значений в общую сумму;

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

· лепестковая диаграмма позволяет сравнивать общие значения из нескольких рядов данных;

· поверхностная диаграмма используется для поиска наилучшего сочетания двух наборов данных;

· пузырьковая диаграмма представляет разновидность точечной диаграммы, где два значения определяют положение пузырька, а третье – его размер;

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

1. Заголовок диаграммы должен четко описывать, что представлено на ней.

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

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

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

5. Горизонтальная ось (также известная как ось X) является горизонтальной частью диаграммы. Горизонтальная ось представляет категории. В данном примере каждый квартал содержит свою группу.

Вопрос 55.

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

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

Фильтрация списков

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

Вопрос 56.

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

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

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

· глобальные — вычислительная сеть объединяет абонентов, расположенных в различных странах, на различных континентах. Глобальные вычислительные сети позволяют решить проблему объединения информационных ресурсов человечества и организации доступа к этим ресурсам;

· региональные — вычислительная сеть связывает абонентов, расположенных на значительном расстоянии друг от друга. Она может включать абонентов большого города, экономического региона, отдельной страны;

· локальные — вычислительная сеть объединяет абонентов, расположенных в пределах небольшой территории. К классу локальных сетей относятся сети отдельных предприятий, фирм, офисов и т. д.

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

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

Ячеистая топология – получается из полносвязной путем удаления некоторых связей. Непосредственно связываются только те компьютеры, между которыми происходит интенсивный обмен данными. Даная топология характерна для глобальных сетей

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

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

Топология Звезда – каждый компьютер отдельным кабелем подключается к общему устройству – концентрат (хаб). Главное преимущество перед общей шиной – большая надежность. Недостаток – высокая стоимость оборудования и ограниченное кол-во узлов в сети (т.к. концентрат имеет ограниченное число портов)

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