ТИПЫ ДАННЫХ
ОРГАНИЗАЦИЯ ДАННЫХ В EXCEL
Книга–файл имеет расширение имени XLS. По умолчанию книги получают имена Книга1.xls, Книга2.xls и т.д. (расширение указывать необязательно). Число книг ограничено только размером диска. Книга содержит рабочие листы.
Рабочий лист образует рабочее пространство пользователя. Листов в книге может быть до 255 (по умолчанию три). Лист имеет 256 колонок и 65536 строк (самый большой адрес – IV65536). Т.е. всего на листе 16 777 216 ячеек. Листам можно давать произвольные имена. По умолчанию они получают имена вида Лист1, Лист2 и т.д.
Клетка – элементарная единица данных в таблице. К ней можно обратиться по адресу, который состоит из буквы – номера колонки и цифры – номера строки, например D25 (клетка на пересечении столбца D и строки 25).
Блок(прямоугольная область клеток). Задается адресами левого верхнего и правого нижнего его углов, разделенными двоеточием, например D4:F20.
ТИПЫ ДАННЫХ
1).Числовые– дробные, целые, с буквой Е. Целая часть от дробной отделяется запятой. 2).Символьные– буквы, слова.
3). Даты– начиная с 1.1.1900г. Даты – это целые числа, представленные в формате даты. Даты вводятся в виде цифр, разделенных точкой. Возможно указание и времени. Поддерживается арифметика дат (допустимо их сложение и вычитание).
МАНИПУЛЯЦИИ ДАННЫМИ
ВЫДЕЛЕНИЕ является важнейшим действием. Преобразование данных осуществляется только над выделенными (указанными) объектами. Выделение клетки осуществляется переходом в нее с помощью клавиатуры или мыши. Выделенная клетка обрамляется жирной линией с квадратной точкой в нижнем правом углу – маркером заполнения. На рис. 1 выделена клетка В2. Содержимое этой клетки будет отображаться в строке формул. Выделение строки/столбца произойдет, если щелкнуть мышью на цифре-номере строки или букве-номере столбца. Выделение листа целиком осуществляется щелчком мыши на кнопке “выделить все”. Выделение блока начинается с установки курсора в любом из четырех углов блока. Далее, удерживая клавишу Shift, с помощью клавиш перемещения выделяется остальная часть блока, который при этом (кроме текущей клетки) окрашивается темным цветом. Для выделения блока мышью следует щелкнуть в одном углу блока и, удерживая кнопку, отбуксировать курсор в противоположный угол блока (рис. 2). Курсор при этом должен сохранять вид 
. Выделение несмежных областей листа может осуществляться мышью аналогично при нажатой клавише Ctrl.
ПЕРЕМЕЩЕНИЕ клетки/блока удобно выполнять с помощью мыши. Для этого курсор следует приблизить к любой границе выделенного блока, так чтобы он превратится в пустую 
и, удерживая левую кнопку мыши, отбуксировать блок на новое место. На рис. 3 изображены исходный и конечный виды перемещаемой области A1:B2.
КОПИРОВАНИЕ осуществляется аналогично, но при нажатой клавише Ctrl (при этом рядом с курсором-стрелкой появляется маленький знак плюс 
). Далее выделенный блок может быть скопирован в любое место (рис. 4).
АВТОЗАПОЛНЕНИЕ (множественное копирование). Мышь фиксируется на маркере заполнения блока (курсор превращается в значок +) и при нажатой кнопке буксируется по горизонтали или вертикали (рис.5).
ФОРМИРОВАНИЕ ПРОГРЕССИЙ. Выполняется как автозаполнение, но с нажатием Ctrl(рис. 6). Курсор превращается в ++.
кнопка “выделить все”
| A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | A | B | C | E | E | E | E | ||||||||||||||||
 1
| ||||||||||||||||||||||||||||||||||||||
  
  
   2
| ||||||||||||||||||||||||||||||||||||||
   3
| ||||||||||||||||||||||||||||||||||||||
Рис. 1. Вы- Рис. 2 Рис. 3. Перемещение Рис. 4. Копирование Рис. 5. Заполне- Рис. 6. Прогрессия
делено В2 Выделено В2:C5 A1:B2 в B4:C5 A1:B2 в B4:C5 (+Ctrl) ние E2 в Е3:Е5 E2 в Е3:Е5 (+Ctrl)
ОЧИСТКА освобождает блок от находящихся в нем данных. Осуществляется клавишей Delete. При этом не происходит освобождения ячеек от занесенных в них форматов и элементов оформления (рамок, заливки, объединения ячеек и др.).
УДАЛЕНИЕ – физическое удаление самого объекта (блока, строки, столбца). При этом удаленные объекты замещаются соседними. Удаление выполняется клавишами Ctrl+Минус. Тогда предъявляется окно Удаление ячеек, где можно выбрать способ замещения удаляемых ячеек. Вместо Ctrl+Минус можно воспользоваться контекстным меню (пункт Удалить).
ВСТАВКА. Вставка пустой строки/столбца: выделяется строка/столбец (курсор мыши фиксируется на номере строки/столбца в бордюре) и нажимаются клавиши Ctrl+Плюс. Новая строка будет вставлена сразу над текущей, новый столбец – слева от текущего столбца. Если нужно вставить сразу несколько смежных строк/столбцов, то и выделить следует сразу несколько соответствующих элементов бордюра. Вставка клетки/блока аналогична – выделяется блок нужного размера и положения и нажимается Ctrl+Плюс. В ответ предъявляется окно диалога вставки, где нужно указать, куда именно следует сдвинуть фрагмент таблицы, чтобы принять добавляемый блок. То же можно сделать мышью. После выделения строки/столбца/блока щелчком правой кнопки вызывается контекстное меню где выбирается пункт Вставка.
УПРАВЛЕНИЕ РАЗМЕРОМ СТРОК/СТОЛБЦОВ. Подгонка шириныстолбцов под фактический размер данных выполняется буксировкой границы столбца мышью после установки ее курсора (принимающего вид двунаправленной стрелки), на бордюре таблицы (на вертикальной линии, отделяющей клетки бордюра). Имеется быстрый способ задания минимально возможной ширины столбца/столбцов – двойной щелчок мышью на разделителе клеток бордюра.
ОПЕРАЦИИ С РАБОЧИМИ ЛИСТАМИ. Удаление/вставка листа может быть осуществлена с помощью меню Правка+Удалить лист и Вставка+Лист. Переименование листа реализуется двойным щелчком на вкладке листа внизу экрана после чего можно ввести новое имя. Другой способ – контекстное меню. Перемещение листа. При первоначальном создании листы в книге могут располагаться случайным образом. В последствии у вас появится желание расположить их вкладки в удобном порядке. Это легко сделать отбуксировав вкладку листа в новое место среди других вкладок Копирование листа может быть осуществлено мышью при нажатой Ctrl. Новый лист получает прежнее имя, но с очередным номером (2, 3 и т.д.), которое можно затем изменить.
ВВОД И РЕДАКТИРОВАНИЕ данных может производиться как в строке формул, так и непосредственно в текущей клетке, если предварительно нажать клавишу F2 или дважды щелкнуть мышью. В этом режиме курсор принимает форму вертикальной черты ”½”. По завершении ввода нажмите Enter. При этом курсор переходит на строку ниже. В случае если ввод
производится не по строкам, это неудобно. Тогда для завершения ввода следует пользоваться клавишами со стрелками. Вообще ввод в клетку считается незаконченным, пока редактируемая клетка не покинута любым способом. При этом большинство средств управления Excel остается недоступными. Если в процессе редактирования данных обнаружилось, что были допущены ошибки, можно отказаться от изменения данных, нажав Escape (при условии, что текущая клетка еще не покинута). В случае, если после преобразований данных вы обнаружили, что сделали это неправильно, следует немедленно применить средства отката, нажав на Ctrl+Z, или воспользоваться кнопками отката:
  кнопка Отменить. Возврат к старому виду данных после
их изменения (возможен откат на глубину до 16-и шагов).
| кнопка Вернуть (отмена отката). Используется, если все-таки вы хотите сохранить отмененные изменения. |
Каждое нажатие на них позволяют осуществлять откат (отмену отката) одного действия. Чтобы отменить сразу несколько действий, следует нажать соседнюю кнопку 6 , которая откроет перечень, сделанных ранее отмен (до 16-ти). Здесь можно указать именно то действие, по которое включительно должны быть сделаны отмены. Откат доступен и в меню Правка.
СОЗДАНИЕ ФОРМУЛ.Сила электронных таблиц заключается в возможности помещать в них не только данные, но и формулы. Все формулы должны начинаться со знака “=“ и могут включать константы, знаки операций, функции, адреса клеток (например =5+4/35, =12%*D4, =12*А4-SIN(D3)^2). Операции выполняются слева-направо в порядке их приоритетов, которые могут быть изменены круглыми скобками. Примеры формул и соответствующих им выражений:
|
|
=7+5^3/(6*8) =A5/(C7-4)+(4+F4)/(8-D5)*2,4 2+Sin2А2 =2+(SIN(А2))^2
Замечания к знаку %. Если вы введете в ячейку число со знаком %, фактическое его значение будет в 100 раз меньше. Например, если введено 5%, запомнится число 0,05. Таким образом, вводится процент, а хранится коэффициент. Это действие эквивалентно установлению процентного формата клетки для числа 0,05.
Ссылки на ячейки могут вводиться непосредственно с клавиатуры, но могут более надежно и более быстро указываться мышью, используемой как указка. Здесь гарантируется правильный ввод, поскольку пользователь непосредственно видит (выделяемые объекты обрамляются бегущей пунктирной линией) и выбирает именно те данные, которые он хочет включить в выражение. Положим нужно ввести в ячейку А1 формулу вида =А2+В4·С2. Здесь следует выполнить следующую (см. таблицу) цепочку действий:
КОПИРОВАНИЕ ФОРМУЛ.Пусть в некоторой ячейке записана формула =B4. При копировании мы обнаружим (см. рис.), что копии отличаются от оригинала. При копировании по горизонтали изменяется номер строки, при копировании по вертикали – номер столбца, при копировании по диагонали изменяются оба. Обычно такая трансформация вполне отвечает желанию пользователя. Ему не приходится вручную изменять формулы для применения их в новом месте (в другой строке/столбце). Но так бывает не всегда. Чтобы предотвратить автоматическое изменение адресов, перед “замораживаемой” координатой следует поставить знак $. Так, формула вида =$A$1 не будет изменена ни при каком копировании ее в таблице. Знак $ может быть установлен и только перед одной координатой. Можно вводить $ непосредственно, а можно (в режиме ввода) с помощью клавиши F4. Ее последовательное нажатие влечет поочередный ввод знака $ перед элементами адреса (A1®$A$1®A$1®$A1®A1).
Задание 1. Расчет квартплаты. Положим, она состоит из оплаты за коммунальные услуги (по 2 руб. за кв. метр площади) и газоснабжение (по 3руб. на проживающего человека). Здесь реализован очевидный механизм расчета:
квартплата = тариф_за_коммун._услуги•площадь + тариф_за_газ•человек или D5 =2руб*80м+3руб*4чел
  |
 |
Задание 2. Расчеты по вкладам(сложные проценты).Вычислить прибыль от суммы 1000р (клетка В1) помещенной на 4 года в банк, ежегодная норма прибыли которого 10%, 15%, 20% и 30% соответственно. Механизм начисления:
прибыль = последняя_сумма_на_счету•норма
сумма = предыдущая_сумма_на_счету + доход.
Начиная с 2-го года можно копировать формулы из
В5,С5 вниз.
Задание 3. Повременная зарплата (заданы: дневной тариф – 40руб, размер налога и премии). Решить самостоятельно (заморозив нужные ссылки).
Задание 4. Распределение фонда заработной платы.Распределить зарплату работникам в соответствии с установленным коэффициентом трудового участия. При правильном решении фонд з/п будет в точности равен сумме (B1=C8). Подсказка: сначала найдите сумму всех коэффициентов (В8) и стоимость единицы коэффициента в рублях. Зарплата определяется умножением этой величины на индивидуальный КТУ.
Задание 4а. Вычислить средний баллсдачи экзамена студенческой группой. Результат поместить в любой свободной клетке.
| |
|
 |
|
Формат функции: =ЕСЛИ(условие; ответ_если_условие_ИСТИННО; ответ_если_ЛОЖНО).
Пример: Вывести большее из двух чисел А1 и А2. =ЕСЛИ(A1>A2; A1; A2).
Или в графической форме:=ЕСЛИ(A1>A2; –––да® A1;
нет® A2)
Пример: Вывести сообщение о том, какое из них больше =ЕСЛИ(A1>A2; “A1 больше”; “A2 больше”).
Возможно вложение функций ЕСЛИ (до семи). Для удобства формирования условий используются логические функции:
=И(условие1; условие2; …) – функция истинна, если истинны все ее условия.
=ИЛИ(условие1; условие2; …) – функция истинна, если истинно хотя бы одно условие.
=НЕ(условие) – функция истинна, если условие ложно.
Пример: Вывести большее из трех чисел А1, А2 и А3 =ЕСЛИ(И(A1>A2;A1>A3);A1;ЕСЛИ(И(A2>A1;A2>A3);A2;A3)).
Или в графической форме:=ЕСЛИ(И(A1>A2;A1>A3); ––––да® A1;
нет® ЕСЛИ(И(A2>A1;A2>A3); ––––да ® A2;
нет® A3)).
Задание 5. Премия. Начислим премии работникам, последовательно усложняя условия.
5.1. Пусть, текущий месяц март и женщинам устанавливается премия в размере 300 руб. к 8 марта. Иными словами
премия1=ЕСЛИ(пол=женский, то 300р, иначе 0р).
D3 =ЕСЛИ(B3=”ж”;300;0).
5.2. Такое решение будет правильным только в марте. Если оставить как есть, женщинам будет назначаться премия ежемесячно. Чтобы этого избежать, нужно учесть значение текущего месяца (клетка В1).
премия2 =ЕСЛИ(женщина И март, то 300р, иначе 0р)
Е3 =ЕСЛИ(И(B3=”ж”;B$1=3);300;0).
5.3. Пусть также в феврале и мужчинам назначается премия в 300р.
премия3 = ЕСЛИ((женщина И март) ИЛИ (мужчина И февраль), то 300р, иначе 0р)
F3 =ЕСЛИ(ИЛИ(И(B3=”ж”;B$1=3); И(B3=”м”;B$1=2));300;0).
5.4. Премии мужчинам и женщинам различны: мужчинам 10% зарплаты, женщинам 300 руб. независимо от заработка.
а)премия4 = ЕСЛИ(женщина И март, то 300р, ЕСЛИ(мужчина И февраль, то 10% от зарплаты, иначе 0р))
G3 =ЕСЛИ(И(B3=”ж”;B$1=3);300;ЕСЛИ(И(B3=”м”;B$1=2);10%*С3;0)). – это вложение функций ЕСЛИ
или несколько проще, если разбить выражение на два слагаемых
б) премия4 =ЕСЛИ(женщина И март, то 300р)+ЕСЛИ(мужчина И февраль, то 10% зарплаты)
G3 =ЕСЛИ(И(B3=”ж”;B$1=3);300;0) + ЕСЛИ(И(B3=”м”;B$1=2);10%*С3;0). – это сумма функций ЕСЛИ
5.5. Положим, кроме того, всем работникам назначается премия к Новому году в размере 500р. (представим как сумму).
премия5 =ЕСЛИ(женщина И март, то 300)+ЕСЛИ(мужчина И февраль, 10% зарплаты)+ЕСЛИ(январь, 500)
H3 =ЕСЛИ(И(B3=”ж”;B$1=3);300;0) + ЕСЛИ(И(B3=”м”;B$1=2);10%*С3;0) + ЕСЛИ(B$1=1;500;0).
5.6. Решить самостоятельно. Премия 300р. женщинам (независимо от месяца), зарабатывающим менее 1000р.
I3=ЕСЛИ(. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.7. Решить. Премия всем в мае. Зарабатывающим до 1000р – премия 500р, с 1000р (т.е. при >=1000) – премия в 20% от зарплаты. Сначала как вложение функций ЕСЛИ, затем как сумму
а)J3=ЕСЛИ( . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
б)J3=ЕСЛИ( . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.8. Решить. Премия (всем независимо от месяца)в размере зарплаты, но не менее 500р и не более 1000р.
а)K3=ЕСЛИ(С3<500;500; ЕСЛИ(С3>1000; . . . . . ; . )) – решить как вложение ЕСЛИ
б)K3=ЕСЛИ(С3<500;500;0) + ЕСЛИ(С3>1000;. . . ;0) + ЕСЛИ(И(С3 . . . . . ;С3 . . . . . ); С3;0)– как их сумму
Задание 6. Сдельная зарплата. Известны: стоимость обработки 1 детали (10р), стоимость испорченной детали (20р). Последняя вычитается из зарплаты. Премия назначается рабочим, которые не имели брака и обработали более 10 деталей. Если брака много, зарплата может оказаться отрицательной и налог, конечно, не взимается. Тарифы в формулах должны быть заданы адресами.
зарплата = Оплата_за_детали – Вычеты_за_брак D4=
премия =ЕСЛИ(брак=0 И деталей>10; то Расчет_премии; иначе 0) E4=ЕСЛИ(
налог=ЕСЛИ(зарплата>0; то Расчет_налога; иначе 0) F4=ЕСЛИ(
выдать = зарплата + премия – налог G4=
Усложним. Если изготовлено >20 деталей, кроме обычной премии дается еще 500р.
|
Задание 7.Вычислить значения кусочно-ломаной функции Y(X) в диапазоне изменения аргумента Х от -6 до +16 с шагом 2. Х и Y находятся в столбцах А и В.
Здесь Y=ЕСЛИ(X<0;5+X;ЕСЛИ(И(0<=X;X<10);5;10–0,5•X))
или в адресах для первого значения Y и Х (клетки В2 и А2):
B2 =ЕСЛИ(A2<0;5+A2;ЕСЛИ(И(0<=A2;A2<10);5;10–0,5*A2))или проще
B2 =ЕСЛИ(A2<0;5+A2;ЕСЛИ(A2<10;5;10–0,5*A2)).
Упрощение возможно поскольку, если в первом ЕСЛИ условие A2<0 неверно, значит обязательно верно противоположное A2³0, т.о., выяснять это больше не нужно. Вообще, вложенных функций ЕСЛИ должно быть на единицу меньше возможных диапазонов данных. В нашем случае, поскольку ось Х разбита на три подмножества, должно быть два ЕСЛИ.
Задание 8. Расчеты с вкладчиками бан данных. В нашем случае, поскольку ось Х разбита на три подмножества, должно быть два ЕСЛИ.
Задание 8. Расчеты с вкладчиками банка. Пусть годовой банковский процент так зависит от вклада: при вкладе до 10т.руб. составляет 10%, при вкладе до 30т – 15%, до 60т – 20%, если выше – 30%. Т.о.
|
доход =ЕСЛИ(вклад<10; то 10%; иначе
ЕСЛИ(вклад>=10 И вклад<30; то 15%; иначе
ЕСЛИ(вклад>=30 И вклад<60; то 20%; иначе 30%)))•вклад или проще
доход=ЕСЛИ(вклад<10; 10%;ЕСЛИ(вклад<30;15%;ЕСЛИ(вклад<60;20%;30%)))•вклад
Записать С8, используя только адреса и сделав необходимое замораживание ссылок
C8=ЕСЛИ(B8<A$2;B$2;ЕСЛИ(B8<