Использование текста в формулах

Если в формуле используется текст, то он обязательно должен быть заключен в двойные кавычки. Если написать формулу «=мама», Excel выдаст ошибку, а если написать «="мама"» — корректная формула.

Использование ссылок в формулах[править]

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

Если в формуле используется несколько ссылок, то каждой из них Excel дает свой цвет. Это очень удобно. Пример: напишите в какой либо ячейке формулу «=A1+D1», нажмите Enter, затем два раза щелкнете по ячейке. В ячейке вы увидите формулу с разноцветными ссылками, а вокруг ячеек A1 и D1 будут прямоугольники соответствующих цветов. Гораздо проще найти, куда указывает ссылка, по цвету прямоугольника, чем просматривать буквы столбцов и номера строк. Наведите курсор мыши на один из разноцветных прямоугольников и перетащите левой кнопкой за границу в другое место. Вы увидите, что при этом меняются и адреса ячеек в формуле — часто это самый быстрый способ подправить адреса в формуле, особенно после копирования маркером автозаполнения.

Функции в Microsoft Excel[править]

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

Все функции в Excel характеризуются:

· Названием;

· Предназначением (что, собственно, она делает);

· Количеством аргументов (параметров);

· Типом аргументов (параметров);

· Типом возвращаемого значения.

В качестве примера разберем функцию «СТЕПЕНЬ»

· Название: СТЕПЕНЬ;

· Предназначение: возводит указанное число в указанную степень;

· Количество аргументов: РАВНО два (ни меньше, ни больше, иначе Excel выдаст ошибку!);

· Тип аргументов: оба аргумента должны быть числами, или тем, что в итоге преобразуется в число. Если вместо одного из них вписать текст, Excel выдаст ошибку. А если вместо одно из них написать логические значения «ЛОЖЬ» или «ИСТИНА», ошибки не будет, потому что Excel считает «ЛОЖЬ» равно 0, а истину — любое другое ненулевое значение, даже −1 равно «ИСТИНА». То есть логические значения в итоге преобразуются в числовые;

· Тип возвращаемого значения: число — результат возведения в степень.

Пример использования: «=СТЕПЕНЬ(2;10)». Если написать эту формулу в ячкейке и нажать Enter, в ячейке будет число 1024. Здесь 2 и 10 — аргументы (параметры), а 1024 — возвращаемое функцией значение.

Пример формулы для вычисления длины окружности, содержащую функцию ПИ():

Синтаксис записи функции[править]

Как вы видите, чтобы Excel не выдал ошибку, функция должна соответствовать определенному набору правил. Этот набор правил называется синтаксис записи функции.

Общий синтаксис записи любой функции в Excel:

имя_функции([аргумент_1; аргумент_2; … ; аргумент_N])

Список аргументов заключен в квадратные скобки, что говорит о том, что это необязательная часть.

Работа с мастером функций[править]

Общие сведения. Способы запуска[править]

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

Существует 3 способа запуска мастера функций:

1. С помощью кнопки в строке формул;

2. С помощью команды "Другие функции..." кнопки сигма;

3. С помощью пункта меню "Вставка" —> "Функция";

Первый шаг[править]

После выполнения одного из этих действий откроется окно мастера функций:

На первом шаге мы выбираем нужную нам функцию, пользуясь поиском или фильтром категорий. После выбора нужной функции нажимаем "ОК" и попадаем на второй шаг.

Второй шаг[править]

Открывается следующее окно

Вставка вложенной функции[править]

В одну функцию можно вставить другую функцию. Допускается до 7-ми уровней вложения функций (в Office 2007 — до 64). Конечно, функцию можно записать вручную (писать название вложенной функции, открывать скобки, ставить точки с запятой). Однако это противоречит самой идеологии мастера функций, который должен облегчать написание формул, защищать пользователя от ошибок и свести к минимуму ручную работу. Существует более удобный способ вложить функцию — специальная кнопка на панели "Строка формул":

После выбора нужной функции из выпадающего списка Excel вставит название фукнции и круглые скобки в указанное место в формуле (в активное текстовое поле аргумента). После этого окно мастера функций для предыдущей функции (в этом примере "СУММ") сменится на окно для вставляемой функции ("СТЕПЕНЬ"), и ее название в формуле сделается жирным:

МАТЕМАТИЧЕСКИЕ

Категория Математические (Мат. И тригонометрия) - одна из самых густонаселенных в Excel (60 команд в 5.0 и 50 в более поздних). Никаких особых хитростей в ней нет. Если SIN, значит, синус, если ASIN, значит, арксинус. Если LN - то натуральный логарифм, если LOG10 - то десятичный, а если LOG - то по произвольному основанию.

Функция ГРАДУСЫ преобразует радианную меру угла в градусную, функция РАДИАНЫ делает обратную операцию. Есть функция ПИ, которая позволит использовать в вычислениях эту математическую константу, а также СЛЧИС - генератор случайных чисел. Желающие могут поупражняться в переводе арабских цифр в римские (РИМСКОЕ). Функция ОСТАТ вычисляет остаток от деления, ЧАСТНОЕ - делит нацело. 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.

У меня есть 12 файлов почти одинаковых по структуре, в идеале из них нужно сделать один сводный, еще что бы и при внесении изменений в один из файлов, это отображалось в сводном. Это вообще можно сделать в Эксель?

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

В новой книге, которая будет содержать консолидированные данные устанавливаем курсор в ячейку А1 и на вкладке Данные в группе Работа с данными выбираем команду Консолидация.

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

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

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

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

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

После указания всех данных нажимаем кнопку ОК.

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

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

1 В таблице выберите ячейки с данными, которые необходимо включить в диаграмму. Кроме того, можно выбрать один или несколько диапазонов данных в диалоговом окне создания диаграммы. Для этого нажмите Выбрать диапазон... и введите один или несколько диапазонов, используя кнопку Добавить другой диапазон.

2 Нажмите на значок Диаграмма значок диаграммы на панели меню либо выберите в меню Вставка пункт Диаграмма. Появится диалоговое окно "Редактор диаграмм".

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

4 Если ни одна из рекомендованных диаграмм не подходит, нажмите на ссылку Дополнительно >> или откройте вкладку Диаграммы, чтобы просмотреть дополнительные варианты.

5 Выполните предварительный просмотр диаграммы и внесите в нее необходимые изменения.

6 Нажмите кнопку Вставить. Диаграмма появится в электронной таблице. Чтобы вернуться к редактированию таблицы, не вставляя диаграмму, нажмите кнопку Отмена.

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

Приступив к созданию диаграммы, как описано выше, откройте в диалоговом окне "Редактор диаграмм" вкладку Настройка.

На вкладке Настройка введите название в текстовое поле Название диаграммы. Название диаграммы отображается в ее левом верхнем углу при выборе диаграммы в таблице.

На вкладке Настройка введите заголовок в текстовое поле Заголовок диаграммы. Заголовок отображается в виде ярлыка в верхней части диаграммы.

Типы диаграмм

Microsoft Excel позволяет создавать различные диаграммы. Всего существует более 30 типов встроенных диаграмм, многие из которых имеют еще несколько разновидностей (видов). Основные типы диаграмм приведены в таблице. Выбор типа диаграммы определяется задачами, решаемыми при ее создании. Наиболее часто используемые типы диаграмм приведены в таблице.

Гистограмма

Линейчатая диаграмма

Круговая диаграмма

Кольцевая диаграмма

График

Диаграмма с областями

Помимо встроенных типов диаграмм, пользователь может создавать, сохранять, а затем использовать собственные типы диаграмм.

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

Табличный процессор MS Excel дает в руки пользователя мощные средства анализа данных, если таблица, где они хранятся, организована особым образом, называемым списком. Список – способ хранения данных в таблице, совокупность поименованных строк, содержащих однородные данные (набор строк таблицы, содержащий связанные данные).

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

на листе рабочей книги может размещаться только один список;
если на этом листе размещаются данные, не входящие в список, то их должны отделять от списка не менее одного пустого столбца и одной пустой строки;
первая строка списка должна содержать заголовки столбцов;
оформление заголовков столбцов должно отличаться от данных, для этого используется их выделение с помощью шрифта, выравнивания, форматов и рамок;
во всех ячейках столбца размещаются однотипные данные, при этом используется один формат;
перед содержимым ячейки не должно быть пробелов;
для поиска записи, подлежащей удалению или изменению, следует нажать кнопку Критерии и ввести в соответствующие поля условия поиска. Затем с помощью кнопок Далее и Назад найти записи, соответствующие этим условиям. Для поиска необходимых данных можно, так же как и в текстовом процессоре MS Word, использовать команду Редактирование/ Найти.

В этом случае для организации поиска используется Раскрывающийся список Найти и выделить. Табличный процессор MS Excel позволяет производить сортировку по нескольким показателям (до трех). Очевидно, что в первую очередь сортировка производится по той категории, которая включает в себя наибольшее число записей, так как последующая сортировка осуществляется уже внутри нее. Для упорядочения данных в ячейках по значениям (без учета формата) в Microsoft Excel предусмотрен определенный порядок сортировки – по возрастанию или по убыванию, причем этот порядок зависит от типа данных.

Фильтрация данных

Фильтрация – это способ поиска подмножества данных в списке в соответствии с заданными условиями. В табличном процессоре MS Excel используется два способа фильтрации списков: Автофильтр для простых условий отбора и Расширенный фильтр для более сложных условий. В отличие от сортировки при фильтрации порядок записей в списке не изме-няется. При фильтрации временно скрываются строки, которые не требуется отображать. Строки, отобранные при фильтрации в Microsoft Excel, можно редактировать, форматировать и выводить на печать, а также создавать на их основе диаграммы, не изменяя порядок строк и не перемещая их.

Чтобы отфильтровать список с помощью Автофильтра, небходимо выделить одну из ячеек списка и выбрать команду Данные/Фильтр/Автофильтр

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

Для задания второго условия следует установить переключатель в положение И или ИЛИ и выбрать из разворачивающихся наборов в полях второго условия необходимые оператор и значение сравнения. В расширенном фильтре условия отбора вводятся в диапазон условий на листе книги. Команда Расширенный фильтр применяется, чтобы отфильтровать данные в тех случаях, когда для отбора записей требуется записать:

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

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

С помощью переключателя Обработка, расположенного в этом окне, пользователь должен указать программе, где следует размещать отфильтрованные записи – на месте или в другом диапазоне. Чтобы поместить отфильтрованные записи за пределами существующего списка, следует установить переключатель Обработка в положение Скопировать результат в другое место, а в поле Поместить результат в диапазон указать верхнюю левую ячейку области вставки. Затем необходимо ввести в поле Диапазон критериев ссылку на диапазон условий отбора, включая заголовки.

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

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

Пример простейшей базы данных в виде таблицы:

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

Программное обеспечение для управления и поддержки работоспособности БД называют системой управления базами данных (СУБД). СУБД осуществляют ввод, проверку, систематизацию, поиск и обработку данных, распечатку их в виде отчётов.

Среди множества СУБД наиболее часто используются пакеты программ dBASE разных версий, FoxBase +, FoxPro, Fox Soft Ware, Clipper, совместимые с dBASE по системе команд и файлам.

Например, БД, созданная в одной СУБД, может использоваться в другой совместимой с ней СУБД, имеющей формат файлов dBASE (*.dbf). Однако есть иные СУБД, например PARADOX и RBase, несовместимые с dBASE. Кроме СУБД для DOS, существуют СУБД, работающие в среде Windows, например Access, MS Works и др.

В основе БД лежит представление данных в виде таблиц. Основными понятиями в СУБД являются поля и записи. В полях содержатся данные. Поле характеризуется длиной. Совокупность всех полей в строке называется записью.

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

Типы данных

В СУБД можно обрабатывать следующие типы данных:

· Символьный (Character).

· Числовой (Numeric).

· Дата календарная (Date).

· Логический (Logical).

Данные символьного типа — это любая последовательность символов длиной не более 254.

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

Значения календарной даты по умолчанию отображаются в Американском формате ММ/ЧЧ/ГГ (ММ-месяц, ЧЧ-число, ГГ-год). Длина этого поля установлена автоматически и равна 8.
Данные логического типа имеют значения ДА (YES) и НЕТ (NO).

В математической логике они называются Истина (True) и Ложь (False). В логических полях БД используются только первые буквы латинских слов Y,T,N,F. Длина логического поля равна 1.
В поле примечаний отмечается признак, который указывает, что к записи прилагается дополнительный фрагмент текста.

⇐ Предыдущая 1 234 5 6 Следующая ⇒