Главная | Обратная связь | Поможем написать вашу работу!

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

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

1. Выделим блок D2:D5. В этом блоке активна ячейка D2.

2. Наберем знак равенства =.

3. Нажмем функциональную клавишу F3. Появится диалоговое окно Вставка имени. выберем имя "прих", щелкнем ОК. Наберем знак минус -. Вновь нажмем клавишу F3 и в диалоговом окне выберем имя "расх". Щелкнем ОК. Формула примет вид =прих-расх.

4. Нажмем сочетание клавиш Ctrl+Shift+Enter. Во всех ячейках блока D2:D5 появится формула {=прих-расх}. Появившиеся фигурные скобки говорят о том, что мы создали табличную формулу. Эти скобки нельзя набрать вручную – в таком случае формула будет воспринята как текст.

На третьем шаге мы выбирали имя из списка имен. Можно было ввести имя непосредственно с клавиатуры, но предложенный подход проще и нет риска ошибиться в имени. В конце набора формулы мы нажимали не Enter, как ранее, а сочетание Ctrl+Shift+Enter. Это важно: если бы мы не применяли сочетание клавиш, формула была бы введена только в активную ячейку выделенного блока (D2).

Скопируем блок ячеек А1:С5 в А11:С15. Ввод табличной формулы без имен выполняется так. Выделим блок D12:D15. В этом блоке активной ячейкой является D12. наберем знак равенства =. Выделим блок В12:В15, наберем знак минус -, выделим блок С12:С15, нажмем сочетание клавиш Ctrl+Shift+Enter. Во всех ячейках блока появится формула {=B12:B15-C12:C15}. Таким образом в блоках (А1:D5 и А11:D15) находятся две идентичные таблицы.

Для справки: после ПЗ №5 в книге должны быть листы: Данные, График, 13пт, Титул, ТабУмн, Вклады, СанДень, Синус, ТаблФормулы.

ПЗ-6. Текстовые функции. Дата прописью. Функции даты и времени.

Текстовые функции.

Система Excel-2010 содержит 24 функции в категории Текстовые (практически столько же было и ранее). Среди них операции объединения в строку данных разного типа (можно использовать символ & или функцию СЦЕПИТЬ), функции преобразования типа (ЗНАЧЕН, КОДСИМВ, СИМВОЛ, ТЕКСТ), поиска подстроки в строке (ПОИСК, НАЙТИ), замены части строки на другую (ЗАМЕНИТЬ, ПОДСТАВИТЬ, СЖПРОБЕЛЫ), выделения подстроки из строки (ЛЕВСИМВ, ПРАВСИМВ, ПСТР), замены регистра букв (ПРОПИСН, ПРОПНАЧ, СТРОЧН) и пр. Описания функций и их аргументов можно увидеть в диалоговом окне задания аргументов функции. Если Вы знаете имя функции, можно воспользоваться всплывающим списком, который появится, если после символа равно (=) написать первые буквы имени функции (рис.слева).

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

1. В ячейку можно ввести число, формулу, текст. Число и формулу можно превратить в текстовую строку следующим образом: нажать функциональную клавишу F2 (редактирование), поставить курсор в крайнюю левую позицию (клавиша Home) и ввести одиночную кавычку (она расположена на клавише с буквой "Э" в английской раскладке клавиатуры). Например, введите в Е1 число 12 (число выровнено по правому краю), превратите его в текстовую строку: '12 (число будет выровнено по левому краю). Удалите данные.

2. Введите в ячейку А1 - "Иванов", в ячейку В1 — "Иван", в ячейку С1 — "Иванович" (кавычки не вводить!). В ячейке А2 нужно получить строку "Иванов Иван Иванович". Для этого введем в А2 формулу: =А1&" "&В1&" "&С1. Знак & (амперсанд) — символ операции склейки (конкатенации) строк. Обратите внимание, что между строками, взятыми из ячеек, вставлены пробелы. Введите в ячейку В1 имя "Николай". Изменится и значение в ячейке А2.

Вместо использования амперсандов можно применять текстовую функцию СЦЕПИТЬ. Она содержит до 30 аргументов – текстовых значений, которые можно объединить в одну строку.

В последней версии Excel: можно сцепить до 255 элементов (объединить в 1 строку).

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

3. Отредактируйте содержимое ячейки А2, вставив несколько дополнительных пробелов до, после и между словами. В ячейку A3 введите формулу =СЖПРОБЕЛЫ(А2). Несколько пробелов преобразуются в один, начальные и конечные пробелы исчезают. Этот прием может быть полезен при импортировании данных из какой-либо внешней базы данных. При этом часто приходится удалять лишние пробелы.

4. Разместим в ячейках А1:А5 фамилии, имена и отчества работников - все в одной ячейке. Требуется изменить эти данные на фамилии и инициалы. Такая задача производится в два этапа (см.рисунок далее).

На первом этапе разнесем исходный текст на несколько отдельных ячеек. Выделим ячейку А1, выберем меню Данные - Tекст по столбцам. Начнет работу Мастер текстов. На первом шаге укажем, что текст "с разделителями", на втором шаге - что разделителем является пробел, а начальная ячейка - В1. Нажмем "Готово". В результате текст будет разбит на три ячейки: В1, С1, D1. То же самое необходимо сделать для других ячеек: А2:А5, для них указать соответствующие начальные ячейки. Получим части ФИО, каждую отдельно в соответствующих ячейках.

На втором этапе из различных частей ФИО сконструируем фамилию и инициалы. Для этого введем в ячейку А11 формулу: =B1&" “&ЛЕВСИМВ(C1)&"."&ЛЕВСИМВ(D1)&"." и размножим ее вниз на соответствующее число ячеек (диапазон А11:А15). Получим фамилии и инициалы, соответствующие исходным значениям ФИО в ячейках А1:А5..

5. Вычислим сумму цифр трехзначного целого числа с использованием текстовых функций (см.рисунок). Надо сначала перевести число (J13) в строку (J12) функцией ТЕКСТ(J13;000), а затем сложить значения на 1, 2 и 3 местах строки, введя формулу в ячейку: =ПСТР(J12;1;1)+ПСТР(J12;2;1)+ПСТР(J12;3;1).

Функция ПСТР выбирает из ячейки J12 по 1 символу с указанного во втором аргументе места (на рисунке строка не видна – она белого цвета). В итоге в ячейке J14 получим сумму цифр трехзначного числа, введенного в ячейку J13.

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

Сумма строковых значений, заданных явно, если это цифры - дает числовой результат! Например, если ввести в ячейку формулу: ="11"+"2" (сумма двух текстовых значений), то, нажав Enter, получим значение 13, причем это будет число (!) (смещено к правой границе ячейки).

Для преобразования данных разного типа при объединении их в текстовую строку следует пользоваться функцией ТЕКСТ(<значение>; <формат>). Пример использования показан на рисунке.

ФИО	Дата рождения
Петров Б.В.	10.10.1991	Петров Б.В. родился в 1991 году
		'=A2&" родился в "&ТЕКСТ(B2;"ГГГГ")&" году"

02.11.2013		Сегодня 02.11.2013, суббота
'=СЕГОДНЯ()		'="Сегодня "&ТЕКСТ(A5;"ДД.ММ.ГГГГ")&", "&ТЕКСТ(A5;"ДДДД")
02.11.2013 23:24
'=ТДАТА()		Сейчас 23 час.24 мин.
		="Сейчас "&ТЕКСТ(A7;"чч")&" час."&ТЕКСТ(A7;"мм")&" мин."
Использование функции ТЕКСТ()

Дата прописью

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

Итак, задача состоит в том, чтобы дату, например, 10.10.2010 представить в виде текста: " от 10 октября 2010 года ". Технология решения этой задачи состоит в том, что сначала нужно разбить дату на составляющие (день, месяц, год), а затем все компоненты соединить в текстовой строке.

Дату введем в ячейку А1. В ячейках А2, А3 и А4 с помощью соответствующих текстовых функций определим день, год и месяц исходной даты. Для дня и года никаких затруднений нет: вызывается соответствующая функция, в качестве аргумента вводится дата из ячейки А1, а функция определяет искомое значение.

Для определения месяца: сначала с помощью функции МЕСЯЦ() определяем номер месяца (число от 1 до 12), а затем переводим число в слово с помощью логической функции ЕСЛИ(), используя в качестве условия равенство номера месяца конкретной цифре. Если бы надо было проверить всего 8 условий, можно было бы обойтись "вложением" функций ЕСЛИ при невыполнении первого условия (в прошлых версиях Excel допускалось только 8 вложений). Однако нам надо проверить 12 значений на соответствие их равенства заданному, поэтому применим другой способ использования логической функции. Он состоит в том, что при невыполнении заданного условия мы переходим в другую ячейку и там проверяем следующее логическое выражение - до тех пор, пока не будет использована вся цепочка возможных значений проверяемого аргумента.

Формулы для определения имени месяца строим в ячейках А5:А15 (см.рисунок). Для дат, которые имеют одну цифру дня, предусмотрим на-писание нуля перед этой цифрой (ячейка А16).

Результат вычислений формируется в ячейке А17 путем набора указанной на рисунке формулы. Вместо нее можно было бы использовать функцию СЦЕПИТЬ, указав в качестве аргументов приведенные в формуле значения.

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

В новой версии Excel можно было бы обойтись одной формулой, вложив все условия внутри функции ЕСЛИ() (допускается до 64 вложений функций).

Промежуточные вычисления (выделив строки со 2 по 16) можно скрыть с помощью контекстного меню или через меню ГлавнаяàгруппаЯчейкиàФорматàСкрыть или отобразить.

Дату в числовом формате в ячейку А1 можно импортировать с другого (например, с титульного) листа или задавать с помощью функции СЕГОДНЯ(), а текстовое выражение из ячейки А17 использовать на другом листе (в шаблоне документа).

⇐ Предыдущая 5 6 7 8 91011 12 13 14 Следующая ⇒