Ввод и редактирование табличных формул.

Создать новый лист с названием ТаблФормулы. В таблице ввести данные, как показано на рисунке. Для расчета дохода можно ввести в ячейку D2 формулу =B2-C2, а затем скопировать (размножить) ее в ячейки D3:D5. В этих ячейках появятся формулы =B3-C3 и т.д. Фактически этими формулами из вектор-столбца В2:В5 вычитается вектор-столбец С2:С5. Вычитание векторов можно сделать более простым и наглядным.

Для наглядности создадим имена векторам (как в предыдущем параграфе – только там мы давали имена отдельным ячейкам). Выделим диапазон В1:С5 и зададим команду: ФормулыàСоздать из выделенногоàв строке выше.

Диапазон В2:В5 получит имя " прих ", а диапазон С2:С5 - имя " расх ". Эти имена теперь находятся в выпадающем списке поля имени.

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

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

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 понимается комбинация букв, цифр и пробелов. При наборе текста в ячейке, если он не помещается в ее окошке, текст может разместиться как бы поверх соседних ячеек справа, перекрывая их и создавая иллюзию расположения сразу в нескольких ячейках. На самом деле текст всегда остается в своей ячейке, что наглядно отображает окошко ввода текста на строке формул.

Любой информации, введенной в ячейку, можно задать специальный текстовый формат, превращая ее в текст. Например, чтобы использовать числа не для расчетов, а в качестве номера изделия или собственного имени (ИНН и т. д.). Текст можно форматировать в процессе работы над ним.

Форматирование шрифта – это комплекс операций, с помощью которых устанавливается нужное отображение текста. Текст можно форматировать как посимвольно, так и выбранными фрагментами (слово, несколько слов, строка и т. д.) Шрифт – это общий графический рисунок букв и символов, входящих в одну стилистическую и композиционную систему.

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

Создадим новый лист, назовем его Текст.

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

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

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

Часто бывает необходимо превратить содержимое ячейки (напр., А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) формулой =ТЕКСТ(;000), а затем сложить значения на 1, 2 и 3 местах строки, введя формулу в ячейку J14:

=ПСТР(J12;1;1)+ПСТР(J12;2;1)+ПСТР(J12;3;1).

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

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

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

 

 

ФИО	Дата рождения
Петров Б.В.	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 2010 можно было бы обойтись одной формулой, вложив все условия внутри функции ЕСЛИ() (допускается до 64 вложений функций).

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

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