 |
Проверка свойств и значений
|
|
|
|
Введение
Excel - пожалуй, самая популярная сегодня программа электронных таблиц. Ею пользуются деловые люди и ученые, бухгалтеры и журналисты. С ее помощью ведут разнообразные списки, каталоги и таблицы, составляют финансовые и статистические отчеты, обсчитывают данные каких-нибудь опросов и состояние торгового предприятия, обрабатывают результаты научного эксперимента, ведут учет, готовят презентационные материалы. Для ведения домашней бухгалтерии Excel тоже вполне подходит.
Основное отличие электронных таблиц от тех табличек, которые можно строить в Microsoft Word и других текстовых редакторах, состоит в том, что настоящие электронные таблицы оснащены возможностью производить вычисления. Ведь вордовская табличка - это просто способ расположения слов и чисел, вы не сможете попросить свой текстовый редактор, к примеру, посчитать сумму чисел по столбцу, а результат поместить в такую-то ячейку. То есть попросить-то сможете, а вот посчитать всего этого Word не сумеет. Зато Excel сумеет.
Excel может вычислять суммы по столбцам, строкам или любым иным группам ячеек, перемножать и делить, брать проценты; он сумеет посчитать среднее арифметическое, дисперсию или срок погашения кредита. В нем вообще можно использовать множество стандартных функций - финансовых, математических, логических, статистических.
Оформление таблиц может быть самым разнообразным (а это немаловажно, когда нужно распечатать прайс-лист, справку или презентационный материал), возможности форматирования данных - как в хорошем текст-процессоре: можно менять шрифты, начертания, выделять строки, столбцы или отдельные ячейки текста цветом, рамочками и линеечками, закрашивать области фоном или цветом, строить по табличным данным графики и диаграммы, вставлять в таблицу картинки и т.д. и т.п.
|
|
|
Программа Excel достаточно мощная, возможности ее, особенно в последних версиях, весьма обширны. Одних только математических, логических, бухгалтерских, статистических функций, которые Excel умеет выполнять над табличными данными, - более двухсот штук.
Функции Excel
Финансовые
В категории Финансовые множество специальных функций, вычисляющих проценты по вкладу или кредиту, амортизационные отчисления, норму прибыли и самые разнообразные обратные и родственные величины.
Например, функция ПЗ (в XP переименована в ПЛТ "плата") позволит рассчитать сумму, которую придется выплатить, взяв (или, наоборот, получить, предоставив кому-то) кредит. Причем в качестве аргументов используются Норма (норма прибыли или годовая процентная ставка), Кпер (количество периодов погашения), Выплата (фиксированные ежемесячные или ежегодные платежи - в счет погашения кредита плюс проценты (в XP это называется Пс "приведенная стоимость")) и Бс (будущая стоимость; равна нулю, если вы рассчитываете за этот период полностью отдать (получить) долг), Тип - равен 1, если выплаты происходят в начале периода, и 0, если в конце.
Обратными и родственными к ПЗ являются: функции КПЕР, позволяющая рассчитать время (количество периодов) погашения; функция БЗ (БС) - будущее значение (будущая стоимость) вклада или кредита; функция ПЛПРОЦ (ПРОЦПЛАТ) - платежи по процентам и т.д. и т.п.
Вводя процентную ставку по вкладу или кредиту, необходимо записать ее либо со знаком процента (25%), либо в виде сотых долей (0,25). Если же написать просто 25, то это будет понято как 2500%. Причем Excel, в отличие от нас, совершенно не волнует тот факт, что в условиях крепкого рубля и низкой инфляции таких ставок уже не бывает…
Дата и время
|
|
|
Большинство функций этой категории ведает преобразованиями даты и времени в различные форматы. Две специальные функции СЕГОДНЯ и ТДАТА вставляют в ячейку текущую дату (первая) и дату и время (вторая), обновляя их при каждом вызове файла или при внесении любых изменений в таблицу. Такую ячейку стоило бы иметь в бланках счетов, самых свежих прайс-листах, каких-нибудь типовых договорах: вы просто распечатываете файл, а текущая дата ставится сама.
Логические
В этой категории всего шесть команд, но о четырех из них стоит поговорить подробнее, поскольку они значительно расширяют наши возможности в применении всех остальных команд. Команда ЕСЛИ позволяет организовать разного рода разветвления. Формат ее:
=ЕСЛИ (логическое_условие; когда_неверно)
В качестве логического условия выступают равенства и неравенства с использованием знаков > (больше), < (меньше), = (равно), >=(больше или равно), <=(меньше или равно), <> (не равно). Пример: =ЕСЛИ (С1>D1*B5; ”УРА! ”; ”УВЫ…”) - если число в ячейке С1 больше, чем произведение D1 и В1, то в нашей ячейке будет радость, а если меньше - разочарование. В функцию ЕСЛИ может быть вложена другая функция ЕСЛИ, а в нее еще одна - "и так семь раз". Пример: =ЕСЛИ (С1>100; ”УРА! ”; ЕСЛИ(Е1=1; G1; G2)) - если ячейка С1 больше ста, то в нашей ячейке будет написано "Ура! ", а если меньше либо равна - то в нее скопируется содержимое ячеек G1(при Е1, равном 1) или G2 (при Е1, не равном 1)
Команда И позволяет создать несколько условий, которые можно использовать в команде ЕСЛИ. Все условия в команде И должны быть выполнены, только тогда функция принимает значение истина. Если хоть одно условие не выполнено, то значение ее ложь. Формат:
=И(логическое_условие_1; логическое_условие_2)
Всего логических условий может быть до 30 штук. Пример совместного использования функций ЕСЛИ и И:
ЕСЛИ(И(Е1>1; G2=”Ура! ”); ”Угадал”; ”Не угадал”) - если ячейка Е1 больше единицы, а в G2 находится слово "Ура! ", то в нашей ячейке окажется слово "Угадал" (истина), если же какое-то из логических условий не выполнено (ложь), получим "Не угадал".
Команда ИЛИ тоже позволяет задать несколько условий, но иным образом. Если хоть одно из них выполнено, то функция принимает значение истина. И только когда все заданные условия неверны, подучим ложь. Формат такой же, как у функции И, использовать ее совместно с ЕСЛИ можно точно так же.
|
|
|
Команда НЕ инвертирует, переворачивает полученное значение: была истина, станет ложь, и наоборот.
Пример: =ЕСЛИ(НЕ(С1>D1*B5); ”УРА! ”; ”УВЫ…”) - "УРА!" появляется, когда С1 не больше D1*B5.
Математические
Категория Математические (Мат. И тригонометрия) - одна из самых густонаселенных в Excel (60 команд в 5.0 и 50 в более поздних). Никаких особых хитростей в ней нет. Если SIN, значит, синус, если ASIN, значит, арксинус. Если LN - то натуральный логарифм, если LOG10 - то десятичный, а если LOG - то по произвольному основанию.
Функция ГРАДУСЫ преобразует радианную меру угла в градусную, функция РАДИАНЫ делает обратную операцию. Есть функция ПИ, которая позволит использовать в вычислениях эту математическую константу, а также СЛЧИС - генератор случайных чисел. Желающие могут поупражняться в переводе арабских цифр в римские (РИМСКОЕ). Функция ОСТАТ вычисляет остаток от деления, ЧАСТНОЕ - делит нацело. Excel также умеет выполнять некоторые операции с матрицами (МУМНОЖ - умножение матриц, МОБР - взятие обратной матрицы, МОПРЕД - вычисление определителя и т.д.). Что же касается суммирования, то кроме СУММ есть также СУММПРОИЗВ (сумма произведений), а также ряд "квакающих" сумм: СУММКВ (сумма квадратов), СУММРАЗНКВ (сумма разностей квадратов), СУМКВРАЗН (сумма квадратов разностей) и даже СУММСУММКВ (сумма сумм квадратов).
Ссылки и массивы
Эта категория в Excel 5 называется Просмотр и ссылки, в следующих версиях - Ссылки и массивы. В ней находятся функции, позволяющие обратиться к массиву данных (по колонке, строке, прямоугольному интервалу) и получить из него самую разнообразную информацию: номера столбцов и строк, в него входящих, их количество, содержимое нужного вам элемента массива; можно найти, в какой ячейке этого массива находится нужное число или текст и т.д.
Текстовые
В этой группе десятка два команд! С их помощью мы сможем посчитать количество символов в ячейке, включая пробелы (ДЛСТР), узнать код символа (КОД-СИМВ), узнать, какой символ стоит первым (ЛЕВСИМВ) и последним (ПРАВСИМВ) в строке текста, поместить в активную ячейку некоторое количество символов из другой ячейке (ПСТР), поместить в активную ячейку весь текст из другой ячейки заглавными (ПРОПИСН) или сточными буквами (СТРОЧН), проверить, совпадают ли две текстовые ячейки (СОВПАД), найти некоторый текст (ПОИСК, НАЙТИ) и заменить его другим (ЗАМЕНИТЬ).
|
|
|
Проверка свойств и значений
Эта категория в 5.0 называется Информационные, а в более поздних - Проверка свойств и значений. Здесь находятся команды, с помощью которых можно получить информацию о типе данных в ячейке (число там находится, текст или какая-то иная информация), о формате, о текущей операционной среде, о типе ошибки, возникшей в формуле, и проч.
Работа с базой данных
Функция для обсчета данных в базе были впервые выделены в отдельную категорию в Excel 97. Здесь можно найти команды статистического обсчета (БДДИСП - дисперсия по выборке из базы, БДДИСПП - дисперсия по генеральной совокупности, ДСТАНДОТКЛ - стандартное отклонение по выборке), операции со столбцами и строками базы, количество непустых (БСЧЕТА) или (БСЧЕТ) ячеек и т.д.
Электронные таблицы.
Строки каждой таблицы пронумерованы цифрами, а столбцы - буквами. Таким образом, у каждой ячейки на листе есть свой уникальный адрес (имя). Например, выделенная рамочкой ячейка в левом верхнем углу имеет адрес А1. если же мы хотим обратиться к этой ячейке из другого листа книги, то в адрес добавится название данного листа: Лист 1! А 1 (восклицательный знак служит разделителем). Более того, если мы захотим обратиться к ячейке из другого файла, в адрес добавится еще и его имя и путь к нему.
То, что ячейка А1 выделена рамочкой, означает, что она активна и можно немедленно что-нибудь в нее ввести с клавиатуры. В каждой ячейке таблицы может находиться число, текст либо формула. Это не все, что может находиться в ячейке, но самое существенное.
Простейшая формула - это, например, сумма. Объяснив программе, что именно надо суммировать, мы в заданной ячейке получаем результат. Конечно увидим мы в ней число, но в отличие от "просто числа" это будет вычисленным. И если измениться значение каких-либо ячеек, входящих в область суммирования, то автоматически поменяется и результат.
Поставим курсор на ячейку А1 и введем число, какое нам вздумается. Закончив, нажмем Enter (или стрелку вниз) и окажемся в ячейке А2. введя таким образом пяток чисел, в ячейке А6 напишем формулу для их суммирования: =А1+А2+А3+А4+А5 и нажмем на Enter (букву А надо вводить в английской раскладке клавиатуры). Excel посчитает сумму и запишет ее в ячейку А6. Суммирование, впрочем, лучше выполнять по-другому, с привлечением к общественно полезному труду стандартных функций Excel. Для этого выделим мышкой ячейки с А1 по А5 (это и будет область суммирования), а потом нажмем кнопку суммирования (сигму) на панели инструментов. В ячейке А6 тут же появится вычисленная сумма.
|
|
|
Стоит теперь перейти на любую из ячеек с А1 по А5 и ввести другое число, как сумма тут же изменится. Конечно, изменится она после того, как вы закончите набор и нажмете Enter, Tab, любую стрелку или ткнем мышкой в другую ячейку.
Точно так же формула отследит изменения, если мы решим очистить ячейку (клавиша Del), удалить ячейку иди группу ячеек (Правка > Удалить) или, наоборот, добавим новые строки (Вставка > Строки) и заполним пустые ячейки в них. Причем Excel не станет пугаться, если в какой-то ячейке вместо числа стоит текст - просто не обратит на это внимание. Такой подход можно только приветствовать, потому что он позволит нам в дальнейшем вставлять в таблицы любые текстовые комментарии и заголовки, не заботясь о том, чтобы исключить их из области действия формул.
Чтобы увидеть, как выглядит формула, созданная с помощью кнопки-сигмы, перейдем стрелками или встанем мышью на эту ячейку и поглядим на строку формул. Слева в отдельном окошечке (оно называется полем имени) написано: А6. Этот адрес (имя) ячейки, где мы находимся. А рядом стоит и сама формула: =СУММА(А1: А5). СУММ (в англоязычной версии SUM) - это операция суммирования, а в скобках написано, что суммируются ячейки в интервале от А1 до А5.
В Excel формулой считается только такое выражение, которое начинается со знака равенства.
Если поставить мышкой курсор в строку формул, можно будет что-то поправить в ней вручную. А если дважды щелкнуть по ячейке А6 или нажать F2, то править можно будет прямо в самой ячейке, а в строке формул наши действия будут продублированы. Excel XP, особенно услужливый, тут же покажет желтенькую справку-подсказку с описанием синтаксиса команды. Если я захочу, чтобы в область суммирования входили также данные из столбцов В и С. Тогда я могу написать в ячейке А6 =СУММА(А1: С5), задавая суммирование по прямоугольнику, верхняя левая вершина которого - в ячейке А1, а нижняя правая - в С5.
Если нужно считать сумму по всему столбцу (вы собираетесь впоследствии добавлять новые данные в таблицу, но не желаете каждый раз переписывать формулу), то в качестве аргумента можно задать, скажем, А: А. При этом произойдет сложение всех ячеек столбца и результат будет помещен в ячейку… В какую? Ведь если сумму записать в этот же столбец (например, в ячейку А6, как мы делали до сих пор), то она тоже попадает в область суммирования. Excel должен записать в А6 вычисленную сумму, но ему тут же придется добавить и это число к сумме, записав новый результат туда же, в А6. Вместо этого Excel сообщит, что не правы, скорее всего, вы. Невозможно, напишет, допустить циклические ссылки. Так что сумму по всему столбцу или по строке (в этом случае пишем так: = СУММ(1:1)) надо помещать в другом столбце или в другой строке.
При выполнении итерационных вычислений, начиная с Excel 97, циклические ссылки допустимы. Но это достаточно сложные материи.
Если нам нужно просуммировать только столбцы А и С, а В пропустить. Тогда мы пишем = СУММ(А1: А5; С1: С5). Двоеточие задает интервал, точка с запятой - перечисление.
На самом деле все описанные выше операции можно проделать и мышкой. Например, встать в ячейку D6, где будет находиться сумма, нажать на сигму - в ячейке сразу возникнет незаполненная формула = СУММ(), а потом мышкой выделить либо прямоугольный блок А1: А5 (сумма по трем столбцам), либо сперва ячейки с А1 по А5, а затем, держа нажатой клавишу Ctrl, - с С1 по С5 (сумма по двум столбцам вразбивку). Можно даже разом выделить весь столбец - щелкнув мышкой по его имени, например по букве А вверху столбца. Строка выделяется аналогично - щелчком по ее номеру слева.
По мере выделения в скобках будут появляться имена ячеек. Чтобы подтвердить окончание ввода, жмем Enter.
Иногда возникает необходимость в формуле использовать данные из другой таблицы. Так, для того чтобы прибавить к нашей сумме ячейку А1, находящуюся на четвертом листе, пишем: = СУММ(А1: А5) +Лист4! А1.
Как только мы стали редактировать формулу, в строке формул появились новые кнопки. Смысл их таков. Щелчок мышью по галочке подтверждает внесенные нами изменения, по крестику - отменяет.
При редактировании формул у нас появляется новое окно-список Функции. Одним щелчком мыши можно взять оттуда любую из десяти функций, которыми пользовались в последнее время.
Когда мы выделяем мышкой ячейки, расположенные не в столбик, а в строку, сумма появится не внизу, а справа от выделенной группы ячеек. Формула будет выглядеть примерно так: =СУММ(А1: D1). Если мы выделим мышкой прямоугольную область, например первые пять ячеек столбцов А, В и С, и нажмем на сигму. Это позволит вычислить сумму по каждому из столбцов и напишет в свободные ячейки внизу.
Если же нам хочется подобным образом посчитать сумму по каждой строке этого прямоугольника, то, выделяя блок А1: С5, надо захватить и пустой столбик D(А1: D5). Тогда при нажатии кнопки суммирования в столбце D окажутся суммы, вычисленные по строкам. Если выделить, кроме блока с данными, также пустой столбец справа и пустую строку снизу и нажать на сигму, то Excel напишет нам суммы и по строкам, и по столбцам.
В XP кнопка сигма превратилась в меню, в котором имеется список нескольких самых часто используемых команд. Кроме обычного суммирования тут есть вычисление среднего арифметического по строке или столбцу (Среднее), подсчет количества чисел - пустые ячейки, тест и формулы команда не учитывает (Число), а также вычисление максимума и минимума.
Мастер диаграмм
Проще всего диаграмма строится, если заранее выделить необходимый фрагмент таблицы, причем хорошо бы, чтобы левый столбец содержал названия строк, а первая строка - названия столбцов. Тогда мастер сразу включит их в диаграмму, расположив по осям или вставив в легенду. Нажав кнопку мастер диаграмм, мы вызываем мастера создания диаграмм. Мастер делает четыре шага. Сначала нам предлагается выбрать тип диаграммы. Тут и круговая, и кольцевая, и гистограмма, и линейчатая и т.д. и т.п. А на странице Нестандартные есть еще "деревянная", "парящие бруски"…
На втором шаге надо выбрать диапазон данных. Для удобства проведения этой операции можно нажать на кнопку справа от строки Диапазон, и окно свернется в строчку. Необходимо выделить мышью нужные строки и столбцы своей таблицы, а потом нажать ту же кнопку еще раз. Но если все было выделено заранее, второй шаг нам не нужен: сразу жмем кнопку Далее >. На этом шаге можно еще задать, какие данные будут отложены по оси Х, а какие по оси Y (переключатель Ряды в: сроках или столбцах). Третий шаг. Водим заголовок диаграммы, название осей, ставим или убираем осевые линии, легенду и прочие детали оформления.
И наконец, задаем, куда диаграмму следует поместить - на отдельный лист или на один из имеющихся. Жмем кнопку Готово и получаем то, что заказали.
Готовую диаграмму, если она таки совсем оправдала наши ожидания, можно еще подредактировать. Щелкаем по любому элементу диаграммы, и он выделяется (отмечается черными квадратиками), щелкаем по пустому месту внутри диаграммы, и она выделится вся. Теперь можно выделенный элемент или всю диаграмму скопировать или удалить (Del), передвинуть мышью в нужное место, уменьшить или растянуть (за черные квадратики).
Однократный щелчок по уже выделенным заголовкам (шапке, подписям) позволит поменять в них текст.
Двойной щелчок на выделенном фрагменте позволит его переоформить (в Excel 5.0 надо вначале выделить всю диаграмму, а потом дважды щелкать). Если щелкаем по шапке, попадаем в диалоговое окно Форматирование названия диаграммы, если по оси - в Форматирование оси. Можно дважды щелкнуть по любой из линий (столбиков, кривых, секторов или что мы там выбрали) и поменять какие-то параметры отдельно по каждой группе (например, поменять цвет). Если щелкать в стороне от кривых или столбиков, то попадем в Форматирование области диаграммы или Форматирование области построения. Даже параметры линий сетки можно отредактировать, если дважды щелкнуть по одной из них. Щелкая по этим же элементам правой кнопкой мыши, мы будем получать контекстное меню - для каждого элемента свое.
Диаграмма динамически связана с исходными данными. Если мы что-то в таблице поменяли, то изменится и диаграмма. А если что-то испортили (возникла ошибка), то и диаграмма испортится, и в ней появятся сообщения об ошибках.
Список используемой литературы
1. Александр Левин: Excel - это очень просто!
|
|
|
