1. Основные идеи spreadsheet.

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

Фамилия	Рост	Вес
Иванов	57	75
Петров	195	38

Таким образом, каждому объекту соответствует строка описывающих его значений.

S preadsheet схожи базами данных, но таковыми не являются: spreadsheet есть единая таблица, тогда как data base – набор таблиц с установленными между ними связями.

2. MS-Excel. Форматирование по условию. Назначение, примеры.

На примере MS Office 2010:

Главная (вкладка)- Условное форматирование (раздел «Стили» на панели инструментов вкладки «Главная»).

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

Н апример, мы говорим компу: «А ну-ка выдели мне все, что больше сотки!»- и он послушно делает).

Совет: перед применением выделить ячейки.

3. MS-Excel. Синтаксис и понятие ссылки. Абсолютная и относительная форма ссылки. Назначение, примеры.

Ссылка в Microsoft Excel — адрес ячейки или связного диапазона ячеек. В каждом листе Excel может быть 2 в 8 степени столбцов и 2 в 16 степени строк (В Excel 2007 2 в 14 степени столбцов и 2 в 20 степени строк). Адрес ячейки определяется пересечением столбца и строки, как в шахматах или морском бое, например: A1, C16. Адрес диапазона ячеек задается адресом верхней левой ячейки и нижней правой, например: A1:C5.

Виды представления ссылок

Типы ссылок (типы адресации)

Ссылки в Excel бывают 3-х типов:

Относительные ссылки (пример: A1);

Абсолютные ссылки (пример: $A$1);

Смешанные ссылки (пример: $A1 или A$1, они наполовину относительные, наполовину абсолютные).

Знак $ здесь никакого отношения к денежным единицам не имеет, это лишь способ указать Excel тип ссылки. Различия между разными типами ссылок можно увидеть, если потянуть за маркер автозаполнения активной ячейки или диапазона ячеек, содержащих формулу со ссылками.

Относительные ссылки

Если вы ставите в какой то ячейке знак "=", затем щелкаете левой кнопкой мыши на какой то ячейке, Excel подставляет после "=" относительную ссылку на эту ячейку. Эта ссылка "запоминает", на каком расстоянии (в строках и столбцах) вы щелкнули ОТНОСИТЕЛЬНО положения ячейки, где поставили "=" (смещение в строках и столбцах). Например, вы щелкнули на ячейку 3-мя столбцами левее и на 2 строки выше. Если после нажатия Enter потянуть вниз за маркер автозаполнения, эта формула скопируется во все ячейки, через которые мы протянули. И в каждой ячейке эта ссылка будет указывать на ячейку, расположенную на 3 столбца влево и 2 строки вверх ОТНОСИТЕЛЬНО положения ссылки. Это можно проверить, дважды щелкнув на одной из скопированных формул, или выделив ее, и нажав F2.

Абсолютные ссылки

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

Чтобы сделать относительную ссылку абсолютной, достаточно поставить знак «$» перед буквой столбца и адресом строки, например $A$1. Более быстрый способ — выделить относительную ссылку и нажать один раз клавишу «F4», при этом Excel сам проставит знак «$». Если второй раз нажать «F4», ссылка станет смешанной такого типа A$1, если третий раз — такого $A1, если в четвертый раз — ссылка опять станет относительной. И так по кругу.

Смешанные ссылки

Смешанные ссылки являются наполовину абсолютными и наполовину относительными. Знак доллара в них стоит или перед буквой столбца или перед номером строки. Это самый сложный для понимания тип ссылки. Например, в ячейке записана формула «=A$1». Ссылка A$1 относительная по столбцу A и абсолютная по строке 1. Если мы потянем за маркер автозаполнения эту формулу вниз или вверх, то ссылки во всех скопированных формулах будут указывать на ячейку A1, то есть будет вести себя как абсолютные. Однако, если потянем вправо или влево — ссылки будет вести себя как относительные, то есть Excel будет пересчитывать ее адрес. Таким образом, формулы, созданные автозаполнением, будут использовать один и тот же номер строки ($1), но изменится номер столбца (A, B, C...).

Именованные ячейки

Для упрощения работы с ячейками или диапазоном ячеек можно дать ей/им имя, и затем обращаться к ячейке или диапазону не по его адресу, а по символьному имени. Именованные ячейки можно использовать везде, где можно использовать то значение, на которое указывает ссылка.

Примечание! Именованные ячейки по умолчанию являются абсолютными ссылками.

Для создания именованной ячейки нужно выделить нужную ячейку или диапазон, затем щелкнуть в текстовое поле (см. рис. ниже) ввести свое имя и нажать Enter (или же щелкнуть по ячейке правой кнопкой мыши и выбрать пункт «Присвоить имя»). Можно использовать русские имена. Имя не может содержать пробелов и начинаться с цифры. Именованная ячейка может ссылаться на несвязный диапазон ячеек (выделенный с "Ctrl").

Для вставки именованной ячейки можно воспользоваться кнопкой со стрелкой вниз:

или нажать клавишу "F3", откроется следующее окно:

Пример использования: "=СУММ(tablica_1);"

Для того что бы убрать имя именованной ячейки (например: чтобы присвоить другой ячейке это имя) - Вставка/имя/присвоить/удалить

4. MS-Excel. Функции расчета сложного банковского процента.

Раздел – Финансовые, функция – БС.

А ргументы:

Ставка – процентная ставка (кэп…).

Кпер – количество периодов.

Замечание: следим, чтобы период ставки соответствовал периодам начисления. Пример: ГОДОВАЯ ставка = 10%. Сколько получим за 2 месяца? В данном случае годовую ставку делим на 12 (получаем месячную ставку) и рассчитываем, то есть функция будет иметь вид: =БС(0,1/12;2;…)

Плт- периодические платежи. Платежи могут идти либо от на (вносим на счет), либо нам (снимаем). Если платим МЫ, то значение будет ОТРИЦАТЕЛЬНЫМ (ставим минус), если платят НАМ – ПОЛОЖИТЕЛЬНЫМ.

Замечание: если необходимо рассчитать будущую стоимость и при этом мы ни вносим деньги на счет, ни снимаем, то в этой части формулы ставится ноль либо просто пропускаем место. Пример: рассчитываем будущую стоимость 100 рублей положенных в банк на 5 лет под 7% годовых (положили и забыли). Формула будет иметь вид: =БС(0,07;5;0;100;0) или =БС(0,07;5;;100;0)

Пс – приведенная стоимость. Та сумма, которая есть сейчас (стоимость, приведенная к текущему периоду).

Тип – когда совершаются внесения/списания на счет/со счета. Имеет два значения: 0 – внесения/списания в конце периода;

- внесения/списания в начале периода.

5. MS-Excel. Анализ данных на примере корреляционного анализа.

Цель корреляционно – регрессионного анализа – построение уравнения регрессии. Для однофакторного анализа достаточно вбить значения на график и построить линию тренда. Ее уравнение (если R2 >0,7) и есть ответ на нашу задачу. Для двуфакторного анализа смотрим вторую лабу, там все есть.

6. MS-Excel. Сортировка и фильтрация. Назначение, примеры использования.

Сортировка выстраивает данные в определенном порядке (от меньшего к большему, от большего к меньшему и пр.)

Фильтрация выводит только значения, удовлетворяющие условиям отбора (больше, меньше, равно и пр.), а остальные скрывает.

Алгоритм:

Выделяем ячейки.

Вкладка «Главная» - раздел «Редактирование» на панели инструментов-«Сортировка и фильтр»

Выбираем сортировку («от а до я»/«от я до а»/«настраиваемая сортировка») или фильтр. При выборе фильтра на каждом столбце появляется выпадающий список, в котором доступны та же самая сортировка и собственно фильтр.

7. MS-Excel. Создание связей между рабочими листами.

Excel позволяет использовать в таблице данные с других листов и из других таблиц.

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

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

Excel позволяет создавать связи с другими рабочими листами и другими рабочими книгами трех типов:

• ссылка на другой рабочий лист в формуле связывания с использованием ссылки на другой лист;

• ссылка на несколько рабочих листов в формуле связывания с использованием трехмерной ссылки,

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

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

В таких случаях Excel ссылается на диапазоны ячеек с помощью трехмерных ссылок. Трехмерная ссылка устанавливается путем включения диапазона листов (с указанием начального и конечного листа) и соответствующего диапазона ячеек. Например, формула, использующая трехмерную ссылку, которая включает листы от Лист1 до Лист5 и ячейки А4:А8, может иметь следующий вид:=SUM(ЛИСТ1:ЛИСТ5!А4:А8).

Трехмерные ссылки можно включить в формулу и другим способом. Для этого достаточно щелкнуть на рабочем листе, который нужно включить в формулу. Но сначала начните формулу в ячейке, где хотите получить результат. Когда дойдет черед до использования трехмерной ссылки, щелкните на ярлычке первого листа, подлежащего включению в ссылку, затем нажмите (и не отпускайте) клавишу <Shift> и щелкните на ярлычке последнего листа, подлежащего включению в ссылку. После этого выделит нужные ячейки. Завершив построение формулы, нажмите клавишу <Enter>.

8. MS-Excel. Понятие диапазона. Приемы копирования, заполнения, именования.

Диапазон – некоторое количество ячеек (например столбец). Именуется аналогично именованным ячейкам (см. вопрос 3, пункт «Именованные ячейки») с той лишь разницей, что выделяется не одна ячейка, а несколько. Копируется Ctrl + C (при этом копируются также формулы). Заполнение: вводим значение в ячейку и протягиваем (в правом нижнем углу ячейки есть квадратик, наводим на него курсор и тянем). В этом случае во все ячейки вставляется одно и то же значение. Если мы заполняем две ячейки, выделяем их и аналогично протягиваем, то диапазон заполняется значениями от соседних с ним на величину шага, равного разнице между второй и первой ячейкой (пример: вводим значение «1» в одну ячейку, и «2» во вторую, выделяем, протягиваем на 3 ячейки вниз. В результате получим диапазон «1, 2, 3, 4, 5»)

9. MS-Excel. Приемы управления книгами.

Документ Excel имеет расширение "*.xls" ("*.xlsx" в Microsoft Office Excel 2007) и называется рабочая книга. Рабочая книга состоит из листов. По умолчанию их создается три. Переключаться между листами можно, используя закладки (ярлычки) в нижней части окна "Лист 1" и т.д.

Каждый лист представляет собой таблицу. Таблица состоит из столбцов и строк. Столбцов в листе 256 (2 в 8 степени), а строк 65536 (2 в 16 степени). Количество ячеек предлагаю посчитать самим. Столбцы обозначаются буквами латинского алфавита (в обычном режиме) от "A" до "Z", затем идет "AA-AZ", "BA-BZ" и т.п. до "IV" (256). Строки обозначаются обычными арабскими числами.

На пересечении столбца и строки находится ячейка. Каждая ячейка имеет свой уникальный (в пределах данного листа) адрес, который составляется из буквы столбца (в обычном режиме) и номера строки (например "A1"). Вспомните морской бой, шахматы или как вы находите ваше место в кинотеатре. Адрес ячейки (ссылка на ячейку) используется в расчётах (и не только) для того, чтобы "достать" данные из этой ячейки, и использовать в формуле.

Только одна из ячеек листа в текущий момент времени является активной. Вокруг активной ячейки видна жирная чёрная рамка с квадратиком в нижнем правом углу (маркером автозаполнения). Даже если выделен диапазон ячеек, все равно одна из выделенных ячеек будет иметь белый цвет. Это не значит, что она не выделена, это значит, что в выделенном диапазоне она активная — именно в нее будет вводится набираемый с клавиатуры текст.

Операции над листами рабочей книги

С использованием левой кнопки мыши:

щелчок левой кнопкой по закладке (ярлычку) — переключение на соответствующее окно (либо использовать сочетание клавиш Ctrl + PageUp/Ctrl + PageDown);

перетаскивание левой кнопкой мыши — изменение порядка расположения листов;

Ctrl + перетаскивание левой кнопкой мыши — создание копии листа;

Ctrl + последовательные щелчки по ярлычкам — выделение нескольких листов;

Shift + щелчок на начальном и конечном ярлычке — выделение диапазона листов;

двойной щелчок по ярлычку — переименование листа. Нажимаем "Enter" для завершения.

По нажатию правой кнопки мыши над ярлычком открывается контекстное меню, в котором доступны следующие команды:

"Добавить...". Открывается окно, в котором выбираем, что добавить: новый лист, диаграмму, готовый шаблон и т.д.;

"Удалить". Надеюсь, без комментариев;

"Переименовать". Надеюсь, без комментариев;

"Переместить/Скопировать...". Интересная и полезная команда:

в выпадающем списке "в книгу:" выбираем, куда мы копируем лист: в один из открытых документов или в новую книгу;

В списке "перед листом:" будут отображены листы выбранной книги. Выбираем, переместить лист в конец или перед каким листом вставить;

Если не устанавливать галочку "Создавать копию", лист будет перемещен в выбранную книгу (т. е. в этой его не останется!), если установить — в выбранной книге будет создана копия этого листа.

"Выделить все листы". Надеюсь, без комментариев;

"Цвет ярлычка...". Тоже вроде без комментариев. Разве что хочу отметить, что активный "цветной" ярлычок отображается "некорректно" (IMHO);

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

10. MS-Excel. Фиксирование заголовков. Назначение, способ реализации

С уть: при пролистывании сильно вниз или сильно вправо мы все равно будем видеть заголовки столбцов и строк. Таким образом заполнять ячейки становится удобнее, не нужно каждый раз лазить в начало документа. Реализация: вкладка «Вид» - раздел «Окно» на панели инструментов – Закрепить области – выбираем нужное.

11. MS-Excel. Понятие циклической (рекурсивной) ссылки.

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

На вкладке Формулы в группе Зависимости формул щелкните стрелку на кнопке Проверка наличия ошибок, выберите пункт Циклические ссылки, а затем щелкните первую ячейку в подменю.

Совет :дважды щелкая стрелки слежения, можно переходить между ячейками в циклической ссылке.

Проверьте формулу в ячейке. Если формула верна, выберите в подменю Циклические ссылки следующую ячейку.

ПРИМЕЧАНИЕ. В строке состояния отображается слово «Цикл», за которым следует ссылка на одну из ячеек, содержащихся в циклической ссылке. Если слово «Цикл» отображается без ссылки, данная циклическая ссылка содержится не на текущем листе.

Продолжайте правку формул до тех пор, пока в строке состояния отображается слово «Цикл»

12. MS-Excel. Функция Lookup. Назначение, параметры. .

Функция ПРОСМОТР возвращает значение из строки, столбца или массива. Функция имеет две синтаксических формы: векторную и форму массива.

ПРЕДПОЛАГАЕМОЕ ДЕЙСТВИЕ СМ. РАЗДЕЛ ИСПОЛЬЗОВАНИЕ

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

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

ПРИМЕЧАНИЯ

Если требуется выполнить сложную проверку или проверку, превышающую предел вложенности функций, функцию ПРОСМОТР можно использовать вместо функции ЕСЛИ. См. примеры для формы массива.

Для правильной работы функции ПРОСМОТР просматриваемые данные должны быть отсортированы в возрастающем порядке. Если это невозможно, рекомендуется использовать функции ВПР, ГПР или ПОИСКПОЗ.

Форма вектора

Вектор — это диапазон, содержащий только одну строку или один столбец. Векторная форма функции ПРОСМОТР просматривает диапазон, в который входят значения только одной строки или одного столбца (так называемый вектор), в поисках определенного значения и возвращает значение из соответствующей позиции второго диапазона. Эта форма функции ПРОСМОТР применяется, когда требуется указать диапазон, содержащий значения для сопоставления. Другая форма функции ПРОСМОТР автоматически выполняет поиск в первом столбце или первой строке.

СИНТАКСИС

ПРОСМОТР(искомое_значение; просматриваемый_вектор; [вектор_результатов])

Функция ПРОСМОТР в векторной форме имеет аргументы, указанные ниже.

Искомое_значение. Обязательный аргумент. Значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.

Просматриваемый_вектор. Обязательный аргумент. Диапазон, состоящий из одной строки или одного столбца. Значения в аргументе просматриваемый_вектор могут быть текстом, числами или логическими значениями.

ВАЖНО. Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат. Текст в нижнем и верхнем регистрах считается эквивалентным.

Вектор_результатов. Необязательный аргумент. Диапазон, состоящий из одной строки или столбца. Вектор_результатов должен иметь тот же размер, что и просматриваемый_вектор.

ЗАМЕЧАНИЯ

Если функции ПРОСМОТР не удается найти искомое_значение, то в просматриваемом_векторе выбирается наибольшее значение, которое меньше искомого_значения или равно ему.

Если искомое_значение меньше, чем наименьшее значение в аргументе просматриваемый_вектор, функция ПРОСМОТР возвращает значение ошибки #Н/Д.

ПРИМЕР

В этом разделе справки используются данные внедренной книги. Измените данные на листе либо измените или создайте формулы, и приложение Excel Web App — интернет-версия Excel — мгновенно пересчитает результаты.

В этом примере функция ПРОСМОТР используется в векторной форме для поиска в диапазоне другого диапазона, состоящего только из одной строки или одного столбца.

Работа с этим примером для функции ПРОСМОТР во внедренной книге

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

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

В режиме полноразмерного просмотра в браузере нажмите кнопку Загрузка , чтобы открыть книгу в приложении Excel или сохранить ее на локальный компьютер. Если открыть книгу в приложении Excel для настольных компьютеров, это позволит в некоторых примерах работать с формулами массива, используя сочетание клавиш CTRL+SHIFT+ВВОД, которое не работает в браузере.

К НАЧАЛУ СТРАНИЦЫ

Форма массива

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

СОВЕТ. Как правило, вместо формы массива функции ПРОСМОТР лучше использовать функцию ГПР или ВПР. Эта форма функции ПРОСМОТР предназначена для обеспечения совместимости с другими редакторами электронных таблиц.

СИНТАКСИС

ПРОСМОТР(искомое_значение; массив)

Функция ПРОСМОТР в форме массива имеет аргументы, указанные ниже.

Искомое_значение. Обязательный аргумент. Значение, которое функция ПРОСМОТР ищет в массиве. Аргумент искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.

Если функции ПРОСМОТР не удается найти искомое_значение, то в массиве выбирается наибольшее значение, которое меньше искомого_значения или равно ему.

Если искомое_значение меньше, чем наименьшее значение в первой строке или первом столбце (в зависимости от размерности массива), то функция ПРОСМОТР возвращает значение ошибки #Н/Д.

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

Форма массива функции ПРОСМОТР очень похожа на функции ГПР и ВПР. Различие заключается в том, что функция ГПР ищет искомое_значение в первой строке, функция ВПР — в первом столбце, а функция ПРОСМОТР выполняет поиск в соответствии с размерностями массива.

Если ширина массива больше его высоты (т. е. есть массив имеет больше столбцов чем строк), то функция ПРОСМОТР ищет искомое_значение в первой строке.

Если высота массива больше его ширины (т. е. массив имеет больше строк, чем столбцов), то функция ПРОСМОТР выполняет поиск в первом столбце.

Используя функции ГПР и ВПР, можно указывать индекс по направлению вниз и вправо, а функция ПРОСМОТР всегда выбирает последнее значение в строке или столбце.

ВАЖНО. Значения в массиве должны быть расположены в порядке возрастания: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат. Текст в нижнем и верхнем регистрах считается эквивалентным.