Главная » 2012 » Ноябрь » 7 » Подробное руководство по созданию формул в Excel 2002
17:15
Подробное руководство по созданию формул в Excel 2002
Большинство изданий, посвященных Excel, представляют собой универсальные руководства по программному продукту, содержащие информацию об использовании всех его средств и инструментов. Наряду с этим вы вряд ли найдете книгу, посвященную одному из основополагающих компонентов Excel, имеющему исключительно важное значение для любого пользователя, а именно - формулам. Ведь именно формулы делают электронную таблицу действительно электронной. Следовательно, чем больше вы знаете о формулах, тем лучше будут ваши таблицы. Большая часть материала, представленного в этой книге, посвящена именно тем вопросам, которые пользователи задают в ходе решения поставленной задачи. Вы также найдете ответы на те вопросы, которые не приходят в голову даже в конце обучения Эта книга не предназначена для начинающих пользователей Excel. В ней рассматривается достаточно сложный материал, который утомителен для начинающих пользователей, но чрезвычайно порадует тех, кому часто и долго приходится управлять электронными таблицами.
Название: Подробное руководство по созданию формул в Excel 2002 Автор: Уокенбах Джон Издательство: Вильямc Год: 2002 Страниц: 624 Формат: PDF Размер: 34,7 МБ ISBN: 5-8459-0314-9 Качество: Отличное
Содержание:
Введение ЧАСТЬ I. ОСНОВНЫЕ СВЕДЕНИЯ Глава 1. Коротко об Excel Краткая история Excel Сначала была VisiCalc Потом появился Lotus Программные продукты компании Microsoft Версии Excel Концепция объектной модели Работа с книгами Таблицы Листы диаграмм Листы макросов XLM Листы диалоговых окон Пользовательский интерфейс Excel Меню Контекстные меню Смарт-тэги Диалоговые окна Панели инструментов Перетаскивание данных Комбинации клавиш Настройка отображения дополнительных элементов интерфейса Ввод данных в ячейки Выделение ячеек и других объектов Форматирование ячеек Числовое форматирование Стилевое форматирование Формулы и функции электронных таблиц Объекты графического слоя Фигуры Диаграммы Ссылки на графические объекты Карты Элементы управления диалоговыми окнами Диаграммы Настройка Excel в соответствии с требованиями пользователя Макросы Панели инструментов Надстройки Средства анализа Доступ к базам данных Структура Управление сценариями Надстройка "Пакет анализа" Сводные таблицы Средства проверки данных Надстройка "Поиск решения" Уровни защиты Защита формулы от перезаписи Защита структуры рабочей книги Резюме Глава 2. Основные сведения о формулах Ввод и редактирование формул Элементы формул Ввод формул Вставка имен Пробел и разрыв строки Предельный размер формул Примеры формул Редактирование формул Применение операторов в формулах Операторы ссылки Примеры формул с операторами Приоритет операторов Вложенные скобки Вычисление формул Ссылки на ячейки и диапазоны ячеек Создание абсолютной ссылки Создание ссылок на ячейки других рабочих листов или рабочих книг Создание точной копии формулы Преобразование формул в значения Скрытие формул Ошибки в формулах Работа с циклическими ссылками Функция подбора параметра Пример подбора параметров Еще немного о подборе параметра Резюме Глава 3. Работаем с именами Что такое Имя? Способы присвоения имен ячейкам и диапазонам Присвоение имен в окне Присвоение имени Присвоение имени с помощью поля Имя Автоматическое присвоение имени Имя столбца или строки Имена, созданные программой Excel Присваивание имен в нескольких листах Область действия имен Создание имен уровня рабочего листа Комбинирование имен уровня рабочего листа с именами уровня рабочей книги Ссылка на имя из другой рабочей книги Работа с именами диапазонов и ячеек Создание списка имен Использование имен в формулах Использование операторов пересечения Использование операторов диапазона с именами Ссылка на отдельную ячейку именованного диапазона Применение имен в уже существующих формулах Автоматическое применение имен при создании формул Отмена имени Удаление имен Удаление именованных ячеек и диапазонов Переназначение имени Изменение имени Просмотр именованных диапазонов Использование имен в диаграммах Поддержка имен ячеек и диапазонов Вставка строки или столбца Удаление строки или столбца Вырезание и вставка именованного диапазона Проблемы при работе с именами Проблемы, возникающие при копировании рабочих листов Проблемы, возникающие при удалении рабочих листов Ключ к пониманию термина "имя" Присвоение имени константе Присвоение имени текстовым константам Использование функций рабочего листа в именованных формулах Использование ссылок на ячейки и диапазоны в именованных формулах Использование именованных формул с относительными ссылками Решение сложных задач с помощью формул Функция ДВССЫЛ и именованный диапазон Использование функции ДВССЫЛ для создания именованного диапазона с фиксированным адресом Использование массивов в именованных формулах Создание формулы с динамическим именем Резюме ЧАСТЬ II. ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ В ФОРМУЛАХ Глава 4, Введение в функции Excel Что такое функция? Упрощение формулы Вычисления, невозможные без функций Ускорение выполнения задач редактирования Способность к принятию решений Еще немного о функциях Типы аргументов функций Имена в качестве аргументов Задание строки или Столбца в качестве аргумента Точные значения в качестве аргументов Математические выражения в качестве аргументов Функции в качестве аргументов Массивы в качестве аргументов Способы введения функции в формулу Ручной ввод функций Вставка функции с помощью диалогового окна Мастер функций Несколько полезных советов по вводу функций Категории функций Финансовые функции Функции даты и времени Математические функции Статистические функции Функции ссылок и массивов Функции работы с базами данных Текстовые функции Логические функции Информационные функции Инженерные функции Функции, созданные пользователем Другие категории функций Функции настройки "Пакет анализа" Резюме Глава 5. Работа с текстом Несколько слов о самом тексте Количество символов в ячейке Числа как текст Текстовые функции Определение наличия в ячейке текста Работа с кодами символов Равенство строк? Объединение двух и более ячеек Отображение значений, отформатированных в текстовом формате Отображение денежных значений, отформатированных как текст Повторяющиеся символы или строки Создание текстовой гистограммы Добавление к числу определенных символов Удаление лишних пробелов и непечатаемых символов Подсчет символов в строке Изменение регистра текста Извлечение из строки определенных символов Замена одного текста другим Поиск и определение расположения символа в строке Поиск и замена символа в пределах строки Дополнительные текстовые формулы Подсчет символов в ячейке Подсчет строк, содержащихся в ячейке Определение заданного числа как порядкового номера Определение литеры столбца по заданному номеру Извлечение имени файла из заданного пути Извлечение первого слова строки Извлечение последнего слова строки Извлечение всех слов строки кроме первого Извлечение имени, отчества и фамилии Удаление из имен титулов и званий Подсчет количества слов в ячейке Создание собственных текстовых функций с помощью языка VBA Резюме Глава 6. Работа с данными даты и времени Принципы управления данными даты и времени в Excel Понятие даты как порядкового номера Введение даты Понятие времени как порядкового номера Введение времени Форматирование даты и времени Трудности, возникающие при использовании даты Функции управления датами Отображение текущей даты Отображение различных дат Создание ряда последовательных дат Преобразование в дату строки, не означающей дату Вычисление количества дней между двумя датами Вычисление количества рабочих дней между двумя датами Вычисление даты по графику рабочих дней Вычисление количества лет между двумя датами Определение возраста человека Вычисление дня года Определение дня недели Определение даты самого ближайшего воскресенья Вычисление дня недели, следующего за определенной датой Определение даты дня недели в заданном месяце Определение числа заданных дней недели в месяце Вычисление праздничных дат Определение даты последнего дня месяца Определение високосного года? Вычисление квартального периода Отображение года римскими цифрами Создание календаря в отдельном диапазоне ячеек Функции управления временем Отображение текущего времени Отображение времени Суммирование времени, превышающего 24 часа Определение разницы между двумя временными значениями Преобразование "военного времени" Преобразование часов, минут, секунд в десятичном исчислении Добавление часов, минут или секунд к заданному времени Работа с временными поясами Округление времени Работа со значениями, не имеющими отношения ко времени суток Резюме Глава 7. Способы суммирования и подсчета данных Подсчет и суммирование ячеек электронной таблицы Подсчет или суммирование записей в базах данных и сводных таблицах Основные формулы подсчета Подсчет общего количества ячеек Подсчет пустых ячеек Подсчет непустых ячеек Подсчет ячеек, содержащих числовые значения Подсчет ячеек, содержащих нетекстовые значения Подсчет ячеек, содержащих текстовое значение Подсчет логических значений Значения ошибки диапазона данных Дополнительные формулы подсчета ячеек Подсчет ячеек с использованием функции СЧЕТЕСЛИ Подсчет ячеек с использованием нескольких условий Подсчет часто встречающихся записей Подсчет количества экземпляров определенного текста Подсчет количества одинаковых значений Распределение частот Формулы суммирования Суммирование всех ячеек диапазона Общая сумма Суммирование n-го количества наибольших или наименьших значений Условное суммирование по одному критерию Суммирование только отрицательных значений Суммирование значений на основе диапазона Difference Суммирование значений на основе сравнения текста Суммирование значений на основе сравнения даты Условные суммы на основе множества критериев Использование оператора И Использование оператора ИЛИ Использование условий И и ИЛИ Использование функций VB А для подсчета и суммирования данных Резюме Глава 8. Поиск данных Знакомство с формулой поиска данных Функции, используемые для поиска данных Основные формулы поиска данных Функция ВПР Функция ГПР Функция ПРОСМОТР Комбинирование функций ПОИСКПОЗ и ИНДЕКС Специальные формулы поиска данных Поиск точного значения Поиск значения слева Поиск с учетом регистра Поиск значения в множестве таблиц Определение оценки по количеству набранных баллов Вычисление среднего уровня оценок Поиск на пересечении строк и столбцов Поиск информации в двух столбцах Определение расположения значения в диапазоне Поиск значения, максимально приближенного к заданному Поиск значения с использованием линейной интерполяции Резюме Глава 9. Базы данных и списки Знакомство со списками и базами данных электронной таблицы Использование средства Автофильтр Принципы работы средства Автофильтр Подсчет и суммирование отфильтрованных данных Копирование и удаление отфильтрованных данных Использование расширенного фильтра Создание диапазона условий Фильтрация списка Определение условий расширенного фильтра Определение одного условия Определение множества условий Определение вычисляемых условий Использование функций базы данных Суммирование списка с помощью таблицы подстановки Создание промежуточных итогов Резюме Глава 10. Дополнительные вычисления Преобразование единиц измерений Таблицы преобразования единиц измерения Использование таблиц преобразования единиц измерения Преобразования единиц измерения длины Преобразования единиц измерения веса Преобразования единиц измерения жидкости Преобразования единиц измерения площади Преобразования единиц измерения объема Преобразования единиц измерения силы Преобразования единиц измерения энергии Преобразования единиц измерения времени Преобразование единиц измерения температуры Решение задач для прямоугольных треугольников Вычисление периметра, длины окружности, площади плоских фигур, поверхности и объема тел Вычисление площади и периметра квадрата Вычисление площади и периметра прямоугольника Вычисление площади и периметра окружности Вычисление площади трапеции Вычисление площади треугольника Вычисление площади поверхности и объема шара Вычисление площади поверхности и объема куба Вычисление площади поверхности и объема конуса Вычисление объема цилиндра Вычисление объема пирамиды Решение систем линейных уравнений Округления Основные формулы округления Округление значений денежных величин Работа с дробными значениями денежных единиц Использование функций ЦЕЛОЕ и ОТБР Округление до четного и нечетного целого Округление значения до n-го разряда значащих цифр Резюме ЧАСТЬ III. ФИНАНСОВЫЕ ФОРМУЛЫ Глава 11. Знакомство с финансовыми формулами Основные финансовые функции Excel Анализ денежных потоков Функции накопления, дисконтирования и амортизации Простые задачи накопления Сложные задачи накопления Простые задачи дисконтирования Сложные задачи дисконтирования Задачи амортизации Преобразование процентных ставок Методы указания процентных ставок Преобразование процентных ставок с помощью встроенных финансовых функций Эффективная стоимость займов Воздействие платежей на эффективную ставку Займы с единообразной ставкой Беспроцентные займы Заем с "ежегодными" выплатами Вычисление процента и основных платежей Использование функций ОСПЛТ и ПРПЛТ Использование функций ОБЩДОХОД и ОБЩПЛАТ Соответствие процентов и частоты выплаты Ограничения финансовых функций Excel Отсроченное начало ряда регулярных выплат Оценка серии регулярных платежей Резюме Глава 12. Финансовые функции дисконтирования и обесценивания Использование функции ЧПС Определение ЧПС Примеры функции ЧПС Использование функции ЧПС для вычисления накопленных сумм Использование функции ВСД Несколько ставок в функциях ВСД и МВСД Использование функции БЗРАСПИС Вычисления обесценивания Резюме Глава 13. Дополнительное использование финансовых функций и формул Создание динамических финансовых планов Создание планов амортизации Простой план амортизации Детальный план амортизации План амортизации займа с переменной ставкой Задание параметров займа с помощью таблицы данных Создание односторонней таблицы данных Создание двухсторонней таблицы подстановки План накопления Планы будущего поступления Функции ЧИСТВНДОХ и ЧИСТНЗ Резюме ЧАСТЬ IV. ФОРМУЛЫ МАССИВА Глава 14. Введение в массивы Введение в формулы массива Формула массива, возвращающая несколько значений Формула массива, возвращающая одно значение Создание массива констант Элементы массива констант Понятие размерности массива Одномерные горизонтальные массивы Одномерные вертикальные массивы Двумерные массивы Присвоение имени массивам констант Работа с формулами массива Ввод формулы массива Выделение диапазона формулы массива Редактирование формулы массива Расширение или сокращение формулы массива Использование формул массива, возвращающих несколько значений Создание массива из значений в диапазоне Создание массива констант из значений диапазона Выполнение действий в массиве Использование функций в операциях с массивами Транспонирование массива Генерирование массива последовательных целых чисел Использование формул массива, возвращающих одно значение Подсчет количества символов в диапазоне Сложение трех наименьших элементов диапазона Подсчет количества текстовых ячеек в диапазоне Устранение промежуточных формул Использование массива вместо диапазона ссылок Резюме Глава 15. Магия формул массива Работа с формулами массива, возвращающими одно значение Суммирование в диапазоне, который содержит ошибки Подсчет количества ошибок в диапазоне Суммирование при выполнении условия Суммирование n-го числа наибольших значений в диапазоне Вычисление среднего без учета нулевых значений Поиск значения в диапазоне Подсчет отличающихся значений в двух диапазонах Возвращение расположения максимального значения диапазона Поиск номера строки, в которой находится n-го значение, совпадающее с заданным Возвращение самого длинного текста в диапазоне Определение допустимых значений диапазона Определение суммы цифр числа Суммирование округленных значений Суммирование каждого n-го значения в массиве Исключение нечисловых символов из текстовой строки Определение ближайшего значения в диапазоне Возвращение последнего значения в столбце Возвращение последнего значения в строке Упорядочение данных с помощью формулы массива Создание динамических перекрестных таблиц Работа с формулами массива, возвращающими несколько значений Извлечение положительных значений из диапазона Извлечение непустых ячеек из диапазона Изменение на противоположный порядка следования элементов в диапазоне Динамическая сортировка величин в диапазоне Возвращение списка уникальных значений диапазона Отображение календаря в диапазоне Возвращение массива с помощью VBA-функций Резюме ЧАСТЬ V. СОВЕРШЕННОЕ ВЛАДЕНИЕ ФОРМУЛАМИ Глава 16. Умышленные циклические ссылки Что такое циклические ссылки? Исправление случайных циклических ссылок Косвенные циклические ссылки Умышленные циклические ссылки Определение параметров вычислений и итераций Примеры формул, использующих циклические ссылки Создание временного снимка ячейки Вычисление всегда-самого-большого значения Генерирование уникальных случайных чисел. Решение рекурсивного уравнения Решение системы уравнений с помощью циклических ссылок Возможные проблемы, связанные с использованием циклических ссылок Резюме Глава 17. Методы построения диаграмм Представление данных на диаграммах Знакомство с формулой РЯД Создание ссылок на ячейки График выполнения работ Создание диаграммы Гантта Создание гистограммы сравнения Создание диаграммы разброса данных Построение каждой n-ой точки данных Автоматическое обновление рядов данных Отображение п последних точек данных Интерактивное отображение данных Построение диаграммы на основе выбора строки Выбор данных из списка Построение графиков функций с одной переменной Построение графиков функций с двумя переменными Создание экстравагантных проектов Диаграммы тенденций Линейные тенденции Нелинейные тенденции Полезные методы построения диаграмм Сохранение нескольких диаграмм на одном листе Просмотр внедренной диаграммы в окне Изменение значений рабочего листа путем перетаскивания точек данных Использование анимированных диаграмм Создание "измерительной" диаграммы Создание диаграммы часов Рисование с помощью графиков Резюме Глава 18. Сводные таблицы О сводных таблицах. Пример сводной таблицы Данные, используемые в сводной таблице Создание сводной таблицы Шаг 1. Указание расположения данных Шаг 2. Определение данных Шаг 3. Завершение создания сводной таблицы Группирование элементов сводной таблицы Создание вычисляемых полей или вычисляемых элементов Создание в сводной таблице вычисляемого поля Вставка в сводную таблицу вычисляемого элемента Резюме Глава 19. Условное форматирование и проверка вводимых значений Условное форматирование Настройка условного форматирования Возможные типы форматирования Определение условий Работа с условными форматами Формулы условного форматирования Использование пользовательских функций в формулах условного форматирования Проверка вводимых значений Определение критерия достоверности данных Типы возможных условий проверки вводимых значений Использование формул в условиях проверки вводимых значений Использование формул проверки вводимых значений для ввода только определенных значений Резюме Глава 20. Создание мегаформул Что такое мегаформула? Создание мегаформулы: простой пример Примеры мегаформул Использованию мегаформул для удаления вторых имен и инициалов Использование мегафомулы для нахождения положения последнего, пробела в строке Использование мегаформулы для определения действительности номера кредитной карточки Преимущества и недостатки использования мегаформул Резюме Глава 21. Инструменты и методы отладки формул Отладка формул? Проблемы формул и их решение Недостающие скобки Ячейки заполнены знаками ########## Пустые ячейки не пустые Формула возвращает ошибку Абсолютные и относительные ссылки Приоритет операторов Формулы не вычислены Реальные и отображаемые значения Ошибки плавающей запятой Ошибка "фантомных ссылок" Ошибка циклических ссылок Средства проверки данных Excel Нахождение ячейки определенного типа Просмотр формул Отслеживание связей ячейки Отслеживание ошибочных значений Исправление ошибок циклической ссылки Использование средства фоновой проверки ошибок Excel 2002 Проверка формул в Excel 2002 Средства проверки данных сторонних разработчиков Power Utility Рак Spreadsheet Detective Excel Auditor Резюме ЧАСТЬ VI. РАЗРАБОТКА ПОЛЬЗОВАТЕЛЬСКИХ ФУНКЦИЙ Глава 22. Введение в VBA Несколько слов о VBA Введение в редактор Visual Basic Запуск редактора Visual Basic Компоненты редактора Visual Basic Использование окна проекта Использование окна кода Введение кода Сохранение проекта Резюме Глава 23. Основы процедур функций Для чего нужны пользовательские функции? Простой пример VBA-функции Работа процедуры функции Объявления функции Выбор имени функции Использование функций в формулах Аргументы функций Использование диалогового окна вставки функции Добавление описания функции Определение категории функции Тестирование и отладка функций Использование окна сообщений VBA Использование в коде операторов Debug. Print Вызов функции из процедуры Установка точек остановки в функции Создание надстроек Резюме Глава 24. Принципы программирования на VBA Пример процедуры функции. Использование комментариев в коде Использование переменных, типов данных и констант Определение типов данных Объявление переменных Использование констант Использование строк Работа с датами Использование выражений присвоения Использование массивов Объявление массива Описание многомерных массивов Использование встроенных функций VBА Управление выполнением кода Конструкция If-Then Конструкция Select Case Создание цикла операторов Оператор On Error Работа с диапазонами Конструкция For Each-Next Ссылка на диапазон Часто используемые свойства диапазона Ключевое слово Set Функция Intersect Функция Union Свойство UsedRange Резюме Глава 25. Примеры пользовательских функций VBA Простые функции Есть ли в ячейке формула? Возвращение формулы ячейки Скрыта ли ячейка? Возвращение имени рабочего листа Возвращение имени рабочей книги Возвращение имени приложения Возвращение версии Excel Возвращение информации о форматировании ячейки Определение типа данных в ячейке Многофункциональная функция Генерирование случайных чисел Генерирование неизменяемых случайных чисел Выбор случайной ячейки Вычисление комиссионных от продаж Функция простой структуры начисления комиссионных Функция сложной структуры начисления комиссионных Функции управления текстом Реверсирование строки Перетасовка текста Возвращение аббревиатуры Соответствие текста шаблону? Содержится ли в ячейке текст? Извлечение n-ого элемента строки Написание цифры прописью Функции подсчета и суммирования Подсчет ячеек с определенными значениями Подсчет видимых ячеек диапазона Суммирование видимых ячеек диапазона Функции управления датами Расчет следующего понедельника Вычисление следующего дня недели Неделя месяца Управление датами до 1900 года Возвращение последней непустой ячейки столбца или строки. Функция LASTINCOLUMN Функция LASTINROW Функции для работы в нескольких листах Возвращение максимального значения из рабочих листов Функция SHEETOFFSET Дополнительные пользовательские функции Возвращение значения Error Возвращение массива из функции Возвращение массива из неповторяющихся случайных целых чисел Перетасовка массива Использование необязательных аргументов Использование неопределенного количества аргументов Резюме, ПРИЛОЖЕНИЯ Приложение А. Работа с импортированными файлами Lotus 1-2-3 О файлах Lotus 1-2-3 Формулы Lotus 1-2-3 Порядок вычислений Текст в вычислениях Логические значения Проблемы с датами Критерии базы данных Совместимость функций Lotus 1-2-3 Функции конвертирования баз данных Приложение Б. Описание функций Excel Функции Excel по категориям Приложение В. Пользовательские форматы чисел О форматировании числа Автоматическое форматирование цифр Форматирование чисел с помощью кнопок панели инструментов Использование клавиатурных сокращений для форматирования чисел Использование диалогового окна Формат ячеек для форматирования чисел Создание пользовательского формата числа О пользовательских форматах чисел Части строки с форматом числа Коды пользовательского формата числа Примеры пользовательских форматов Масштаб Скрытие нуля Отображение нулей впереди Форматирование процентных значений Отображение дробей Отображение текста в кавычках Повтор текста Отображение знака минус справа Условное форматирование числа Выделение цветом Форматирование дат и времени Отображение текста с числами Отображение нуля с тире Использование специальных символов Устранение определенных типов элементов Заполнение ячейки повторяющимися символами Отображение точек впереди Приложение Г. Дополнительные ресурсы Excel Техническая поддержка Microsoft Параметры поддержки База знаний Microsoft Домашняя страница Microsoft Excel Инструменты Microsoft Office в Web Форумы в Internet Форум по электронным таблицам Форум Microsoft Поиск по форуму Web-узлы Страница электронных таблиц Web-pecypc по Excel Страница по Excel Стефена Баллена Вопросы и ответы Предметный указатель
