Техника для бизнеса

28"2006

08"2006

24/2005

04"2005

21"2004

03"2004

ПОИСК

ИНФОРМАЦИЯ

О нас

Контакты

Где купить?

Реклама на сайте

Подписка на новости

Карта сайта





  главная    содержание    форум    архив  

Статья

НЕИЗВЕСТНЫЙ EXCEL

В журнале «Техника для бизнеса» №21’2004 мы начали разговор о средствах Microsoft Office XP, в частности, о базовых и дополнительных возможностях программы MS Word. В этом выпуске решено продолжить исследование пакетов Office и дать объективную оценку программе Microsoft Exсel версии XP.

Пакетом Exсel пользуются большинство офисных сотрудников, однако работают они с программой по минимуму, ограничиваясь, как правило, самыми простыми процедурами. В частности, многие думают, что поле Exсel — сплошная таблица (сетка с набором ячеек для числовых и символьных данных). Но такая трактовка некорректна. Дело в том, что здесь «таблица» — это не только средство систематизации данных, но еще и инструмент для их обработки. Мы подробно разберем форматы, в которых можно указывать информацию в ячейках, а также множество стандартных и дополнительных функций, с помощью которых изменяют или вычисляют объекты в ячейках.

 

Microsoft Exсel XP

Программный пакет для работы с таблицами и диаграммами

РАЗРАБОТЧИК: Microsoft.

 

ПАРАМЕТРЫ

ТРЕБОВАНИЯ К КОМПЬЮТЕРУ (мин.): соответствуют минимальным требованиям ОС.

СОВМЕСТИМЫЕ ОС: Windows 95, 98, Me, NT, 2000, XP.

ВРЕМЯ УСТАНОВКИ (среднее): 10 мин.

РУСИФИКАЦИЯ: есть.

ОКНО: стандартное Windows-окно.

ДОПОЛНИТЕЛЬНАЯ НАСТРОЙКА: возможна по усмотрению пользователя (в базовой инсталляции не требуется).

ОСОБЕННОСТИ: многофункциональная система работы с таблицами и диаграммами; возможность вставки изображений и других графических объектов; работа с текстом и др.

 

ТАБЛИЦЫ И ФУНКЦИИ

ФОРМАТЫ ДАННЫХ. Запустив первый раз Microsoft Exсel, пользователь получает доступ к возможностям, которые, как говорится, на поверхности: поскольку окно программы — огромное количество ячеек, он приступает к их заполнению различной информацией.

Каждый из типов данных (текст, числа, формулы и т.д.) программа отображает в различных форматах (всего их 12). Они определяются автоматически или устанавливаются вручную. Чтобы переобозначить содержимое поля, используют правую кнопку мышки и выбирают в появившемся меню строку «Формат ячеек». На экране появляется дополнительное окно с закладками, первая из которых и назначает форматирование.

1. Общий — используют для отображения произвольных значений.

2. Числовой — применяют для изменения формы представления чисел. Например, можно выбрать способ отображения отрицательных чисел (черный шрифт с минусом, красный шрифт и красный шрифт с минусом), назначить разделитель групп разрядов (с помощью галочки) или указать нужное количество выводимых знаков после запятой. В итоге, если забить символы в указанную ячейку и нажать клавишу «Enter», в ней отразится число с предписанным форматированием, например, красный текст с разделением.

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

4. Финансовый — предназначен для выравнивания денежных величин по разделителю целой и дробной части (например, число «1234,56» отобразится как «1 234,56»). Возможность обозначить валюту тоже присутствует. А вот выделить «минусовые» данные не получится. В Денежном и Финансовом форматах также назначают количество знаков после запятой, разделение групп разрядов здесь проходит автоматически.

5. Дата — служит для отображения различных дат. В окне настроек выбирают режим вывода (например, 14 марта 2001 г.). Затем заполняют ячейку строкой вида 14.03.2001 или 14.03.01. После нажатия клавиши «Enter» в ячейке появится выбранный в настройках вид даты. Если же в ячейку будет «забито» обычное число либо другой текст, строка вида «######» обозначит неверный тип полученных данных.

6. Время — отображает значение времени. По аналогии с форматом Дата выбирают режим вывода (например, 1:30:55 pm) и указывают в ячейке нужное время (12:34 или 12:34:00), в результате чего получается, скажем, 12:34:00 pm.

7. Процентный — введенное в ячейку число умножается на 100 и выводится со знаком процента. Если пользователю необходимо печатать числа без подобного умножения, в строке после набора цифр знак процента указывают вручную, например, 12% (если просто ввести «12», получим строку вида «1200,00%»). Число знаков после запятой задают в настройках форматирования.

8. Дробный — используется для записи дробных числовых значений. В окне настроек формата пользователь выбирает необходимый вид (простая дробь типа 1/2, дроби из двух цифр типа 21/34 и т.д.), после чего указывает в ячейке любое число с плавающей точкой, а программа переводит его в дробный вид и переписывает ячейку. Например, число «0,123456789» при выборе настройки «простыми дробями» на экране отобразится как «1/8».

9. Экспоненциальный — удобен для записи очень больших и очень маленьких чисел. Excel автоматически переводит их в степенной вид и отображает как «x,xxE+yy». Например, «1234567890» приобретет вид «1,23Е+0,9», а если в настройках указать не два, а три знака после запятой, то «1,234Е+0,9».

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

11. Дополнительный — содержит четыре дополнительных подформата:

• Почтовый индекс — только целые числа введенных данных.

• Почтовый индекс+4 — только целые числа введенных данных с дефисом перед последними четырьмя знаками. Например, при вводе числа «123456789» в ячейке будет указано «12345-6789».

• Телефонный — при наборе «1234567890» появится строка «(123) 456-7890».

• Табельный номер — в ячейке отображаются любые значения цифр и символов.

12. Все форматы — здесь можно создать собственные числовые форматы или изменить уже существующие по своему усмотрению.

 

ПОСТРОЕНИЕ И ФОРМАТИРОВАНИЕ ТАБЛИЦ

Теперь, зная, в каких форматах можно «забивать» данные в таблицы Excel, постараемся разобраться, что с ними можно делать дальше.

Пример 1. Построение простой таблицы.

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

Чтобы написать общее название таблицы, необходимо объединить четыре верхние ячейки, иначе название запишется только в одну из них, что повлияет на ширину всего столбца. Для этого выделяют первые четыре ячейки, удерживая левую клавишу мыши, на выделенное место «кликают» правой кнопкой и в появившемся меню выбирают пункт «Формат ячеек». Затем открывают закладку «Выравнивание» и ставят «галочку» напротив пункта «Объединение ячеек».

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

На последнем этапе заполняют таблицу данными и растягивают ее с помощью мыши до необходимого размера.

Пример 2. Автоматическое заполнение ячеек.

Теперь предположим, что требуется создать таблицу 2х102, где в одном столбце будут числа с 1 по 100, а во втором, скажем, с 3 по 300 с шагом 3. Конечно, заполнять можно и вручную, но на это уйдет уйма времени и сил, а с помощью программы ее «рисуют» за считаные секунды.

Построив соответствующее обрамление, объединив ячейки и назначив форматирование, заполняют первые три строки первого столбца. Затем выделяют этот столбец и наводят курсор мыши на правый нижний угол последней заполненной ячейки до появления крестика, после чего нажимают левую клавишу и, удерживая ее, тянут курсор вниз на 97 ячеек — в результате первый столбец автоматически заполнится нужными числами от 1 до 100. Второй столбец заполняют аналогично, набрав в первых трех ячейках цифры 3, 6 и 9.

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

Пример 3. Построение сводной таблицы на основе уже созданных.

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

Итак, сначала необходимо организовать третий столбец. Для автоматического заполнения следует в его первой ячейке написать выражение «=А1*B1» (это означает произведение чисел из ячеек с координатами А1 и В1) и растянуть его, как уже было описано выше, на весь столбец. На других функциях более подробно мы остановимся в следующем пункте.

Заполнив третий столбец, выделяют таблицу, не задействуя ее названия, выбирают в главном меню программы пункт «Данные», а затем «Сводная таблица». В результате на экране откроется подпрограмма помощи.

Первый шаг — предлагается выбрать источник для построения сводной таблицы (в списке или в базе данных Microsoft Exсel, во внешнем источнике данных, в нескольких диапазонах консолидации, в другой сводной таблице или сводной диаграмме) и тип построения (таблица или диаграмма). В нашем случае выбираем первый пункт и тип «Таблица».

Второй шаг — подпрограмма предлагает указать диапазон значений и сама отмечает нашу выделенную область. Когда этого не происходит, диапазон указывают вручную (например, если нужно задать его в области от А1 до C60, набирают строку «$А$1:$C$60»).

Третий шаг — устанавливают, куда именно поместить сводную таблицу: в новый или в текущий лист. Во втором случае указывают координаты для размещения. Затем нажимают кнопку «Макет...» и переносят мышкой в поле «Столбец» первый столбец исходной таблицы (обозначен цифрой 1), в поле «Строка» — второй столбец (обозначен цифрой 3), а в поле «Данные» — третий столбец (обозначен числом 32). На этом же шаге можно установить дополнительные настройки к будущей таблице: нажав кнопку «Параметры...», задают формат, источник данных, макет страницы (число полей в столбце) и т.д. Завершают построение кнопкой «Готово».

 

ФУНКЦИИ ДЛЯ РАБОТЫ С ЯЧЕЙКАМИ

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

Итак, запись функции начинается со знака «=». Это значит, что в указанной ячейке будет осуществлена операция, которую и указывают после знака равенства (в нашем случае — умножение).

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

1. Операция: 1+4, функция: =1+4, результат: 5. Если числа 1 и 4 указаны в ячейках A1 и A2, то функция примет вид: =A1+A2.

2. Операция: 2-4, функция: =2-4, результат: -2. Если числа 2 и 4 указаны в ячейках A1 и A2, то функция примет вид: =A1-A2.

3. Операция: 2*4, функция: =2*4, результат: 8. Если числа 2 и 4 указаны в ячейках A1 и A2, то функция примет вид: =A1*A2.

4. Операция: 4/2, функция: =4/2, результат: 2. Если числа 4 и 2 указаны в ячейках A1 и A2, то функция примет вид: =A1/A2.

Теперь разберем некоторые математические функции. Более подробно рассмотрим наиболее сложные, а все остальные вынесем в таблицу 1.

ПРОМЕЖУТОЧНЫЕ ИТОГИ (Номер функции; Ссылка 1; Ссылка 2;...) — выводит промежуточный итог в список или базу данных. Если такой список создан, его можно модифицировать, редактируя формулу с упомянутой функцией. (Номер функции — это число от 1 до 11, указывающее, какую функцию использовать при вычислении итогов внутри списка (1 — СРЗНАЧ;

2 — СЧЁТ; 3- СЧЁТЗ; 4 — МАКС; 5 — МИН; 6 — ПРОИЗВЕД; 7 — СТАНДОТКЛОН; 8 — СТАНДОТКЛОНП; 9 — СУММ; 10 — ДИСП; 11 — ДИСПР); Ссылка 1; Ссылка 2 — от 1 до 29 интервалов или ссылок, для которых подводятся итоги. Уже имеющимися формулами подведения итогов внутри аргументов Ссылка 1; Ссылка 2;... (вложенные итоги) пренебрегают, чтобы избежать двойного суммирования.

Функция «ПРОМЕЖУТОЧНЫЕ ИТОГИ» игнорирует все скрытые строки, которые получаются в результате фильтрации списка. Это важно в том случае, когда нужно подвести итоги только для наглядных данных.

МОБР (Массив) — возвращает обратную матрицу заданного массива, причем исходная матрица также хранится в массиве. Пример =МОБР(A1:B2), результат — массив.

Массив может быть задан как диапазон ячеек (скажем, A1:C3), как массив констант (например, {1;2;3: 4;5;6: 7;8;9}), как имя диапазона или массива. Если какая-либо из ячеек в массиве пуста или содержит текст, то функция МОБР возвращает значение ошибки #ЗНАЧ!. Она также сигнализирует об ошибке, если массив имеет неравное число строк и столбцов. Обратные матрицы, как и определители, обычно используют для решения систем уравнений с несколькими неизвестными. Произведение матрицы на ее обратную — это единичная матрица, то есть квадратный массив, где диагональные элементы равны 1, а все остальные — 0.

СУММЕСЛИ (Диапазон; Критерий; Диапазон суммирования) — суммирует ячейки, заданные критерием. Здесь «Диапазон» — диапазон вычисляемых ячеек; «Критерий» — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как >0, в этом случае суммироваться будут только положительные числа. «Диапазон суммирования» — фактические ячейки для суммирования.

ЧЕТН (Число) — возвращает число, округленное до ближайшего четного целого. Эта функция пригодится при обработке объектов, которые поступают парами. Например, упаковочный ящик позволяет упаковывать по два объекта в ряд. Ящик будет заполнен, если количество объектов, округленное до ближайшего четного числа, равняется вместимости ящика.

ЧИСЛКОМБ (Число; Число выбранных) — возвращает количество комбинаций для заданного числа объектов. Функция используется для определения числа всех возможных сочетаний объектов группы. «Число» — это количество элементов, «Число выбранных» — количество объектов в каждой комбинации.

Помимо математических функций, Microsoft Excel поддерживает еще и массу других, например, «Финансовые». Опять же мы подробно остановимся на двух самых сложных, а все остальные вынесем в таблицу.

ПС (Ставка; Кпер; Плт; Бс; Тип) — возвращает приведенную (к текущему моменту) стоимость инвестиции. Она представляет собой общую сумму, которая на настоящий момент равна ряду будущих выплат. Например, когда вы занимаете деньги, сумма займа является приведенной стоимостью для заимодавца. «Ставка» — проценты за период. Например, если получена ссуда под 10% годовых и делаются ежемесячные выплаты, то процентная ставка за месяц составит 10%/12, или 0,83%. В качестве значения аргумента нужно ввести в формулу 10%/12, или 0,83%, или 0,0083. «Кпер» — количество периодов платежей. Например, если получена ссуда на 4 года и делаются ежемесячные платежи, то ссуда имеет 4*12 (или 48) периодов. То есть в качестве Кпер в формулу вводят число 48. «Плт» — фиксированная выплата, производимая за каждый период, ее значение не меняется за все время действия ссуды. Обычно выплаты включают основные платежи и платежи по процентам, без других сборов или налогов. Например, ежемесячная выплата по четырехгодичному займу в 10000 руб. под 12% годовых составит 263,33 руб. В качестве значения аргумента Плт вводят в формулу число -263,33. «Бс» — исходная сумма плюс проценты. Если аргумент опущен, он полагается равным 0. Например, если предполагается накопить 50000 руб. для оплаты специального проекта в течение 18 лет, то 50000 руб. и есть будущая стоимость. Можно сделать предположение о сохранении заданной процентной ставки и определить, сколько нужно откладывать каждый месяц. Тип — число 0 или 1, обозначающее, когда должна производиться выплата.

БС (Ставка; Кпер; Плт; Пс; Тип) — возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки. «Пс» — это приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент Пс опущен, то он полагается равным 0. В этом случае обязательно должно быть указано значение аргумента Плт.

Для работы со временем и датами в программе предусмотрены специальные функции. Расскажем о двух самых сложных операциях из разделов «Дата» и «Время», а все остальные опять сведем в таблицу.

ДАТА (Год; Месяц; День) — возвращает целое число, представляющее определенную дату. Если до ввода этой функции форматом ячейки был Общий, результат отформатируется как дата.

ДНЕЙ360 (Начальная дата; Конечная дата; Метод) — возвращает количество дней между двумя датами на основе 360-дневного года (двенадцать 30-дневных месяцев). Функцию используют для расчета платежей, если бухгалтерия основана на двенадцати 30-дневных месяцах. «Начальная дата» и «Конечная дата» — это две даты, количество дней между которыми необходимо вычислить. Если первая окажется более поздней, чем вторая, функция возвращает отрицательное значение. Даты вводят с использованием функции ДАТА или как результат других формул и функций. Например, для 23 мая 2008 года можно использовать ДАТА(2008;5;23).

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

ГПР (Искомое значение; Таблица; Номер строки; Интервальный просмотр) — ищет значение в верхней строке таблицы или массива значений и возвращает его в том же столбце из заданной строки таблицы или массива. Функцию ГПР используют, когда сравниваемые значения расположены в верхней строке таблицы данных, а возвращаемые — на несколько строк ниже. Если сравниваемые значения находятся в столбце слева от искомых данных, то следует использовать функцию ВПР. Буква «Г» в ГПР означает «горизонтальный».

ВПР (Искомое значение; Таблица; Номер столбца; Интервальный просмотр) — ищет значение в крайнем левом столбце таблицы и возвращает его в той же строке из указанного столбца таблицы. Функцию ВПР используют вместо ГПР, когда сравниваемые значения расположены в столбце слева от искомых данных. Буква «В» в названии функции ВПР означает «вертикальный». «Искомое значение» — это значение, которое должно быть найдено в первом столбце массива, оно может быть числом, ссылкой или текстовой строкой. «Таблица» — таблица с информацией, в которой ищут данные. Можно использовать ссылку на интервал или имя интервала, например, База Данных или Список. Значения в первом столбце аргумента Таблица могут быть текстовыми строками, числами или логическими значениями (текстовые строки сравнивают без учета регистра букв). «Номер столбца» — это номер столбца в массиве Таблица, в котором должно быть найдено соответствующее значение. Если он равен 1, возвращается значение из первого столбца аргумента Таблица; если 2, то выводится значение из второго столбца упомянутого аргумента, и т.д. Если Номер столбца меньше 1, то функция ВПР возвращает значение ошибки «#ЗНАЧ!», если больше, чем количество столбцов, то выводится сообщение «#ССЫЛ!». «Интервальный просмотр» — логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение. Иными словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем Искомое значение. Если аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, возвращается значение ошибки «#Н/Д». Если интервальный просмотр имеет значение ИСТИНА, то в первом столбце должны быть расположены аргументы в возрастающем порядке: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА. В противном случае функция ВПР может выдать неправильный результат. Если же Интервальный просмотр имеет значение ЛОЖЬ, то Таблица не обязана быть отсортированной. Данные можно упорядочить следующим образом: в меню «Данные» выбирают команду «Сортировка» и устанавливают переключатель «По возрастанию».

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

Естественно, программа Microsoft Exсel может работать и с базами данных. Разберем соответствующие функции.

БДДИСП (База данных; Поле; Критерий) — оценивает дисперсию совокупности по выборке, используя числа в столбце списка или базы данных, которые удовлетворяют заданным условиям. «База данных» — интервал ячеек, формирующих список или БД (совокупность связанных данных, где строки являются записями, а столбцы — полями). Верхняя строка списка содержит названия всех столбцов. «Поле» — определяет столбец, используемый функцией. Аргумент Поле может быть задан как текст с названием столбца в двойных кавычках, например, «Возраст» или «Масса», или как число, задающее положение столбца в списке: 1 — для первого поля, 2 — для второго и т.д. «Критерий» — интервал ячеек, содержащий задаваемые условия. Любой интервал, имеющий по крайней мере одно название столбца и одну ячейку, может быть использован как аргумент Критерий.

ДСТАНДОТКЛП (База данных; Поле; Критерий) — вычисляет стандартное отклонение генеральной совокупности, используя числа в столбце списка или базы данных, удовлетворяющие заданным условиям.

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

КОДСИМВ (Текст) — возвращает числовой код первого знака в текстовой строке, который соответствует таблице знаков, используемой на данном компьютере.

ПОИСК (Искомый текст; Текст для поиска; Начальная позиция) — возвращает позицию первого вхождения знака или текстовой строки при поиске слева направо, начиная с последнего аргумента. Функция используется для поиска вхождения знака или строки текста в другую строку текста с тем, чтобы применить функции ПСТР или ЗАМЕНИТЬ для изменения текста.

НАЙТИ (Искомый текст; Просматриваемый текст; Начальная позиция) — находит вхождение одной текстовой строки («Искомый текст») в другую («Просматриваемый текст») и возвращает положение начала искомого текста относительно крайнего левого знака рассматриваемого текста. Для поиска вхождений одной строки в другую можно использовать также функцию ПОИСК, но в отличие от нее НАЙТИ учитывает регистр и не допускает использования подстановочных знаков.

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

РУБЛЬ (Число; Число знаков) — преобразует число в текстовый формат и добавляет к нему обозначение денежной единицы. Имя этой функции (и добавляемое обозначение) зависит от языковых параметров.

Т (Значение) — возвращает текст, ссылка на который задается аргументом. Если «Значение» является текстом или ссылается на текст, функция возвращает само это значение. Если ссылки на текст нет, возвращается пустой текст. В общем случае нет необходимости использовать Т в формулах, поскольку Microsoft Excel при необходимости автоматически преобразует значения. Эта функция предназначена для совместимости с другими системами электронных таблиц.

Есть в Microsoft Excel и связь с логическими переменными. Для этого предусмотрены такие базовые функции, как «И» или «ИЛИ». Разберем «логику» более подробно.

ЕСЛИ (Логическое выражение; Значение, если истина; Значение, если ложь) — возвращает одно значение, если заданное условие при вычислении выдает ИСТИНА, и другое — если ЛОЖЬ. Эту функцию используют при проверке условий для значений и формул. «Логическое выражение» — любое значение или выражение, принимающее значение ИСТИНА или ЛОЖЬ. Пример: «A10=100». Если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае — ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения. «Значение, если истина» — это значение, которое возвращается, если первый аргумент ИСТИНА. Например, если этот аргумент — строка «В пределах бюджета» и «Логическое выражение» истинно, то функция ЕСЛИ отобразит текст «В пределах бюджета». Если «Логическое выражение» истинно, а второй аргумент функции пустой, то возвращается значение 0. Чтобы отобразить слово ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента. «Значение, если истина» может быть формулой. «Значение, если ложь» — значение, которое возвращается, если первый аргумент ложный. Например, если этот аргумент — строка «Превышение бюджета» и «Логическое выражение» ложно, то функция ЕСЛИ отобразит текст «Превышение бюджета». Если «Логическое выражение» ложно, а «Значение, если ложь» опущено, возвращается ЛОЖЬ.

Чтобы проверить свойства и значение той или иной ячейки, можно задать базовые функции. Разберем их подробнее. В этом разделе описаны 9 функций, которые используют для проверки типа значения или ссылки. Каждая из них проверяет тип значения и возвращает значение ИСТИНА или ЛОЖЬ.

ИНФОРМ (Тип информации) — возвращает информацию о текущей операционной системе. «Вопросы» могут быть такими:

«версия» — версия Microsoft Excel в виде текстовой строки;

«версия ОС» — текущая версия операционной системы в виде текстовой строки;

«доступная память» — количество свободной памяти в байтах;

«используемая память» — количество памяти, используемой для данных;

«источник» — абсолютная ссылка в стиле A1 в виде текста и с префиксом $A, используемая для совместимости с пакетом Lotus 1-2-3 версий 3.x. Возвращается ссылка на самую левую верхнюю видимую в окне ячейку с учетом текущего состояния прокрутки;

«каталог» — путь текущего каталога;

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

«перевычислить» — текущий режим повторного вычисления; возвращается автоматически или вручную;

«система» — название операционной среды («mac» для Macintosh и «pcdos» для Windows);

«число файлов» — количество активных рабочих листов в открытых книгах.

Статистические функции программы Microsoft Exсel наиболее полномерны, ведь именно их используют в экономико-статистических расчетах, с их помощью анализируют и прогнозируют, рассчитывают вклады и т.д. Разберем более детально некоторые из них, а все остальные представим в таблице.

ГИПЕРГЕОМЕТ (Число успехов в выборке; Размер выборки; Число успехов в совокупности; Размер совокупности) — возвращает гипергеометрическое распределение. ГИПЕРГЕОМЕТ возвращает вероятность заданного количества успехов в выборке, если заданы размер выборки, количество успехов в генеральной совокупности и размер генеральной совокупности. Функция используется для задач с конечной генеральной совокупностью, где каждое наблюдение — это успех или неудача, а каждое подмножество заданного размера выбирается с равной вероятностью. «Число успехов в выборке» — количество успешных испытаний в выборке. «Число успехов в совокупности» — количество успешных испытаний в генеральной совокупности. «Размер совокупности» — размер генеральной совокупности. Все аргументы функции при обработке усекаются до целых. Если любой из них не является числом, то функция ГИПЕРГЕОМЕТ возвращает значение ошибки #ЗНАЧ!. Если число успехов в выборке меньше нуля или больше, чем наименьшее из размера выборки и чисел успехов в совокупности, то функция ГИПЕРГЕОМЕТ также возвращает значение ошибки #ЧИСЛО!.

ОТРБИНОМРАСП (Число неудач; Число успехов; Вероятность успеха) — возвращает отрицательное биномиальное распределение, т.е. вероятность того, что случится некоторое количество неудачных испытаний, прежде чем будет получено определенное число успехов (при том условии, что вероятность успешного испытания постоянна). Эта функция подобна биномиальному распределению за тем исключением, что количество успехов фиксировано, а количество испытаний переменно. Как и в случае биномиального распределения, испытания считаются независимыми. Числа неудач и успехов усекаются до целых. Если какой-либо из аргументов не является числом, функция возвращает значение ошибки #ЗНАЧ!. Если вероятность успеха меньше нуля или больше единицы, функция возвращает #ЧИСЛО!. Если (Число неудач + Число успехов — 1) меньше либо равно нулю, возвращается значение ошибки #ЧИСЛО!.

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

ОТРЕЗОК (Известные значения x; Известные значения y) — вычисляет точку пересечения линии с осью абсцисс, используя известные значения x и y. Точка находится на оптимальной линии регрессии, проведенной через известные значения x и y. Функция используется, когда нужно определить значение зависимой переменной при значении независимой переменной, равной нулю. Например, ОТРЕЗОК можно использовать, чтобы предсказать электрическое сопротивление металла при температуре 0 градусов Цельсия, если имеются данные измерений при комнатной температуре (или любой другой).

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

ЦЕНА (Москва): полный пакет Microsoft Office XP — от $300.

ТАБЛИЦА 1. МАТЕМАТИЧЕСКИЕ ФУНКЦИИ

ТАБЛИЦА 2. ФИНАНСОВЫЕ ФУНКЦИИ

ТАБЛИЦА 3. ФУНКЦИИ "ДАТА" И "ВРЕМЯ"

ТАБЛИЦА 4. ФУНКЦИИ "ССЫЛКИ" И "МАССИВЫ"

ТАБЛИЦА 5. ФУНКЦИИ ДЛЯ РАБОТЫ С БАЗАМИ ДАННЫХ

ТАБЛИЦА 6. ФУНКЦИИ ДЯЛ РАБОТЫ С ТЕКСТОВЫМИ ЗНАЧЕНИЯМИ

ТАБЛИЦА 7. ФУНКЦИИ ДЛЯ РАБОТЫ С ЛОГИЧЕСКИМИ ОПЕРАТОРАМИ

ТАБЛИЦА 8. ФУНКЦИИ ДЛЯ ПРОВЕРКИ СВОЙСТВ И ЗНАЧЕНИЙ ЯЧЕЕК

ТАБЛИЦА 9. СТАТИЧЕСКИЕ ФУНКЦИИ

 





Мнения пользователей

Ученик  (2013-05-29)
Большое спасибо, очень ценная информация, век живи-век учись!


LJUDMILLA  (2008-04-25)
Полезная информация



Оставить мнение

Имя

E-mail (не обязательно)

Мнение


Введите код

 
  все статьи    все тесты    экспертная оценка    школа потребителя  




Яндекс цитирования Яндекс.Метрика
Rambler's Top100
Система Orphus


Нашли ошибку на сайте? Выделите ее мышью и нажмите Ctrl+Enter.

Copyright © "Потребитель".
Использование материалов сервера в on-line изданиях разрешается при наличии гиппертекстовой ссылки на biz.potrebitel.ru.
Ссылка должна содержать слова: "Журнал ПОТРЕБИТЕЛЬ. Техника для бизнеса".
Использование материалов в off-line изданиях возможно лишь с письменного разрешения редакции.
По вопросам размещения рекламы, ошибкам на сайте, предложениям по работе сайта -


Место для рекламы: