Можно ли в формуле электронной таблицы использовать данные других электронных книг

Обновлено: 06.07.2024

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

В 1979г. американцы Д. Бриклини Б. Френкстон создали первый табличный редактор, концепция которого была разработана еще в 1961г. Р. Маттезихом.

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

Ниже представлен фрагмент электронной таблицы.

Основные понятия табличного редактора:

  • Ячейка – наименьший табличный компонент, образуемый при скрещивании строки электронной таблицы и столбца, в котором хранится информация.
  • Строка – ряд смежных горизонтальных ячеек.
  • Столбец – ряд смежных вертикальных ячеек (другое название «колонка» или «графа»).

Каждая колонка электронной таблицы имеет буквенное наименование, а строка – числовое. Таким образом, соединение буквы и цифры на пересечении строки и столбца будет указывать адрес (ссылку)ячейки.

  • Диапазон ячеек – совокупность смежных ячеек, состоящая из нескольких клеток строки и/или столбца. Он обозначается указанием верхней и нижней границ диапазона.

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

Примеры формата данных электронной таблицы

Возможности современных электронных таблиц позволяют производить различные операции:

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

Три самых распространенных табличных процессоров:

Табличные редакторы

При открывании Microsoft Excel создает рабочую книгу, каждая страница которой представляет таблицу из 256 столбцов и более 65 тысяч строк.

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

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

  1. Любая формула должна записываться со знака «=».
  2. Формула может содержать числа, адреса отдельных ячеек или диапазона, знаки операций и функции.

Символы математических операций, используемые в электронных таблицах

  1. Если выражение содержит адрес ячейки, то формула оперирует с ее содержимым. Это позволяет автоматически пересчитать результат при изменении значения ячейки.

Пример формулы с использованием ссылок

Можно увидеть, что в электронной таблице записанная формула соответствует следующим подсчетам: 2*4+6.

  1. Формулы могут содержать одну или несколько функций. Функции табличного редактора - встроенные вычислительные инструменты, обеспечивающие автоматические расчеты. После указания названия функции в формуле в скобках вводятся разного рода параметры (число, адрес ячейки или диапазона ячеек и др.), разделенные между собой «;».

Встроенные функции

Табличное представление информации

Виды информации по форме представления

Рассмотрим подробнее графический способ представления информации, который имеет ряд преимуществ:

  1. облегчает восприятие человеком, благодаря наглядности;
  2. допускает компьютерную обработку;
  3. помогает в сравнительно небольшом объеме охватить суть информации.

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

Структура таблицы

Структуры данных таблицы зависит от ее типа:

Любая таблицы состоит из следующих элементов:

  1. Номер таблицы – нумерационный заголовок.
  2. Общий заголовок – имя таблицы.
  3. Верхний заголовок (его еще называют «головкой») – первая строка таблицы, предназначенная для ввода наименований столбцов.
  4. Боковой заголовок (или «боковик») – левый столбец, в котором записываются имена строк.
  5. Прографка – совокупность горизонтальных и вертикальных полос таблицы, в которой находятся данные, относящиеся к головке и боковику.
  6. Ячейка – основной табличный элемент, образующийся пересечением строки и столбца.
  7. Строка – ряд смежных ячеек, ограниченный линиями сверху и снизу.
  8. Графа (столбец) таблицы – вертикальная колонка данных.

Структура таблицы

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

  • Оформление таблиц начинается с написания номера таблицы, представляющий собой сочетание слова«Таблица» + порядковый номер («Таблица 1»). Размещается он в правом верхнем углу. Это делается для упрощения ссылки на конкретную сводку в документе.
  • Общий заголовок располагается по центру. Он призван давать четкое представление о хранящейся в таблице информации.
  • Наименования строк и столбцов должны быть короткими и понятными. Они записываются с заглавной буквы.В конце заголовков и данных в табличных ячейках точки не принято ставить.
  • Имена строк и колонок можно печатать в свободном порядке. В том случае, когда наименований достаточно много целесообразнее их объединить в группы или перечислить в алфавитной последовательности.
  • Единицы измерений (в том случае, если в прографке указываются числовые данные), записываются только в заголовке. Они вводятся после наименования,через«,». Например, «Температура, °С».
  • Вся область прографки заполняется полностью. Если возникают трудности с заполнением конкретной клетки, то используют прочерк или другой условный знак. Часто используемые символы:

? – неустановленные сведения;

ⅹ – заполнение неосуществимо;

↓ – необходимо использование информации из вышерасположенной ячейки.

Переход текстовой формы в табличную

Таблицы встречаемые в повседневной жизни

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

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

Представление информации в форме таблицы

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

Различают следующие виды таблиц:

  • v «Объекты – свойства» (в сокращении ОС). Таблица этого типа включает сведения о параметрах отдельных объектов, принадлежащих к одному и тому же классу.

Обобщенная форма таблиц типа ОС

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

Изменение значения строк и столбцов

  • v «Объекты-объекты-один» (или ООО). Данная таблица сообщает о единственном значении объектов, принадлежащим к различным классам.

Обобщенная табличная форма ООО

Отличительная особенность таких таблиц:

- прографка содержит исключительно данные одного типа.

Пример таблицы типа ООО

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

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

Табличный способ решения логических задач

Алгоритм решения логических задач с помощью таблицы:

  1. Построить таблицу, определив число строк и графов согласно данным задачи.
  2. Внимательно прочитать каждое условие, отыскав опорные точки, позволяющие сделать исключение. Проставить в нужную ячейку соответствующий символ.
  3. Анализируя условие задачи, сравнивая и устанавливая связи и противоречия, заполнить пустые ячейки таблицы.
  4. Проверить обязательно условие: в каждой строке или столбце должен находиться только один знак «+», а в остальных знак «-». Знаки можно заменить на 0 или 1.
  5. Найти решение, используя метод последовательных исключений.

Разбор решения задачи табличным способом.

Решение задачи №1 табличным способом

1.Определиться со структурой таблицы. Она должна содержать 4 строки (бутылка, стакан, кувшин и банка) и 4 столбца (молоко, лимонад, квас и вода).

Составление таблицы к задаче №1

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

  1. Согласно второму утверждению, «сосуд с лимонадом стоит между кувшином и сосудом с квасом», т.е. в кувшине не может находиться ни лимонад, ни квас. Поставить «-».

4.Так как известно, что банка наполнена не лимонадом и не водой, тогда следует проставить прочерки в строке «банка» в подходящих ячейках.

5.Последнее предположение о том, что стакан поставили возле банки и сосудом с молоком, означает, что молоко находится не в стакане и не в банке. Можно поставить «+» в оставшуюся незанятую ячейку и выяснить, что в кувшин оказался сосудом для молока.

6.Так как в любой строке или в колонке допускается лишь единственный плюс, можно заполнить таблицу полностью.

Ответ: Бутылка наполнена лимонадом, в стакане хранится вода, кувшин является сосудом для молока, а в банке соответственно квас.


В данный момент вы не можете посмотреть или раздать видеоурок ученикам

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

Получите невероятные возможности



2. Раздавайте видеоуроки в личные кабинеты ученикам.


3. Смотрите статистику просмотра видеоуроков учениками.

Конспект урока "Электронные таблицы (на примере Exсel)"

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

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

Однажды в 1979г., сидя на лекции в Гарвардском университете студент финансового факультета Дэн Бриклин наблюдал за преподавателем. Лектору приходилось нелегко. Мало того, что ему нужно было обучить студентов и обеспечить наглядность материала, так ещё и приходилось каждый раз стирать и переписывать большое количество данных в таблице финансовой модели. У Бриклина появилась идея создать компьютерную программу, «электронную таблицу», которая автоматически будет делать перерасчёт всех данных при их изменении.

Он и его друг Роберт Фрэнкстон, который разбирался в программировании, разработали первую программу электронной таблицы, названную ими VisiCalc.

Что такое электронные таблицы?

Какие данные можно вводить в ячейки таблицы?

Основные режимы работы электронных таблиц.

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

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

С помощью табличных процессоров можно решать следующие задачи:

· Создавать электронные таблицы.

· Открывать, редактировать и сохранять электронные таблицы в файле.

· Графически представлять данные с помощью графиков и диаграмм.

· Вычислять и анализировать данные, а также пересчитывать значения при изменении данных.

· Выводить на печать электронные таблицы.

Рассмотрим интерфейс табличного процессора Microsoft Excel.

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

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

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


Давайте познакомимся поближе со всеми частями интерфейса.

В левой части строки заголовков расположена Панель быстрого доступа. Название панели говорит о том, что с её помощью мы можем получить быстрый доступ к важным командам. Вы можете настроить данную панель, разместив на ней команды, которые вы будете часто использовать.

Специалисты компании Microsoft, после проведения ряда серьёзных исследований, объединили наиболее часто используемые функции в «Ленту вкладок», сгруппировав их в логические группы – «Главная», «Вставка», «Разметка страницы», «Формулы», «Данные» и так далее. Эти группы называются вкладками.

Рассмотрим вкладки подробнее:

1. Отдельно выделенная вкладка Файл находится в левой части ленты и отвечает за самые главные действия, без которых вся остальная работа просто не имеет смысла: создание и открытие документа, сохранение, печать, публикацию в Интернете и пересылку по электронной почте созданного вами файла.

2. Вкладка Главная используется в основном при работе с буфером обмена и при форматировании.

3. Вкладку Вставка часто используют при работе с графическими объектами, и в первую очередь она полезна при создании диаграмм.

4. Большинство инструментов, размещённых на вкладке Разметка страницы, имеют прямое или косвенное отношение к настройкам печати документов.

5. Вкладка Формулы позволяет вставлять и редактировать формулы, а также переключать режимы отображения

6. Вкладка Данные используется при обработке данных, в том числе полученных от внешних источников.

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

8. Далее следует вкладка Вид. Группы вкладки содержат пиктограммы команд и меню для настроек внешнего вида документа.

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

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

Рабочий лист представляет собой прямоугольник, разделённый на строки и столбцы, пресечение которых образует ячейки. Столбец – это вертикально расположенная область электронной таблицы. Столбцы обозначают буквами и сочетаниями букв латинского алфавита. Например А, В и так далее; Z, AA, AB и так далее. Строка – горизонтально расположенная область электронной таблицы. Строки нумеруются числами. Всего в Excel 1.048.576 строк и 16.384 столбца.

Каждая ячейка имеет свой адрес, который состоит из названия столбца и номера строки, например: А5, В12. Называя адрес ячейки, в первую очередь указывают имя столбца, а во вторую – номер строки.

Табличный курсор – это выделенный прямоугольник, который можно поместить в любую ячейку. Ячейка, в которую помещён курсор, называется текущей. Данные можно вводить и редактировать только в текущей ячейке.

Диапазон ячеек – это группа нескольких выделенных ячеек. Адрес диапазона состоит из адресов верхней левой и нижней правой ячеек, разделённых двоеточием. Например: В2:В6 или С4:D18.

Какие данные можно вводить в ячейки электронной таблицы?

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

Excel работает с данными следующих типов:


· числовые значения (45; 8,3; );

· текстовые значения (Школа №; 9 класс, ФИО);

· дата и время суток (Январь 2016; 5.04.2017; 13:00);

· примечания («это победитель в олимпиаде по информатике»);

· формулы (=СУММ(С6:С10) или =В5*D5/F5);

· изображения и объекты мультимедиа из файлов или коллекций клипов, фигуры и объекты WordArt;

· гиперссылки на ресурсы Интернета и другие документы.

Рассмотрим основные типы данных более подробно:

Текст. Для ввода текстовых данных выделяют нужную ячейку, набирают текст и нажимают клавишу Enter. Excel автоматически распознает текстовые значения и по умолчанию выравнивает их по левому краю ячейки. Если соседние ячейки не заполнены, то Excel позволяет отображать более длинный текст, перекрывающий расположенные справа ячейки. Если в них уже размещены данные, видимое текстовое значение обрезается, но не удаляется. Полностью текст можно увидеть в строке ввода при выделении ячейки или при изменении ширины столбца.


Числа. Excel автоматически распознаёт числовые значения и по умолчанию выравнивает их по правому краю ячейки. Данные могут быть целыми (10), десятичными (8,5) или обыкновенными дробями (7 ½).

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

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

· Дробную часть числа от целой отделяют запятой.

· Если число разделили точкой, то табличный процессор распознает его как дату (например 1.06 воспринимается как 1 июня, а 12.36 – как декабрь 2036 года).

· Если требуется, чтобы Excel сохранил в виде текста числа, даты и формулы, то вводить их начинают с апострофа (например, если ввести в ячейку апостроф 55 – там появится число 55, выровненное по левому краю без апострофа).

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

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

Ввод формулы в Excel начинают со знака “=”. Знак равенства является признаком начала формулы. Он указывает табличному процессору на необходимость интерпретации следующего за ним выражения в виде формулы. Ввод формулы завершают нажатием клавиши Enter либо щелчком мыши по кнопке «птичка» в строке формул или по любой свободной ячейке. После ввода формулы табличный процессор выполняет вычисления и по умолчанию отображает в ячейке результат вычислений.

Для обозначения арифметических операций используются следующие знаки: плюс (+), минус (–), умножить (*), разделить (/), возведение в степень (^). Порядок выполнения действий не отличается от принятого в математике: сначала вычисляются значения функций и выполняются действия в скобках, затем – возведение в степень, умножение и деление, и наконец – сложение и вычитание.

Пример. В ячейки В2, В3 и В4 записаны числа 8, 9, 10, а в ячейки С2, С3 и С4 – числа 6, 18, 8.


· в ячейке D2 по формуле (=В2+С2) найти сумму чисел;


· в ячейке D3 найти результат вычисления по формуле (=В3*С3/9);


· в ячейке D4 найти разность квадратов по формуле (=В4^2-С4^2).


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

Основные режимы работы электронных таблиц:

· Режим формирования таблицы, который предполагает заполнение и редактирование документа.

· Режим отображения таблицы. Для электронных таблиц может быть установлен режим отображения формул и режим отображения значений (по умолчанию включён режим отображения значений). Чтобы в Excel установить режим отображения формул, нужно на вкладке “Формулы” в разделе “Зависимости Формул” нажать кнопку “Показать формулы”.

· Режим выполнения вычислений. Все вычисления начинаются с ячейки, расположенной на пересечении первой строки и первого столбца электронной таблицы. Вычисления проводятся в естественном порядке. Если в ячейке изменяются данные, то документ пересчитывается заново. В Excel для выбора режима вычислений нужно выполнить команды «Файл – Параметры – Формулы» и в разделе “Параметры вычислений” нужно отметить пункт автоматически.

Важно запомнить:

· Электронная таблица представляет собой табличную модель структурирования, обработки и отображения информации.

· Ячейка образуется на пересечении столбца и строки. Содержимым ячейки может быть текст, число, формула.

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

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

· Выделяют 3 основных режима работы электронных таблиц: это режим формирования таблицы, режим отображения таблицы и режим выполнения вычислений

Раздел 1. Принцип работы электронных таблиц

В отличие от обыкновенных таблиц каждая ячейка электронной таблицы имеет адрес, который образуется так же, как и в популярной игре «Морской бой» из латинской буквы столбца и номера строки, где расположена ячейка.

В ячейки электронной таблицы может находиться текст, числа и формулы. Формулы начинаются со знака «=». В формулах можно использовать адреса других электронных ячеек, в этом случае они замещаются данными, находящимися в этих ячейках. Так, если в ячейке « E 8» содержится число «50», а в ячейку « D 8» введена формула «=E8*10», то в ячейке « d 8» получится результат - число «500».

Ввод и редактирование данных в ячейках

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

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

Форматирование данных в ячейках

Чтобы отобразить данные в ячейке в заданном формате, нужно нажать на ячейке правой клавишей мыши и в контекстном меню выбрать пункт «Формат ячейки». Появится следующее окно.


Рис. 1. Окно диалога выбора формата ячейки

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

Разработать таблицы Excel

При составлении таблиц Excel нужно руководствоваться следующими принципами:

1. Нельзя пропускать пустые столбцы, то есть, если столбец «Наименование» имеет адрес « B », то столбец «Количество» должен иметь адрес « C ». Если название столбца длиннее чем ширина столбца, то его можно растянуть или воспользоваться вкладкой выравнивание в диалоговом окне «Формат ячейки» и установить флажок «Переносить по словам».

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

3. Заголовок столбца нужно писать в одной ячейке.

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

Для расположения текста внутри ячейки в несколько строк можно воспользоваться вкладкой «Выравнивание» в диалоговом окне «Формат» ячейки и отметить там пункт «Переносить по словам» или нажать в ячейке комбинацию клавиш « Alt + Enter ».

Копирование данных в ячейках

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


Рис. 2. Маркер копирования

Маркер копирования появляется в правом нижнем углу выделенного диапазона ячеек. Нажав левой клавишей мыши на маркер и потянув за него, можно скопировать данные в соседние ячейки. Excel анализирует данные в ячейках и, в приведенном примере, в соседние ячейки скопируется арифметическая прогрессия в виде возрастающих на «10» чисел.

При копировании формул с помощью маркера координаты относительного адреса ячеек будут изменяться в соответствии с направлением протягивания. Так, если формулу «= E 8*10» протянуть вниз за маркер копирования, то в следующую вниз ячейку будет копироваться формула «= E 9*10», затем «= E 10*10» и.т.д.

Копирование и перемещение диапазонов ячеек

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

Ввод формул

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


Рис. 3. Строка формул

Кнопка используется для отмены ввода данных в ячейку и равнозначна клавише « Esc ». Кнопка используется для завершения ввода и равнозначна клавише « Enter ». При нажатии на кнопку в строку формул вставляется знак равенства, означающий начало ввода формулы. При вводе формул нет необходимости набирать адрес ячеек вручную, так как щелчок мышью на нужной ячейке автоматически вводит ее адрес.

Построить следующую таблицу Excel

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


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

Копирование формул лучше всего осуществлять с помощью маркера копирования.


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

Использования мастера диаграмм

При построении диаграмм с помощью мастера необходимо правильно заполнить поля на вкладке ряд на втором шаге мастера.


Рис. 4. Вкладка ряд мастера диаграмм

Используя кнопки «Добавить» и «Удалить» нужно выбрать графики ряды данных, которые будут отображены на диаграмме. Ячейка заголовка столбца с данными ряда выбирается с помощью кнопки диапазона в поле «Имя», столбец соответствующих значений - в поле «Значения», шкала по оси « X » выбирается в поле «Подписи оси X ». Выбранные имена для рядов данных отображаются в «Легенде» рядов с обозначением цвета для данного ряда.

Использование ссылок на диапазоны ячеек

В Excel можно использовать относительные, абсолютные и смешанные адреса ячеек. Так ссылка « A 1» - относительная, «$ A $1» - абсолютная, а ссылки «$ A 1» и « A $1» - смешанные. При копировании формул координаты относительной ссылки меняются на величину разности между координатами нового и старого положения ячейки. Такие изменения позволяет копировать целые столбцы или целые строки формул из одной ячейки через маркер копирования. Абсолютные ссылки остаются неизменными при копировании формул, что позволяет всегда ссылаться на одну и ту же ячейку. Смешанные ссылки используются при необходимости изменения одной координаты и неизменности другой, что может потребоваться, например, при создании копии столбца ячеек в таблице.

Для ссылки на диапазон ячеек используется двоеточие, так ссылка « A 1: B 6» ссылается на прямоугольный блок ячеек с верхним левым углом в ячейке « A 1» и нижнем правым углом в ячейке « B 6». Для ссылки на весь столбец буква столбца повторяется после двоеточия, например, « A : A » – столбец « A ». Для ссылки на всю строку номер строки повторяется после двоеточия, например, «1:1» - ссылка на первую строку.

Ссылки на ячейки и диапазоны другого листа или другой книги предваряются названием листа или книги, например, «Лист1! A 1», «Книга1!Лист1! A 1: B 8».

Для ссылки на пересечение диапазонов ячеек используют пробел, так ссылка « A 1: A 15 A 5: B 8» указывает на диапазон « A 5: A 8».

Использование имен

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


Рис. 5. Присвоение имени ячейки

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

Задать для ячейки, определяющей массу хлеба имя и использовать его в формулах. Вставку имени в формулу можно производить по команде «Вставка имени» из меню «Вставка/Имя» или просто введя имя в ячейку.

Форматирование ячеек


Рис. 6. Вкладка "Выравнивание" окна диалога выбора формата ячейки

Для объединения нескольких смежных ячеек нужно их выделить и отметить флажок объединение ячеек на вкладке «Выравнивание» окна диалога выбора формата ячеек

Чтобы выделить цветом границу между ячейками, нужно выделить ячейки и воспользоваться значком «Границы» на панели форматирования или вкладкой «Граница» окна диалога выбора формата ячейки. Изменить направление текста в ячейке можно с помощью элемента «ориентация» на вкладке «выравнивание», а изменение режимов выравнивания текста в ячейке. Толщина и вид границы могут быть выбраны на вкладке «Границы».


Рис. 7 . Вкладка «Граница» окна диалога выбора формата ячейки

Построить следующий бланк квитанции

При составлении сложных бланков на Excel объединение ячеек лучше производить в последнюю очередь, так как в дальнейшем добавление строк или столбцов станет затруднительным. Данный бланк можно построить, вообще, не объединяя ячеек, а лишь пользуясь различными режимами выравнивания и обрамления ячеек. Для определения числа столбцов и строк, нужных для построения бланка, можно провести недостающие линии. Эмблема на бланке может быть построена с помощью панели «Рисование».


Рис. 8 . Фрагмент бланка с показанными линиями сетки

Если панель «Рисования» не видна, то ее можно включить по команде меню «Вид»-«Панели инструментов».

Общей чертой всех табличных процессоров является способ ссылок на ячейки, используемых при конструировании формул. Ссылки бывают трех типов: абсолютные, относительные и смешанные. По умолчанию при создании формул применяются относительные ссылки. Относительная ссылка на ячейку , например, A1, основана на положении этой ячейки по отношению к ячейке, содержащей формулу. При перемещении ячейки с формулой или при копировании формулы вдоль строк или вдоль столбцов относительные ссылки, включенные в формулу, автоматически корректируются. Например, формула "=A1+B1" в ячейке C1 при копировании вниз в ячейку C2 изменяется на "=A2+B2", а при копировании вправо в ячейку D1 изменяется на "= B1+C1".

Абсолютная ссылка на ячейку , например, $A$1, всегда указывает на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. Например, формула "=$A$1+$B$1", введенная в ячейку C1, при копировании в ячейку C2 или D1 останется без изменения.

Смешанная ссылка содержит либо абсолютный адрес столбца и относительный адрес строки, например, $A1, $B1, либо абсолютный адрес строки и относительный адрес столбца, например, A$1, B$1 и т. д. При копировании или перемещении ячейки, содержащей формулу, относительная часть ссылки изменяется, а абсолютная часть остается неизменной, т.е. при копировании формулы вдоль строк и вдоль столбцов относительная часть ссылки автоматически корректируется, а абсолютная часть ссылки не изменяется. Например, формула "=$A1+$B1", расположенная в ячейке C1, при копировании в C2 изменится на "=$A2+$B2", а при копировании в D1 останется без изменения. Напротив, формула "=A$1+B$1", расположенная в ячейке C1, при копировании в C2 останется без изменения, а при копировании в D1 изменится на "=B$1+C$1".

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

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

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

Все табличные процессоры используют одинаковые знаки арифметических и логических операций: "+" – сложение , "–" – вычитание , "*" – умножение , "/" – деление , "^" – возведение в степень, ">" – больше, "<" – меньше, "=" – равно, ">=" – больше или равно, "< http://www.intuit.ru/2010/edi" >

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

Например, пусть требуется решить в MS Excel следующую финансовую задачу: "Через сколько лет ежеквартальные вклады размером 160 тыс. руб. принесут доход в 10 млн руб. при ставке процента 13,5% годовых? Процент начисляется по полугодиям". Для решения этой задачи применяется финансовая функция КПЕР, диалоговое окно которой с заполненными полями и результатом вычисления приведено на рис.7.11.

В современных табличных процессорах существуют возможности построения различных типов двумерных и трехмерных диаграмм, позволяющих представлять числовые данные в наиболее понятном для целевой аудитории графическом виде. Диаграммы используются для облегчения восприятия больших объемов данных и взаимосвязей между различными рядами данных. Количество типов диаграмм, которые можно построить, зависит от конкретного приложения и его версии. На рис.7.12 приведено окно диаграмм для версии 2007 Microsoft Excel .

Приложение Microsoft Excel версии 2007 поддерживает различные типы диаграмм, позволяя строить не только диаграммы стандартных типов, но и смешанные диаграммы, используя несколько типов диаграмм.


Рис. 7.12. Диалоговое окно выбора типа линии тренда

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

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

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


Рис. 7.13. Диалоговое окно выбора типа линии тренда

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

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

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

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

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

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

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

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

Рассмотрим применение аппарата сценария на примере решения задачи следующего содержания. Предположим, что вероятность того, что некто, посетивший электронный магазин на некотором сайте в Интернете, совершит покупку, составляет 20%. Если сайт посетят одновременно 10 человек, то какова вероятность того, что:

  1. никто не сделает ни одной покупки;
  2. точно два человека совершат по одной покупке;
  3. не более 2-х человек совершат по одной покупке;
  4. по крайней мере два человека совершат по одной покупке.

Как изменятся вероятности в пунктах 1)-4), если сайт посетят одновременно 20 человек или если вероятность совершения покупки одним человеком составляет 10%?

На рис.7.16 показан отчет по сценариям для решения каждого пункта сформулированной задачи.

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

Самый простой макрос – это записанная последовательность нажатия клавиш, перемещений и щелчков кнопками мыши. Инструментальное средство Macrorecorder (Макрорекордер) отслеживает действия пользователя, выполняемые при помощи команд меню или клавиатурных эквивалентов ("горячих" клавиш), генерирует и записывает соответствующие макрокоманды до тех пор, пока пользователь не остановит процесс записи. В результате создается программа ( макрос ), выполнение которой дает тот же эффект, что и выполненные действия. Последовательность действий может быть воспроизведена, как магнитофонная запись . Это позволяет автоматизировать процессы обработки данных, проводимые командами меню , неоднократно выполняя записанную программу. Ее можно обработать и изменить, добавив стандартные макрокоманды и макрофункции. Для использования макрорекордера не требуется никаких знаний по программированию. При помощи макрорекордера удобно создавать макросы, которые автоматизируют рутинные операции : форматирование таблиц, создание диаграмм и сводных таблиц, работу со списками данных и т.д.

Созданный макрос можно изменить, добавив операторы встроенного языка программирования или его стандартные функции и организовав диалоги с пользователем. Современные программы обработки электронных таблиц позволяют пользователю создавать на базе табличного процессора новые приложения со специализированными диалоговыми окнами, что делает работу с приложением максимально удобной. Для создания приложений табличные процессоры содержат в качестве дополнительной компоненты язык программирования высокого уровня. Например, в MS Excel это язык Visual Basic for Application ( VBA ) – объектно-ориентированный язык программирования , который базируется на командах и синтаксисе языка Basic . VBA встроен в офисную среду и позволяет манипулировать не только объектами MS Excel , но и объектами всех популярных приложений Microsoft Office.

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

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

Читайте также: