Дополнительные возможности автозаполнения

 

Если растягивание автозаполнения выполнить правой клавишей мыши, то появится меню, в котором могут быть следующие команды:

• Копировать ячейки — дублирование данных ячейки;

• Заполнить — ввод значений последовательности с изменением фор­
мата (значение по умолчанию);

• Заполнить только форматы — заполнение только формата, без из­менения значений, т.е. по сути, копирование формата начальной
ячейки;

• Заполнить только значения — заполнение только значений, без из­менения формата;

 

Рис 19.13 Дублирование данных

 

• Линейное приближение — линейная аппроксимация;

• Экспоненциальное приближение — экспоненциальная аппроксима­ция;

• Прогрессия... — вызов окна Прогрессия.

Первые четыре из этих команд, появляются и после щелчка по кнопке пара­метров автозаполнения (рис. 19.13).

Копирование формул. Использование абсолютных адресов

Автозаполнение очень эффективно может использоваться для копирования формул. Так, например, если в ячейке D2 содержится формула

=СУММ(А2:С2)

то автозаполнение вниз приведет к появлению в расположенных ниже ячей­ках формул =СУММ(АЗ:СЗ), =СУММ(А4:С4) и т.д. Если в ячейке А4 со­держится формула

=СУММ(А2:А4)

то автозаполнение вправо приведет к появлению в расположенных справа ячейках формул =СУММ(В2:В4), =СУММ(С2:С4) и т.д.

Таким образом, при копировании формул путем автозаполнения адреса яче­ек корректируются. Такая же корректировка адресов производится и при копировании выделенных блоков. Изменяющиеся при копировании, пере­мещении и автозаполнении адреса называются относительными.

Однако в некоторых случаях требуется, чтобы некоторые адреса в формулах при выполнении подобных операций не изменялись. Для этого адреса, кото­рые требуется сохранить неизменными, следует из относительных преобра­зовать в абсолютные.Например, если в ячейке А7 записана формула =А5*А9

и необходимо, чтобы при автозаполнении вправо в произведении изменялся только первый сомножитель (В5, С5, D5 и т.д.), а второй оставался неизмен­ным (это может, например, понадобиться для умножения суммы в долларах на курс доллара), то перед автозаполнением в ячейку А7 следует ввести фор­мулу

=А5*$А$9

в которой адрес А9 преобразован из относительного в абсолютный путем до­бавления символов $.

Для вставки символов $ при редактировании ячейки, достаточно установить текстовый курсор в относительный адрес и нажать F4.

 

Глава 20. Типы и форматы данных в Excel 2007

 

Типы данных

 

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

• Текстовое значение.

• Числовое значение.

• Формулу.

 

Текстовые значения

 

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

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

 

 

Рис. 20.1. Ввод текстовых значений

 

мер, в ячейки D6 и D7 на рис. 20.1 введен одинаковый текст, однако текст в ячейке D6 виден полностью, а в ячейке D7 нет, т.к. соседняя ячейка Е6 пус­тая, а Е7 заполнена.

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

Иногда требуется, чтобы текст, вводимый в ячейку, размещался в ней не в одну, а в несколько строк. В этом случае для перехода на следующую стро­ку внутри ячейки необходимо во время набора нажать комбинацию «Alt» + «Enter».

 

Числовые значения

 

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

 

Различают два вида числовых значений:

• число;

• дата и время.

 

Числа

 

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

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

Если число меньше единицы, то нуль перед разделителем набирать необяза­тельно, после ввода числа он будет добавлен автоматически.

Если неизвестен десятичный эквивалент нецелого числа, то оно может быть введено в дробном виде. Например, для ввода числа 23/7 необходимо через пробел набрать 2 3/7, а для ввода числа 3/7 — 0 3/7. После ввода такого чис­ла в самой ячейке оно будет показано в том же виде, в котором было набрано, например 2 3/7. Реально же в ячейку будет записано десятичное число. Если установить табличный курсор на ячейку с дробным числом, то в секции со­держания панели формул будет показано это десятичное число.

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

Если при наборе после числа поставить знак процента, то реально в ячейку будет записано число, которое Меньше чем введенное в 100 раз. При этом и в ячейке, и в секции содержания панели формул будет показано то значение, которое набиралось. Например, после ввода значения 12% в ячейке будет показано 12%, а реально записано число 0,12. И именно оно и будет исполь­зоваться в формулах при расчетах.

Даты и время

Дата и время относятся к числовым значениям, и их можно использовать при вычислении по формулам. Excel распознает введенное значение как дату (время) в том случае, когда оно имеет один из заранее установленных фор­матов представления. В противном случае значение будет восприниматься как текст. Можно вводить только дату, только время или и то, и другое.

Русскоязычная версия Excel распознает следующие форматы представле-

ния даты и времени (на примере 3 марта 1997 года 13 часов 15 минут 17,1 секунд):

 

Дата				Время
3.3.1997	3.	Map.		13:15 1:15 PM
3.03.1997	3.	Map	13:15:17.1	1:15:17.1 РМ

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

Для вставки в ячейку текущей даты можно нажать Ctrl+Shift+;.

Дата в Excel воспринимается как число дней отсчитанных от некоторой на­чальной даты. По умолчанию начальная дата — 1 января 1900 года. Нача­лу этих суток, т.е. 0 часов, 0 минут, 0 секунд, соответствует число 1. Время представляется как десятичное число меньше единицы, которое показывает, какая часть суток истекла, например, для полудня, т.е. для 12:00, это 0,5.

Если указывается дата и время, то Excel воспринимает это значение как сум­му чисел, соответствующих дате и времени. Дате 3 марта 1997 года при точке отсчета 1.01.1900 соответствует число 35492, а времени 13 часов 15 минут 17,1 секунд - число 0,55228. В итоге этому моменту времени соответствует число 35492,55228.

Если введенное число не помещается в ячейку, то оно будет иметь экспонен­циальную форму представления, например 6Е+08, т.е. 6х10⁸. Если в ячейку не помещается и экспоненциальное представление числа или если в ячейку не помещается дата, то в ней появляются символы ###. В этом случае следу­ет расширить столбец таблицы или уменьшить размер шрифта.

Для того, чтобы вводимое числовое значение (число или дата) восприни­малось как текстовое, необходимо перед ним набрать апостроф или набрать знак равенства и заключить число в кавычки, например '0099 или ="0099". При этом, апостроф и кавычки в ячейке видны не будут. Такое преобразо­вание может понадобиться, например, тогда, когда в ячейку вводится код номенклатуры товара, состоящий из цифр и начинающийся с нулей. В этом случае, если просто ввести 0099, то Excel автоматически распознает это зна­чение как числовое, и ведущие нули выводиться не будут, т.е. будет показано число 99.

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

 

Форматы данных

 

Каждый раз при вводе в ячейку тех или иных данных, Excel автоматически старается определить их тип и присвоить ячейке соответствующий формат - форму представления данных. Важность назначения правильного фор­мата проиллюстрируем на примере: если даже в ячейку введено числовое значение, но установлен текстовый формат (формат Текстовый), то содер­жимое ячейки уже будет восприниматься как текстовое, ячейку нельзя бу­дет использовать в математических формулах, так как текст не может в них использоваться.

С другой стороны, попробуйте ввести текст, начинающийся со знака «=» (равно). У вас ничего не получится, так как Excel все, что начинается со зна­ка «равно» Excel распознает как формулу, и если после знака «=» вы далее начнете вводить какой-либо текст - будет автоматически выдано сообщение об ошибке.

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

Выход из подобных ситуаций один - вручную переназначить формат для ячейки - принудительно установить для нее формат Текстовый или Число­вой (или какой-либо еще).

В Excel имеется набор стандартных форматов (наборов параметров) ячеек, которые могут применяться во всех документах.

В начале работы с новой таблицей все ячейки имеют одинаковый стандарт­ный формат, используемый по умолчанию. Этот формат называется Обыч­ный. Он имеет следующие параметры:

• изображенное значение повторяет истинное;

• выравнивание по горизонтали зависит от типа данных;

• выравнивание по вертикали выполняется по нижнему краю;

• текст без переноса слов;

• ориентация изображенного значения горизонтальная;

• для отображения содержимого используется стандартный шрифт без
выделения и эффектов;

• обрамления вокруг ячеек нет;

• заполнения нет.

 

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

• Числовой - стандартный числовой формат, автоматически используемый во всех случаях, когда Excel определяет содержимое ячейки как числовое.

• Финансовый и Денежный — число округляется до двух знаков пос­ле десятичного разделителя, например число 43,569 представляется в виде 43,57р. При этом после самого числа ставится знак денежной единицы (его можно задать в дополнительных настройках форма­та, на российских компьютерах по умолчанию ставится значение р. (то есть рубли)). Разница между данными форматами состоит в том, что формат Денежный позволяет отображать отрицательные суммы, например, другим цветом и без знака минус, а также устанавливать
произвольно выравнивание в ячейке.

• Процентный — если в ячейку после установки формата введено чис­ло от 0 до 1, то оно умножается на 100, затем число (независимо от величины) округляется до целого и к нему добавляется знак %, в ре­зультате этого, например, числа 0,43569 и 43,569 будут представлять­ся одинаково 44%.

• Дробный - применяется в тех случаях, когда необходимо то или иное нецелое число отобразить не в виде десятичной дроби (напри­мер 2,25), а в виде обыкновенной дроби (будет 2 1/4).

• Экспоненциальный - данный числовой формат предназначен для отображения чисел в виде произведения двух составляющих: числа, находящегося в пределах от 1 до 10, и степени числа 10 (положитель­ной или отрицательной). Например, число 0,789 в ячейке с форма­том Экспоненциальный будет выглядеть как 7,89Е-01 (то есть 7,89 умноженное на 10 в степени -1).

• Дата - содержимое ячейки будет интерпретироваться как дата и соответствующим образом преобразовываться при отображении

• Время — содержимое ячейки будет интерпретироваться как время и соответствующим образом преобразовываться при отображении.

• Текстовый - при установке данного формата содержимое ячейки будет восприниматься как текстовое. Данный формат автоматически устанавливается во всех случаях, когда Excel определил тип вводи­мых в ячейку данных, как текстовый.

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

Рис. 20.2. Внешний вид содержимого ячейки при разных установленных форматах

 

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

20.3. Назначение формата данных для той или иной ячейки

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

1. Выделить эти ячейки.

2. Перейти на вкл адку Главная ленты инструментов и щелкнуть мышкой по кнопке в группе Число. Либо можно щелкнуть правой кнопкой мыши по выделенным ячейкам и в появившемся контекс­тном меню выбрать команду Формат ячеек. В результате любого из указанных действий на экране отобразится диалоговое окно Формат ячеек.

3. В окне Формат ячеек перейдите на вкладку Число и в списке Число­ вые форматы выделите нужный формат.

4. Нажмите кнопку ОК.

Для тех или иных форматов можно будет задать дополнительные параметры отображения. Так для формата Число, вы сможете установить, какое коли­чество знаков должно отображаться после запятой (десятичного раздели­теля). Наблюдать эффект от применяемых настроек формата можно прямо «не отходя от кассы», то есть непосредственно на вкладке Число, в поле Об­разец.

Рис. 20.3. Диалоговое окно «Формат ячеек»