Тема Продвинутые методы обработки текстовых документов
 Скачать 2.96 Mb.
|
|
АвтоСумма на вкладке Формулы. Для вычисления суммы чисел в ячейках, расположенных непрерывно в одном столбце или одной строке, достаточно выделить ячейку ниже или правее суммируемого диапазона и нажать кнопку АвтоСумма. Для подтверждения ввода формулы следует нажать клавишу Enter. Для вычисления суммы произвольно расположенных ячеек следует выделить ячейку, в которой должна быть вычислена сумма, нажать на кнопку АвтоСумма, а затем на листе выделить суммируемые ячейки и/или диапазоны ячеек. Для подтверждения ввода формулы следует нажать клавишу Enter. Кроме вычисления суммы, кнопку АвтоСумма можно использовать при вычислении среднего значения, определения количества числовых значений, нахождения максимального и минимального значений. В этом случае необходимо щелкнуть по стрелке кнопки и выбрать необходимое действие. Редактирование формул • Ячейки с формулой можно редактировать так же, как и ячейки с текстовым или числовым значением: щелкнув мышью два раза по ячейке или в строке формул. • При редактировании ячейки, как и при вводе формулы, ссылки на ячейки и границы вокруг соответствующих ячеек выделяются цветом. • Для изменения ссылки на ячейки и/или диапазон ячеек достаточно перетащить цветную границу к новой ячейке или диапазону. Для того чтобы изменить размер диапазона ячеек, можно перетащить угол границы • Для того чтобы заменить ссылку следует ее удалить, а затем выделить мышью новую ячейку или диапазон ячеек. • В формулу можно добавлять новые операторы и аргументы. Например, в существующую формулу в ячейку В14 в таблице можно добавить оператор "/" (деление) и аргумент А20 В процессе редактирования можно запускать мастер функций. Например, в существующую формулу в ячейку D2 в таблице можно добавить оператор "/" (деление) и в качестве аргумента округление значение ячейки А20. В этом случае после ввода знака "/" (деление) следует любым способом ввести функцию ОКРУГЛ и ее аргументы Ссылки на ячейки других листов и книг • При использовании в создаваемых формулах ссылок на ячейки других листов и книг в процессе создания формулы следует перейти на другой лист текущей книги или в другую книгу и выделить там необходимую ячейку. • При каждом переходе на другой лист, его имя автоматически добавляется к ссылке на ячейку. Имя листа и адрес ячейки разделены служебным символом ! (восклицательный знак). • Например, в формуле в ячейке D2 в таблице на использована ячейка А4 листа Курс текущей книги. При переходе к ячейке другой книги, имя файла в квадратных скобках автоматически добавляется к ссылке на ячейку. Например, в формуле в ячейке D2 в таблице на рисунке использована ячейка А4 листа Курс файла ЦБР.xlsx. Использование трехмерных ссылок Трехмерные ссылки используются при выполнении действий с данными из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. При этом в формулу включаются все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула =СУММ('Комплект1:Комплект5'!D14) суммирует все значения, содержащиеся в ячейке D14 на всех листах в диапазоне от Комплект1 до Комплект5 включительно • Выделите ячейку, в которую нужно ввести функцию. • Введите = (знак равенства), имя функции, а затем открывающую круглую скобку. • Выделите ярлык первого листа, на который нужно создать ссылку. • Удерживая нажатой клавишу Shift, выделите ярлык последнего листа, на который необходимо создать ссылку. • Выделите диапазон ячеек, на которые нужно создать ссылку. • Подтвердите ввод формулы. • Трехмерные ссылки могут быть использованы для создания ссылок на ячейки на других листах, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СЧЁТ, МАКС, МИН, ПРОИЗВЕД, и некоторых других. Использование имен ячеек и диапазонов Использование имен • Имя – слово или строка знаков, представляющих ячейку, диапазон ячеек, формулу или константу. Имена можно использовать в любом листе книги. Присваивание имен • Имя можно присвоить ячейке или диапазону ячеек. • Выделите ячейку или диапазон ячеек. • В группе Определенные имена вкладки Формулы нажмите кнопку Присвоить имя. • В окне Создание имени в поле Имя введите имя ячейки или диапазона • Для задания области действия имени в поле со списком Область выберите Книга или имя листа в книге. • При желании в поле Примечание можно ввести примечание к имени, которое затем будет отображаться в окне Диспетчера имен. Для присвоения имени формуле или константе в поле Диапазон окна Создание имени следует ввести знак равенства (=) и формулу или константу Применение имен • При создании формул короткие имена можно вводить с клавиатуры. • Во избежание возможных ошибок при использовании имен в процессе создания формулы следует в группе Определенные имена вкладки Формулы щелкнуть кнопку Использовать в формуле и выбрать нужное имя в списке имен • Если нужное имя не отображается в списке, выберите команду Вставить имена, а затем в окне Вставка имени выберите вставляемое имя. • Имена ячеек являются абсолютными ссылками. Удаление имени • Ненужное или ошибочное имя можно удалить. • В группе Определенные имена вкладки Формулы нажмите кнопку Диспетчер имен. • В окне Диспетчер имен выделите имя и нажмите кнопку Удалить Ошибки в функциях и аргументах Если формула содержит ошибку функции и/или аргумента, не позволяющую выполнить вычисления или отобразить результат, Excel отобразит сообщение об ошибке. В ячейке с ошибкой в формуле вместо результата вычисления отображается один из кодов ошибки, а в левом верхнем углу ячейки появляется индикатор ошибки (зеленый треугольник). При выделении ячейки с ошибкой рядом с ней появляется кнопка Источник ошибки. Если щелкнуть по кнопке, появится меню, в котором указан тип ошибки, а также команды действий для исправления ошибки. Основные ошибки и некоторые возможные причины их появления приведены в таблице. #ЗНАЧ! Использование недопустимого типа аргумента или операнда В формулу вместо числа или логического значения (ИСТИНА или ЛОЖЬ) введен текст. Для оператора или функции, требующей одного значения, указывается диапазон. #ДЕЛ/0! Деление числа на 0 (ноль). В формуле содержится явное деление на ноль (например, =А1/0). Используется ссылка на пустую ячейку или ячейку, содержащую 0 в качестве делителя #ИМЯ? Excel не может распознать имя, используемое в формуле Используется имя ячейки или диапазона, которое не было определено. Ошибка в написании имени ячейки или диапазона. Ошибка в написании имени функции. В ссылке на диапазон ячеек пропущено двоеточие (:) В формулу введен текст, не заключенный в двойные кавычки. #Н/Д Значение недоступно функции или формуле Не заданы один или несколько необходимых аргументов стандартной или пользовательской функции листа #ССЫЛКА! Ссылка на ячейку указана неверно Ячейки, на которые ссылаются формулы, были удалены или в эти ячейки было помещено содержимое других скопированных ячеек. #ЧИСЛО! Неправильные числовые значения в формуле или функции. В функции с числовым аргументом используется неприемлемый аргумент Числовое значение результата вычисления формулы слишком велико или слишком мало, чтобы его можно было представить в Excel #ПУСТО! Задано пересечение двух областей, которые в действительности не имеют общих ячеек Используется ошибочный оператор диапазона Трассировка связей между формулами и ячейками Для удобства поиска причин ошибок, а также и в некоторых других случаях можно графически отобразить связь между ячейками в формуле. Для отображения ячеек, входящих в формулу в качестве аргументов, необходимо выделить ячейку с формулой и нажать кнопку Влияющие ячейки в группе Зависимости формул вкладки Формулы. Если кнопка не отображается, щелкните сначала по стрелке кнопки Зависимости формул вкладки Формулы Один щелчок по кнопке Влияющие ячейки отображает связи с ячейками, непосредственно определяющими результат вычисления. Если в этих ячейках также находятся формулы, то следующий щелчок отображает связи с ячейками, являющимися аргументами этих формул. И так далее. Для отображения ячеек, в формулы которых входит какая-либо ячейка, ее следует выделить и нажать кнопку Зависимые ячейки в группе Зависимости формул вкладки Формулы. Если кнопка не отображается, щелкните сначала по стрелке кнопки Зависимости формул вкладки Формулы Один щелчок по кнопке Зависимые ячейки отображает связи с ячейками, непосредственно зависящими от выделенной ячейки. Если эти ячейки также влияют на другие ячейки, то следующий щелчок отображает связи с зависимыми ячейками. И так далее. Связи в пределах текущего листа отображаются синими стрелками. Связи с ячейками других листов и книг отображаются черными пунктирными линиями и значком листа. Красные стрелки показывают ячейки, вызывающие ошибки. Для скрытия стрелок связей следует нажать кнопку Убрать все стрелки в группе Зависимости формул вкладки Формулы. Использование логических функций. О логических функциях Логические функции используют для проверки и анализа данных, а также в условных вычислениях. Вместо функций ЛОЖЬ и ИСТИНА можно непосредственно ввести слово с клавиатуры в ячейку или в формулу. Логические функции в качестве аргументов используют логические выражения. Логические выражения используются для записи условий, в которых сравниваются числовые или текстовые значения. В логических выражениях используют операторы сравнения, приведенные в таблице. Оператор Значение = Равно < Меньше > Больше <= Меньше или равно >= Больше или равно <> Не равно Проверка и анализ данных Для наглядного представления результатов анализа данных можно использовать функцию ЕСЛИ. Синтаксис функции: ЕСЛИ(А;В;С), где A – логическое выражение, правильность которого следует проверить; В– значение, если логическое выражение истинно; C – значение, если логическое выражение ложно. Например, в таблице на рис. ниже функция ЕСЛИ используется для проверки значений в ячейках В2:В12 по условию <0,6%. Если значение удовлетворяет условию, то функция принимает значение "ДА", а если значение не удовлетворяет условию, то функция принимает значение "нет". Условные вычисления Часто выбор формулы для вычислений зависит от каких-либо условий. Например, при расчете торговой скидки могут использоваться различные формулы в зависимости от размера покупки. Для выполнения таких вычислений используется функция ЕСЛИ, в которой в качестве аргументов значений вставляются соответствующие формулы. Например, в таблице на рис. ниже при расчете стоимости товара цена зависит от объема партии товара. При объеме партии более 30 цена понижается на 10%. Следовательно, при выполнении условия используется формула B:B*C:C*0,9, а при невыполнении условия – B:B*C:C. 3. РАБОТА С ДИАГРАММАМИ Диаграммы являются средством наглядного представления данных и облегчают выполнение сравнений, выявление закономерностей и тенденций данных. Диаграммы создают на основе данных, расположенных на рабочих листах. Как правило, используются данные одного листа. Это могут быть данные диапазонов как смежных, так и не смежных ячеек. Несмежные ячейки должны образовывать прямоугольник. При необходимости, в процессе или после создания диаграммы, в нее можно добавить данные, расположенные на других листах. Диаграмма может располагаться как графический объект на листе с данными (не обязательно на том же, где находятся данные, взятые для построения диаграммы). На одном листе с данными может находиться несколько диаграмм. Диаграмма может располагаться на отдельном специальном листе. Диаграмму можно напечатать. Диаграмма, расположенная на отдельном листе, печатается как отдельная страница. Диаграмма, расположенная на листе с данными, может быть напечатана вместе с данными листа или на отдельной странице. Диаграмма постоянно связана с данными, на основе которых она создана, и обновляется автоматически при изменении исходных данных. Более того, изменение положения или размера элементов данных на диаграмме может привести к изменению данных на листе. В Excel можно создавать различные диаграммы. Всего существует 11 типов встроенных диаграмм, каждый из которых имеют еще множество разновидностей (видов). Выбор типа диаграммы определяется задачами, решаемыми при ее создании. Один из типов диаграмм является стандартным, то есть он используется по умолчанию при создании диаграмм. Обычно стандартной диаграммой является плоская гистограмма. Создание диаграммы Перед созданием диаграммы следует убедиться, что данные на листе расположены в соответствии с типом диаграммы, который планируется использовать. Данные должны быть упорядочены по столбцам или строкам. Не обязательно столбцы (строки) данных должны быть смежными, но несмежные ячейки должны образовывать прямоугольник. При создании гистограммы, линейчатой диаграммы, графика, диаграммы с областями, лепестковой диаграммы, круговой диаграммы можно использовать от одного до нескольких столбцов (строк) данных. При создании диаграммы типа "Поверхность" должно быть два столбца (строки) данных, не считая столбца (строки) подписей категорий. При создании круговой диаграммы нельзя использовать более одного столбца (строки) данных, не считая столбца (строки) подписей категорий. Как правило, данные, используемые для создания диаграммы, не должны иметь существенно различную величину. 1. Выделите фрагмент таблицы, для которого создается диаграмма. 2. На вкладке Вставка в группе Диаграммы щелкните по кнопке с нужным типом диаграмм и в галерее выберите конкретный вид диаграммы (рис. 1). Рис. 1. Выбор типа и вида создаваемой диаграммы 3. На листе будет создана диаграмма выбранного вида (рис. 2). Рис. 2. Созданная диаграмма Для удаления диаграммы достаточно выделить ее и нажать клавишу Delete. Настройка и редактирование диаграмм Выделение диаграммы Все действия выполняются с выделенной диаграммой или с ее выделенными элементами. Признаком выделения являются рамка диаграммы (рис. 3). На рамке имеются маркеры, расположенные по углам и сторонам рамки. Изменение типа диаграммы После создания можно изменить тип и вид диаграммы. 1. В группе Тип вкладки Конструктор нажмите кнопку Изменить тип диаграммы. 2. В окне Изменение типа диаграммы требуемый тип и вид диаграммы. Добавление и удаление элементов диаграммы Независимо от выбранного макета диаграммы можно добавлять и удалять ее отдельные элементы. Для этого используют элементы вкладки Макет (рис. 4). Рис. 4. Добавление и удаление элементов диаграммы Изменение положения всей диаграммы Диаграмма, созданная на листе с данными, первоначально расположена по умолчанию примерно в центре видимой части листа. Изменить положение диаграммы можно перетаскиванием выделенной диаграммы за область диаграммы. Диаграмму можно переместить в любую часть листа. Диаграмму, размещенную на листе с данными, можно переместить на отдельный лист. 1. Нажмите кнопку Переместить диаграмму вкладки Конструктор. 2. В окне Размещение диаграммы установите переключать на отдельном листе и при необходимости введите имя создаваемого листа. При перемещении диаграммы на отдельный лист автоматически создается новый лист. Работа с такой диаграммой не отличается от работы с диаграммой, размещенной на листе с данными. Выбор стиля оформления Стиль оформления диаграммы определяет, в первую очередь, цвета элементов диаграммы. 1. В группе Стили диаграмм вкладки Конструктор щелкните по кнопке Дополнительные параметры галереи стилей. 2. Выберите требуемый вариант (рис. 5) 3. Рис. 5. Выбор стиля диаграммы Оформление элементов диаграммы Независимо от выбранного стиля диаграммы можно оформлять ее отдельные элементы. Для этого используют элементы вкладки Формат (рис. 6). Рис. 6. Оформление элементов диаграммы 4. СПИСКИ В MS EXCEL Списки в Excel – это таблицы, строки которых содержат однородную информацию В Excel список называют базой данных, при этом строки таблицы – это записи базы данных, а столбцы – поля записей. Чтобы таблица Excel воспринималась, как база данных, столбцам должны быть присвоины однострочные имена, которые будут использоваться как имена полей записей базы данных. При этом необходимо соблюдать некоторые правила : 1. на одном рабочем листе не следует помещать более одного списка; 2. отделять список от других данных рабочего листа хотя бы одним свободным столбцом или одной свободной строкой.; 3. имена полей списка должны располагаться в первой строке таблицы; 4. чтобы отделить имена полей от данных, следует поместить рамку по нижнему краю ячеек строки с именами столбцов; 5. каждый столбец списка должен содержать во всех строках однотипные данные; 6. не следует вводить дополнительные пробелы в начале ячеек данных, поскольку они влияют на сортировку и поиск; 7. внутри списка не должно быть полностью пустых строк и столбцов. |