2. Вычисления в программе « Образовательные материалы

Ввод данных. Относительные и абсолютные ссылки. Имена ячеек. Связывание листов, рабочих книг. Прогрессии

Ввод данных

В MS Excel данные вводятся в активную ячейку. В момент ввода виден текстовой курсор, для того чтобы закончить ввод данных, нужно нажать клавишу [Enter]

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

Текст

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

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

Число

По умолчанию числа выровнены справа. Числа бывают обычные для вычислений, дата, время и другие форматы.
Дата представлена в виде набора чисел разделённых точкой – 01.02.2014 или слеш — 10/04/2010

Время – в формате часы:минуты:секунды.
Если ввести время, количество часов которого меньше 23, то оно будет отображено так как есть.
Но, если количество часов будет больше 23, то время будет преобразовано в тип дата время.

Например, введём в ячейку A1 — 24:23:12, нажмём [Enter], снова активируем ячейку A1,
нажмём [F2] и снова нажмём [Enter]. Получим дату 01.01.1900 0:23:12.
Здесь программа автоматически преобразовала числовой формат в тип ДД.ММ.ГГГГ ч:мм

Чтобы настроить формат чисел, нужно на вкладке Главная в группе Число, внизу справа раскрыть диалог Формат ячеек:

Формула

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

Ссылки на ячейки

в ячейке A1 введён текст, а в ячейках B2, B3, C2, и C3, введена ссылка на эту ячейку:

в результате ячейки B2, B3, C2, и C3, отобразят то, что есть в ячейке A1:

Здесь важно понять, что физически текст в таблице, написан ОДИН раз — в ячейке A1, а ссылки лишь отображают некое содержимое. Стоит один раз изменить текст в ячейке A1, как наши четыре ссылки отобразят это изменение:

Операторы

Операторы могут быть арифметические, логические и др. Нам дана таблица с числовыми данными в колонке A. Введём в ячейках колонки B, ссылки на ячейки колонки A:

данные продублируются:

К ссылке на другую ячейку, можно добавить оператор, например сложение. Введём в колонке C, ссылки на ячейки колонки B и прибавим к ним число 20:

Результат:

Введём в ячейки колонки D, формулы сложения ячеек колонок A и B:

Результат:

Введём в ячейки колонки E, формулы разности ячеек колонок C и D, в ячейки колонки F, формулы произведения ячеек колонок A и B, в ячейки колонки G, формулы деления ячеек колонок C и A:

Результат:

Адресация ссылок

Относительные ссылки

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

Пример. Дана таблица с продажами изделий:

Рассчитаем колонку Всего по формуле Цена за шт ? Продано шт, т.е. в ячейке E2 формула =C2*D2, в E3 — =C3*D3, и так далее:

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

Как работает относительная адресация. Копируем ячейку, например E3, с формулой =C3*D3, затем клавишей [v], переместим курсор на 5 ячеек вниз в ячейку E8:

Программа изменит в формуле величину строки на количество перемещений, и после команды вставить, в E8 формула будет рассчитана так C(3+5) * D(3+5), или что по другому — C8*D8:

Если скопировать ячейку E8 и вставить на 2 ячейки вверх, то программа отнимет величину строки на 2 единицы. Т.е. C(8-2)*D(8-2) или что по другому C6*D6:

При перемещении по горизонтали, программа изменит в формуле величину колонки на количество перемещений.
Скопируем ячейку E6, затем курсор переместим на две позиции вправо в G6. После команды вставить, формула в G6 будет рассчитана так (C+2)6 * (D+2)6, или что по другому E6*F6

Итак. Нам уже чётко понятно, что если ячейка C1, с формулой =A1+A3:

будет скопирована и вставлена в ячейку C2, то её формула будет такой =A1+A3:

Абсолютные ссылки

Иногда использование только относительных ссылок недопустимо, особенно когда речь идёт о ссылке на одну ячейку. Рассмотрим таблицу создания розничной цены из оптовой:

Ячейки колонки Цена содержат сумму оптовой цены на процент:

Т.е. используется относительная ссылка, это допустимо, а вот процент получается из произведения оптовой цены с единственной ячейкой D1:

А вот что произойдёт, если мы поместим копии ячейки D3 в ячейки D4 и D5:

Смещение ссылок в колонке C нас устраивает, а ссылка на ячейку D1 смещаться не должна. Мы должны запретить это смещение и использовать абсолютные ссылки запрещающие смещение по вертикали и горизонтали. Абсолютные ссылки имеют формат $A$1, символ $ перед колонкой это запрет смещения ссылок вдоль колонок, а перед строкой запрет смещения вдоль строк. В нашем случае достаточно добавить знак $ запрещающий смещение вдоль колонки D, т.е. перед строкой — C3*D$1:

Затем копируем ячейку D3 и вставляем вдоль колонки Процент:

Окончательный вид таблицы:

Настраиваемые форматы

Приведём пример настройки разделителя групп разрядов. Введём число 2,5 в ячейки A1 и A2. Теперь выделим ячейки A1, A2 и A3

Раскроем все настройки форматов ячеек: вкладка Главная, группа Число. Внизу справа кнопка диалога группы, откроем её.

В диалоге Формат ячеек на вкладке Число в списке Числовые форматы выберем Числовой. Справа число десятичных знаков выберем 0 и нажмём ОК.

В ячейках A1 и A2 появились 3. В ячейке A3 введём формулу =A1*A2 нажмём [Enter] В ответе получаем 6, т.к. 2,5 * 2,5=6,25. Вот мы и получили 6 без 25.

Связывание листов

одной рабочей книги

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

имя листа ! имя ячейки + имя листа ! имя ячейки

Рассмотрим рабочую книгу MS Excel состоящую из трёх листов:

Создадим пустой лист, назовём его Iквартал и в любой ячейке введём формулу:

=Январь!E9+Февраль!E9+Март!E9

Нажимаем [Enter] получаем результат 6672540

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

Курсор в любую ячейку листа Iквартал вводим знак =
Клик по ярлыку листа Январь, клик по итоговой ячейке E9, вводим с клавиатуры знак +
Клик по ярлыку листа Февраль, клик по итоговой ячейке E9, вводим с клавиатуры +
Клик по ярлыку листа Март, клик по итоговой ячейке E9, (+ вводить НЕ НУЖНО!)
Правый клик по ярлыку листа Iквартал, нажимаем [Enter]

Нескольких рабочих книг

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

имя листа ! имя ячейки + имя листа ! имя ячейки

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

Откроем файлы I квартал.xlsx, II квартал.xlsx
Создадим новый документ
Удалим Лист2 и Лист3, а Лист1 назовём Итог за полугодие
В любой ячейке листа Итог за полугодие введём формулу:

='[I Квартал.xlsx]Итог за I квартал’!A1+'[04-II Квартал.xlsx]Итог за II квартал’!A1
Нажимаем [Enter]

Если файлы I квартал.xlsx, II квартал.xlsx закрыты, то перед именем файла нужно вводить полный путь к файлу.

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

Имена в формулах

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

Для создания имени, нужно выделить ячейку или диапазон, затем левее строки формул ввести нужное имя и нажать [Enter].

Теперь помесить курсор в именованную ячейку можно с помощью кнопки правее поля имён ячеек

Если формулы были созданы раньше, чем имена для ячеек, то можно заменить ссылки именами командой меню: Вкладка Формулы, группа Определённые имена, Присвоить имя и Применёить имена. В диалоге выбираем нужное имя.

Удалить имя поможет команда: вкладка Формулы, группа Определённые имена, Диспетчер имён. В диалоге выбираем нужное имя и нажимаем Удалить.

Списки

На новом листе введём в ячейку A2 — число 1, B2 – Январь и С2 – Понедельник

Активируем ячейку A2. За маркер автозаполнения (квадратик в правом нижнем углу активной ячейки), удерживая левую кнопку мыши протянем до десяти ячеек вниз

Появятся копии ячейки A2, а справа от курсора появится смарт-тэг, раскроем его и выберем пункт Заполнить:

Мы получили числовую последовательность. Аналогично заполним ячейки от B2 и C2.

Аналогично можно получить последовательность, например такую: Раздел 1, Раздел 2,…,Раздел N-1, Раздел N

Арифметическая прогрессия

В колонке A, мы создали, числовую последовательность, где разница между её элементами – 1. Если нужно, чтобы разность между элементами последовательности была больше 1(2, 6, 10…) или меньше 1(2, 2.5, 3, 3.5, 4…), а также чтобы разница между элементами последовательности была не одинаковая – применяют команду Прогрессия.

Создадим новый документ и выведем в столбце последовательность от 0 до 1000 с шагом 100:

1 способ:

Введём 0 и 100 в ячейки A1 и A2.

Выделим эти ячейки и за маркер автозаполнения протянем вниз

2 способ:

В ячейке B1 введём 0

Вкладка Главная/Редактирование/Заполнить/Прогрессия.

В диалоге Прогрессия выбираем: По столбцам, Шаг 100, Тип Арифметическая, Предельное значение 1000, нажимаем ОК.

(Команду Прогрессия можно было вызвать, если протянуть маркер удерживая правую кнопку мыши)

По умолчанию время в последовательности изменяется по часам: 12:10, 13:10… Если нужно, чтобы время изменялось только по минутам с шагом в 5 минут, то в первых двух ячейках последовательности вводим 12:10 и 12:15 и за эти две ячейки создаём последовательность.

2. Вычисления в программе

0 Responses to “2. Вычисления в программе”

Мета

Разделы