Автоматическое изменение формата ячейки после ввода данных
После ввода определенных последовательностей символов, Excel автоматически изменяет формат ячейки. После этого все в дальнейшем вводимые в эту ячейку числа Excel пытается преобразовать в этот формат.
§ Дата. Если в ячейке написать «1.2.3» или «1/2/3», Excel заменит это на «01.02.2003» (первое число второго месяца третьего года). Если написать «1.2» или «1/2», то Excel заменит это на «01.фев». При этом формат ячейке будет автоматически преобразован в «Дата»;
§ Процентный. Если в ячейке написать «1%», формат ячеки автоматически сменится на «Процентный»;
§ Время. Если в ячейке написать «13:46:44» или «13:46», формат ячейки автоматически сменится на «Время»;
Внимание!!! на разных компьютерах форматы по умолчанию представления чисел, денежных единиц, даты и времени могут отличаться! Настроить их можно по пути »Панель управления» —> «Язык и региональные стандарты» —> закладка «Региональные параметры».
Изменение формата ячеек с помощью кнопки панели инструментов «Форматирование»
На ленте «Главная – Число» находятся 5 кнопок, с помощью которых можно быстро изменить формат выделенных ячеек.
Описание кнопок (слева направо):
§ Денежный формат. Будет использована денежная единица по умолчанию (см. выше);
§ Процентный формат. Если в ячейке уже будет находится число, то Excel домножит его на 100 и добавит знак «%». Все правильно, ведь 1 арбуз — это «100%», а «0,7» арбуза — «70%»;
§ Формат с разделителями (числовой формат). В этом формате будут отделяться пробелом группы разрядов (сотни, сотни тысяч и т.д.) и будет добавлено 2 знака после запятой;
|
|
|
§ Увеличить разрядность. Добавляет один десятичный разряд;
§ Уменьшить разрядность. Убирает один десятичный разряд.
Изменение формата с помощью окна «Формат ячеек»
Общие сведения. Способы запуска
В окне «Формат ячеек», в закладке «Число», можно выбрать один из нескольких предопределенных в Excel форматов, настроить их, а также создать свой формат, используя специальный язык описания форматов.
Существует 3 способа открытия окна «Формат ячеек»:
1. выделяем нужные ячейки —> вкладка Главная… ;
2. выделяем нужные ячейки —> щелкаем правой кнопкой на одной из них —> «Формат ячеек»;
3. выделяем нужные ячейки —> нажимаем «Ctrl+1».
Описание форматов:
Формат «Общий»
см. выше;
Формат «Числовой»
§ можно настроить число десятичных знаков, автоматически добавляемых после запятой;
§ можно настроить отделять или не отделять пробелом триады разрядов (сотни, сотни тысяч и т.п.);
§ можно настроить формат отображения отрицательных чисел:
§ черные с минусом;
§ красные без минуса;
§ черные с минусом и отступом (справа);
|
|
|
§ красные с минусом и отступом.
Внимание! Несмотря на то, что при изменении количества отображаемых знаков после запятой визуально число изменяется, в ячейке по прежнему хранится неокругленное число и операции будут осуществляться именно с ним. К примеру, пусть в ячейку было занесено число 1,23456. Пользователь оставил лишь 4 знака после запятой. В ячейке будет отображаться число 1,2346. Но при умножении на 100 000 в ячейке будет результат 123 456, а не 123 460! При расчетах пользуйтесь функцией ОКРУГЛ, если есть необходимость работать с округленным числом.
Формат «Денежный»
Те же настройки, что и в «Числовой» (кроме отключения разделения разрядов), плюс выбор денежной единицы. По умолчанию отображается денежная единица, указанная в настройках «Панель управления» —> «Язык и региональные стандарты» —> закладка «Региональные параметры».
Формат «Финансовый»
Те же настройки, что и в «Денежный» (кроме выбора формата отрицательных чисел: он всегда черные с минусом и отступом). Отличия от формата «Денежный» незначительные. Особенно их хорошо будет видно, если оформить ячейки сначала в формате «Денежный», выбрать денежный знак «$», ввести в ячейки отрицательные числа, увеличить ширину столбца и затем перевести их в формат «Финансовый». В ячейках появится отступ справа (как и у Денежного), но знаки «$» и «-» будут выровнены по левому краю. Очевидно, где-то принят такой стандарт оформления.
|
|
|
Формат «Дата»
Обычно сами даты вводятся в ячейки вручную (см. Автоматическое изменение формата ячейки после ввода данных), а затем в окне «Формат ячеек» выбирается нужный вид представления даты. Также можно сменить текущий формат представления даты на формат, принятый в других странах.
Формат «Время»
Обычно время вводятся в ячейки вручную (см. Автоматическое изменение формата ячейки после ввода данных), а затем в окне «Формат ячеек» выбирается нужный вид представления времени. Также можно сменить текущий формат представления времени на формат, принятый в других странах.
Формат «Процентный»
В формате «Процентный» все числа умножаются на 100 и добавляется знак «%». Также можно настроить количество десятичных разрядов после запятой.
Формат «Дробный»
В формате «Дробный» Excel пытается преобразовать десятичные дроби в обыкновенные. Например, «1,2» (одна целая, две десятых) будет преобразовано в «1 1/5» (одна целая, одна пятая).
|
|
|
Формат «Экспоненциальный»
Формат «Экспоненциальный» удобен для представления очень больших (расстояние до Солнца в метрах) или очень маленьких (масса атома водорода в килограммах) чисел. Например, число «299 792 458» (скорость света в метрах) в этом формате преобразуется в «3,E+08». Знак «+» здесь означает, что запятую нужно передвинуть вправо, а «08» — на какое количество разрядов. Также можно настроить количество знаков после запятой.
Формат «Текстовый»
Значения в ячейках, оформленных в этом формате, отображаются точно так же,как вводятся. Они обрабатываются как строки вне зависимости от их содержания. Например, если в ячейке, оформленной в формате «Текстовой», написать «1.2.3», Excel не будет пытаться преобразовать это в дату.
Ввод в ячейку 1-го символа « ' « (клавиша «Э» русской раскладки) автоматически приводит содержимое ячейки к текстовому формату. Символ « ' « на экране не отображается.
Формат «Дополнительный»
В выпадающем списке «Язык» выберите «Русский». В списке «Тип» появятся следующие варианты: «Почтовый индекс», «Индекс + 4», «Номер телефона», «Табельный номер». Попробуйте оформить ячейку типом «Номер телефона» и введите туда 10-значный номер. Думаю объяснять не надо. Для других языков могут отобразится другие варианты.
Пункт «(все форматы)»
В этом пункте можно создать свой формат (например, «шт», «кг.», «м/с» и т.п.), отсутствующий среди стандартных. Для этого используется специальный язык описания форматов. В текстовом поле сверху описываете его и нажимаете «Enter». Для удаления формата выделяете «нужный» формат и нажимаете кнопку «Удалить». Удалить встроенный формат невозможно. (Совет: форматы хранятся в рабочей книге; удаление ненужных форматов удобно завершать Отменой, чтоб не «испортить» формат текущей ячейки.)
Условное форматирование
ВНИМАНИЕ!!! Перед прочтением этой темы следует разобраться со ссылками, формулами и функциями. ПРИМЕЧАНИЕ: В Office 2007 значительно расширились возможности условного форматирования.
Имеется возможность указать Excel, чтобы при вводе данных, соответствующих определенному условию, он изменял оформление ячейки: заливку, границы, шрифт.
Сделать это можно с помощью окна «Условное форматирование», вызываемым так:
Главная – Стили: Условное форматирование…
В этом окне можно задать до 3-х способов автоматического оформления ячеек в зависимости от условия. Добавить новое условие можно, нажав кнопку «А также >>«. Нажав кнопку «Формат», можно настроить параметры шрифта, границы и заливки для ячеек, значения в которых удовлетворяют заданному условию. Значения в ячейках, не содержащих данных, Excel считает равным нулю.
Ниже показан результат применения настроек, указанных на рисунке выше, к выделенному диапазону.
Здесь первое условие «=» прописано для того, чтобы Excel пустые ячейки не оформлял как ячейки, содержащие «0».
ВНИМАНИЕ!!! Имеет значение порядок условий! Если условие «=» поместить после «=ОСТАТ(C14;2)=0», то пустые ячейки и содержащие «0» будут подсвечиваться одинаково.
В условном форматировании можно использовать значения ячеек в формулах, из в зависимости от результата вычисления формулы изменять оформление. На рисунке ниже показан пример различной подсветки ячеек с чётными числами, нечётными числами и пустых ячеек.
—-> 
Функция «ОСТАТ(число;делитель)» находит остаток от деления. В даных формулах использована относительная ссылка на ячейку «C14» (первую ячейку диапазона), по остальным ячейкам Excel «пробежится» сам. Если бы ссылка была абсолютной (что происходит по умолчанию, если выделять из окна «УФ»), это бы работало только для ячейки «C14».
ВНИМАНИЕ!!! Удаление данных из ячейки с помощью «Delete» не приводит к удаление условного форматирования! Удалить его можно либо из окна «Условное форматирование» кнопка «Удалить...», либо с помощью команды »Правка» —> «Очистить» —> «Форматы».
Маркер автозаполнения
В нижнем правом углу выделенной (активной) ячейки или выделенного диапазона ячеек можно видеть небольшой квадратик — маркер автозаполнения.
При наведении на него, указатель становится тонким крестиком, за который можно «схватиться» левой или правой кнопкой мыши и потащить вниз, вверх, вправо или влево. Это может использоваться в разных целях:
§ Копирование данных 1 в 1 в другие ячейки (чисел, текста, формул);
§ Создание арифметических и геометрических прогрессий;
§ Создание различных встроенных в Excel последовательностей (названия месяцев, дней недели, даты, время);
§ Продолжение пользовательских настраеваемых последовательностей;
§ Копирование оформления ячеек.
Если потянуть за маркер правой кнопкой, после ее отпускания откроется контекстное меню, в котором можно найти дополнительные команды для копирования оформления ячеек, создания прогрессий и последовательностей дат:
Если протянуть маркер автозаполнения через ячейки, в которых уже есть какие-то данные, данные будут перезаписаны.
Ссылки
Общие сведения
Ссылка в Microsoft Excel — адрес ячейки или связного диапазона ячеек. В каждом листе Excel 2007 16 384 (2 в 14 степени) столбцов и 1 048 576 (2 в 20 степени) строк. Адрес ячейки определяется пересечением столбца и строки, как в шахматах или морском бое, например: A1, C16. Адрес диапазона ячеек задается адресом верхней левой ячейки и нижней правой, например: A1:C5.
Виды представления ссылок
Есть два вида представления ссылок в Microsoft Excel:
§ Классический A1;
§ Стиль ссылок R1C1 (здесь R — row (строка), C — column (столбец)).
Включить стиль ссылок R1C1 можно в настройках «Сервис» —> «Параметры» —> закладка «Общие» —> галочка «Стиль ссылок R1C1»
Если включен классический вид и в ячейке с адресом A1 находится формула »=B3» (B3 — относительная ссылка), то после переключения в вид R1C1 она примет вид «=R[2]C[1]». В режиме R1C1 в квадратных скобках указывается относительное смещение. В скобках после R указано смещение в строках (row) — две строки вниз (вниз, потому что число положительное, если бы стояло «R[-2]» — было бы вверх). В скобках после C указано смещение в столбцах — 1 столбец вправо (если бы было C[-1] — влево). В общем случае направления вниз и вправо считаются положительными, вверх и влево — отрицательными.
Если включен классический вид и в ячейке с адресом A1 находится формула «=$B$3» ($B$3 — абсолютная ссылка), то после переключения в вид R1C1 она примет вид «=R3C2». Квадратных скобок нет, то есть указано не относительно смещение по отношению к положению формула, а абсолютное смещение по отношению к всему листу (вспомните абсолютную и относительную системы координат).
Типы ссылок (типы адресации)
Ссылки в Excel бывают 3-х типов:
§ Относительные ссылки (пример: A1);
§ Абсолютные ссылки (пример: $A$1);
§ Смешанные ссылки (пример: $A1 или A$1, они наполовину относительные, наполовину абсолютные).
Знак $ здесь никакого отношения к денежным единицам не имеет, это лишь способ указать Excel тип ссылки. Различия между разными типами ссылок можно увидеть, если потянуть за маркер автозаполнения активной ячейки или диапазона ячеек, содержащих формулу со ссылками.
Относительные ссылки
Если вы ставите в какой то ячейке знак «=«, затем щелкаете левой кнопкой мыши на какой то ячейке, Excel подставляет после «=« относительную ссылку на эту ячейку. Эта ссылка «запоминает», на каком расстоянии (в строках и столбцах) Вы щелкнули ОТНОСИТЕЛЬНО положения ячейки, где поставили «=« (смещение в строках и столбцах). Например, вы щелкнули на ячейку 3-мя столбцами левее и на 2 строки выше. Если после нажатия Enter потянуть вниз за маркер автозаполнения, эта формула скопируется во все ячейки, через которые мы протянули. И в каждой ячейке эта ссылка будет указывать на ячейку, расположенную на 3 столбца влево и 2 строки вверх ОТНОСИТЕЛЬНО положения ссылки. Это можно проверить, дважды щелкнув на одной из скопированных формул, или выделив ее, и нажав F2. Для лучшего понимания вспомните, как ходит шахматный конь. Он ходит буквой «Г» и из центра доски бьёт 8 клеток. «Упростим» немного правило хода коня: представим, что он может ходить только одной буквой «Г» — 2 клетки вперед и одну вправо. На какую бы клетку доски мы не поставим коня, каждый раз он ОТНОСИТЕЛЬНО своего положения отсчитывает смещение в строках и столбцах — 2 строки вверх и один столбец влево. Точно таким же образом работают относительные ссылки, только правило их «хода» задает пользователь. Каждый раз, когда мы тянем за маркер автозаполнения формула, содержащую относительные ссылки, Excel пересчитывает адреса всех относительных ссылок в ней в соответствии с их «правилом хода» (у каждой относительной ссылки в формуле может быть свое «правило»).
Абсолютные ссылки
Как было сказано выше, если потянуть за маркер автозаполнения формулу, содержащую относительные ссылки, Excel пересчитает их адреса. Если же в формуле присутствуют абсолютные ссылки, их адрес останется неизменным. Проще говоря — абсолютная ссылка всегда указывают на одну и ту же ячейку.
Чтобы сделать относительную ссылку абсолютной, достаточно поставить знак «$» перед буквой столбца и адресом строки, например $A$1. Более быстрый способ — выделить относительную ссылку и нажать один раз клавишу «F4», при этом Excel сам проставит знак «$». Если второй раз нажать «F4», ссылка станет смешанной такого типа A$1, если третий раз — такого $A1, если в четвертый раз — ссылка опять станет относительной. И так по кругу.
Смешанные ссылки
Смешанные ссылки являются наполовину абсолютными и наполовину относительными. Знак доллара в них стоит или перед буквой столбца или перед номером строки. Это самый сложный для понимания тип ссылки. Например, в ячейке записана формула »=A$1». Ссылка A$1 относительная по столбцу A и абсолютная по строке 1. Если мы потянем замаркер автозаполнения эту формулу вниз или вверх, то ссылки во всех скопированных формулах будут указывать на ячейку A1, то есть будет вести себя как абсолютные. Однако, если потянем вправо или влево — ссылки будет вести себя как относительные, то есть Excel будет пересчитывать ее адрес. Таким образом, формулы, созданные автозаполнением, будут использовать один и тот же номер строки ($1), но изменится номер столбца (A, B, C...).
Именованные ячейки
Для упрощения работы с ячейками или диапазоном ячеек можно дать ей/им имя, и затем обращаться к ячейке или диапазону не по его адресу, а по символьному имени. Именованные ячейки можно использовать везде, где можно использовать то значение, на которое указывает ссылка.
Примечание! Именованные ячейки по умолчанию являются абсолютными ссылками.
Для создания именованной ячейки нужно выделить нужную ячейку или диапазон, затем щелкнуть в текстовое поле (см. рис. ниже) ввести свое имя и нажать Enter. Можно использовать русские имена. Имя не может содержать пробелов и начинаться с цифры. Именованная ячейка может ссылаться на несвязный диапазон ячеек (выделенный с «Ctrl»).
Для вставки именованной ячейки можно воспользоваться кнопкой со стрелкой вниз:
или нажать клавишу «F3», откроется следующее окно:
Пример использования: »=СУММ(tablica_1);»
Для того что бы убрать имя именованной ячейки (например: чтобы присвоить другой ячейке это имя) - Вставка/имя/присвоить/удалить
Формулы в Microsoft Excel
Общие сведения
Excel - программируемый табличный калькулятор. Все расчеты в Excel выполняют формулы. Формулой Excel считает все, что начинается со знака «=«. Если в ячейке написать просто «1+1», Excel не будет вычислять это выражение. Однако, если написать «=1+1» и нажать Enter, в ячейке появится результат вычисления выражения - число 2. После нажатия Enter формула не пропадает, ее можно увидеть снова, если сделать двойной щелчок по ячейке, или если выделить ее и нажать F2 или просто нажать Ctrl+Апостроф. Также ее можно увидеть в панели инструментов «Строка формул», если опять же выделить ячейку. После двойного щелчка, нажатия F2 или после щелчка в строке формул, можно изменить формулу, и для завершения нажать клавишу Enter.
В формуле можно использовать различные типы операторов (арифметические и т. п.), текст, ссылки на ячейку или диапазон ячеек, круглые скобки, именованные диапазоны. Естественно, в формулах соблюдается приоритет выполнения операций (умножение выполняется раньше сложения и т. п.). Для изменения порядка выполнения операций используются круглые скобки.
Дата добавления: 2018-04-05; просмотров: 750; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!