72408

Работа с текстовыми данными

Лабораторная работа

Информатика, кибернетика и программирование

Цель работы: изучить основные сведения по работе с текстовыми данными, научиться обрабатывать текст. Уметь связывать данные в разных таблицах, освоить работу логических функций. Изучить пользовательские форматы данных. Научиться использовать средство Условное форматирование для выделения диапазона данных.

Русский

2014-11-22

180.5 KB

2 чел.

Лабораторная работа № 3.

Работа с текстовыми данными.

Цель работы: изучить основные сведения по работе с текстовыми данными, научиться обрабатывать текст. Уметь связывать данные в разных таблицах, освоить работу логических функций. Изучить пользовательские форматы данных. Научиться использовать средство Условное форматирование для выделения диапазона данных. Освоить работу с поименованными областями. Изучить способы сокрытия и защиты данных.

Задание (теоретическая часть):

  1.  Ознакомиться с текстовым форматом ячеек.
  2.  Используя лекционный материал, любые печатные  издания и пособия по EXCEL, а также справочную систему самого EXCEL, изучить назначение и действие текстовых функций.  Выписать в лекционную тетрадь (распечатать) все функции для работы с текстом - 23 штуки: название функции, список аргументов и получаемый результат. Привести примеры работы этих функций.
  3.  Изучить правила создания числовых пользовательских форматов и коды для их построения.
  4.  Изучить правила создания имен для группы ячеек и способы использования этих имен.
  5.  Изучить возможности EXCEL для связывания данных в разных таблицах, а также работу функции ВПР 
  6.  Ознакомиться с правилами и возможностями сортировки числовых и текстовых данных.
  7.  Изучить назначение и действие логических функций: И, ИЛИ, ЕСЛИ. Выписать в лекционную тетрадь правила записи логических функций и примеры их использования.
  8.  Изучить способы сокрытия и защиты данных. Освоить защиту одной ячейки, рабочего листа и книги целиком. Ознакомиться со способом сокрытия формул в таблице.

Задание (практическая часть: 4 таблицы – 1 справочник и 3 связанные с ним таблицы, 2 графика):

  1.  На первом листе рабочей книги создать справочник № 1 в соответствии с предложенным заданием по номеру в списке.

Общие требования к справочнику № 1.

В первом столбце справочника должны быть размещены уникальные данные в соответствии с представленным в задании кодом (Ц – цифра в коде). Для этого необходимо использовать пользовательский формат данных. Для всего справочника следует создать поименованные области (для правильной работы функции ВПР). Количество строк в справочнике должно быть не менее 7.

Задание для справочника № 1.

№ вар

1-я колонка

2 колонка

Название колонки

с кодом

Формат кода

1

Код менеджера

Ц  ЦЦ (Ц)

Обслуживающий менеджер

2

№ фирмы

 Ц/ЦЦ  Ц

Наименование фирмы-поставщика

3

Код пряжи

ЦЦ(тк. ЦЦ))

Название пряжи

4

Индекс

: Ц—ЦЦ(Ц)

Полное наименование ткацкого оборудования

5

№ фирмы

ЦЦ—ЦЦ

Фирма - покупатель

6

Артикул

ЦЦ/ЦЦ)

Наименование трикотажа

7

Код модели

(Ц) ЦЦ № Ц

Название модели

8

Код фасона

Ц  /ЦЦ/  Ц

Фасон обуви

9

Индекс ткани

Ц/Ц - ЦЦ

Категория ткани

10

Артикул ткани

Ц-ЦЦ/Ц

Название ткани

11

Код

ЦЦ{Ц}  (Ц)

Состав пряжи

12

Личный номер мастера

 [Ц]  ЦЦЦ

Старший мастер в смене

13

Код станка

Ц—ЦЦ—Ц

Название станка

14

Код фирмы

: Ц : Ц х ЦЦ

Наименование фирмы-посредника

15

Код цвета

ЦЦЦ/от.ЦЦ

Наименование цвета пряжи

16

Артикул

Ар. Ц  (ЦЦЦ)

Наименование кожи

17

Код изделия

 (ЦЦ)  Ц/Ц

Название изделия

18

Личный код

ЦЦ [ ЦЦ ]

Контролер - браковщик

19

Код по каталогу

Ц — ЦЦ — Ц)

Модель по каталогу

20

Индекс

Ин: ЦЦ { ЦЦ } 

Название красителя

  1.  Наложить защиту на данные справочника № 1 таким образом, что бы никто кроме автора этой таблицы не смог внести никакие изменения в таблицу.

Задание для таблицы № 2.

№ п/п

Текст

Коды

Данные из справ. № 1 (функция ВПР)

Число

Число

Расчетный столбец и

формула для его расчетов

1

Ф

И

О

Код менеджера:

Обслуживающий менеджер

Количество изделий

Стоимость изделия

Стоимость всей партии

Количество * стоимость изделия

+5% для менеджера Иванова,

+7% для остальных менеджеров

2

Ф

И

О

№ фирмы

Фирма-поставщик

Стоимость заказа

Сумма предоплаты

Остаток суммы

за заказ

Стоимость заказа + предоплата

+500 руб для предоплаты > 50%, +100 руб. для всех остальных

3

Ф

И

О

Код пряжи

Название пряжи

Количество бобин

Вес бобины

Стоимость

партии пряжи

Кол-во*вес

*250 руб, если вес < 1 кг,

*300 руб – если вес >= 1 кг.

4

Ф

И

О

Индекс

оборудования

Наименование оборудования

Изделий за смену

Количество

смен за месяц

Всего изделий

за месяц

Изделия*смены

+3% если > 15 смен

+5% если <= 15 смен

5

Ф

И

О

№ фирмы

Фирма - покупатель

Стоимость фурнитуры

Цена ткани

Стоимость

заказа

Цена ткани + фурнитура

+100 руб, если фурнитура дешевле ткани, прочее:  ткань + фурнитура

6

Ф

И

О

Артикул трикотажа

Наименование трикотажа

Стоимость пряжи

Стоимость изготовления

Стоимость изделия

Пряжа+изготовление

+200 руб при ст-ти изгот-ия < 350 руб, +300 руб  в остальных случаях

7

Ф

И

О

Код модели

Название модели

Вес пряжи в изделии

Стоимость 1 кг пряжи

Стоимость изделия

Вес*стоимость

+30% для веса пряжи < 400 гр.,

+50% остальное

8

Ф

И

О

Код фасона

Фасон обуви

Стоимость раскроя

Количество пар в заказе

Стоимость раскроя партии

Стоимость* количество

+2000 руб для сапог,

+500 руб – все остальное

9

Ф

И

О

Индекс ткани

Состав ткани

Метров в рулоне

Количество рулонов

Общий метраж ткани на складе

Метраж*рулоны

+1метр, если < 3 рулонов,

+5 метров - все остальное

10

Ф

И

О

Артикул ткани

Название ткани

Метраж на 1 изделие

Стоимость 1 метра ткани

Стоимость ткани на 1 изделие

Метраж*стоимость

+500 руб., если шелк,

+300 руб  все остальное.

11

Ф

И

О

Код состава

Состав пряжи

Количество мотков

Стоимость 1-го мотка

Общая стоимость партии пряжи

Количество*стоимость

+200 руб., если < 10 мотков,

+400 руб. – все остальное

12

Ф

И

О

Личный номер мастера

Старший мастер в смене

Вывязано изделий

Вес одного изделия

Расход пряжи  за смену

Изделия* вес

+3%, если ст. мастер – Иванов,

+5% для остальных мастеров

13

Ф

И

О

Код станка

Название станка

Стоимость станка

Количество станков

Стоимость оборуд-ия

Стоимость* кол-во

+10%, если кол-во станков< 3,

+5% - все остальные

14

Ф

И

О

Код фирмы

Наименование фирмы-посредника

Изделий в заказе

Стоимость изделия

Стоимость

заказа

Количество*стоимость

+10% для фирмы “Наши деньги”, +30%- все остальные фирмы.

15

Ф

И

О

Код цвета

Наименование цвета пряжи

Стоимость мотка

Количество мотков

Стоимость всей пряжи в партии

< 50 мотков  - кол-во*(стоим.+10 руб.), прочие – кол-во*(стоимость+5 руб).

16

Ф

И

О

Артикул кожи

Наименование кожи

Объем в м2  партии

Стоимость 1-го м2 кожи

Стоимость поставки кожи

Объем*стоимость

+10% для замши,

+5% - все остальное

17

Ф

И

О

Код изделия

Название изделия

Вес изделия в граммах

Стоимость материала  за 1 кг

Себестоимость изделия

(Вес+20%)* стоимость – для свитеров

(Вес+10%)* стоимость – все остальное

18

Ф

И

О

Личный код

Контролер - браковщик

Количество брака

Цена 1 упаковки

Стоимость отбракованного

Количество*(цену+10 руб.), для Петрова, остальные –Количество*(цену+15 руб.)

19

Ф

И

О

Код по каталогу

Название модели по каталогу

Стоимость модели

Стоимость доставки

Стоимость

заказа

Стоимость модели+доставка

+10% если цена мод.< цены дост.

+5% если цена мод.>= цены дост.

20

Ф

И

О

Индекс красителя

Название красителя

Количество упаковок

Вес упаковки

Стоимость  партии

., Для краситель акриловый –Вес*количество*600 руб,

прочее – Вес*количество*800 руб

  1.  На втором листе рабочей книги создать развернутую таблицу № 2 в соответствии с заданием.

Общие требования к таблице № 2. Фамилии, имена и отчества следует указывать полностью. В качестве исходных данных можно взять список своей группы. Коды в столбец № 5 следует вносить вручную. Столбцы № 5 и 6 (данные из справочника № 1) должны быть связаны со справочником № 1 при помощи функции ВПР таким образом, что бы при изменении кода в столбце № 5, автоматически изменялись бы и данные в столбце № 6. Расчетный столбец таблицы должен заполняться при помощи функции ЕСЛИ. Данные для двух столбцов с числовыми данными должны быть представлены при помощи числового пользовательского формата, содержащего обязательный для каждой ячейки текст, например: 10 штук, 256 рублей, 34 $USA (числа и формат ячеек придумать самостоятельно в соответствии с заданием).

Количество строк в таблице должно быть не менее 20. Данные из справочника № 1 должны повторяться для нескольких строк таблицы № 2.

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

Общие требования к таблице № 3. Три столбца с фамилией именем и отчеством заменить на один столбец в формате Фамилия И.О. Этот столбец должен быть связан с данными из таблицы № 2 таким образом, что бы любые изменения в анкетных данных приводили бы к автоматическому изменению данных в таблице № 3. Удалить из таблицы все столбцы с исходными числовыми данными, оставив один лишь расчетный столбец. Отсортировать данные в таблице по расчетному столбцу.

  1.  На основании таблицы № 3 построить круговую диаграмму на отдельном листе, на диаграмме обязательно вывести фамилии с инициалами и расчетные данные.

Задание на Условное форматирование для таблицы № 2.


№ вар

Условие № 1

Условие № 2

1

наличие в фамилии буквы а или и

имя длиннее отчества

2

фамилия оканчивается на -ев

имя и отчество начинаются с одной буквы

3

фамилия короче отчества

отчество оканчивается на -еевич

4

в фамилии есть буква с

имя длиннее фамилии

5

длина фамилии более 9 символов

фамилия содержит две буквы е

6

наличие в фамилии буквы о

длина отчества более 10 символов

7

имя и фамилия начинаются с одной буквы

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

8

фамилия начинается с гласной буквы

длина имени менее 7 символов

9

в фамилии есть буква ш

имя начинается с гласной буквы

10

фамилия оканчивается на -ов

фамилия короче имени

11

в фамилии есть буква т

имя заканчивается на -ина

12

первая буква  совпадает с последней

отчество длиннее имени на две буквы

13

фамилия начинается с буквы А,Б или В

длина фамилии более 8 символов

14

в фамилии есть буква р

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

15

фамилия оканчивается на -на

длина отчества менее 10 символов

16

фамилия начинается с буквы М,Н или О

отчество короче фамилии

17

длина фамилии более 7 символов

отчество начинается с гласной буквы

18

в фамилии есть буква л

первые буквы фамилии и отчества одинаковы

19

фамилия оканчивается на -ин

отчество содержит две буквы е

20

фамилия начинается с буквы Р,С или Т

длина имени более 8 символов

  1.  Используя данные справочника № 1 и таблицы № 2, на пятом листе создать таблицу № 4 (смотри задание ниже).

Общие требования к таблице № 4. При вычислениях в таблице № 4 должны использоваться функции СЧЕТЕСЛИ и СУММЕСЛИ. При изменении данных в исходных таблицах, должны изменяться данные и в таблице № 4. Количество строк в таблице № 4 должно совпадать с количеством строк в справочнике № 1.

  1.  По данным таблицы № 4 создать гистограмму на отдельном листе. Вывести на гистограмму текстовые данные (из столбца № 1) и подписи данных (из столбца № 2).
  2.  Общие требования к внешнему виду таблиц и диаграмм взять из лабораторной работы № 2. Дать созданным таблицам названия, соответствующие хранимым в них данным. Ячейки таблицы должны иметь пользовательский формат, соответствующий формату хранимой в ней информации. Все таблицы должны быть аккуратно и грамотно оформлены и радовать глаз, как самого студента, так и преподавателя.

Задание для таблицы № 4.

№ вар

Содержание столбцов

1

Обслуживающий менеджер

Количество всех партий для менеджера

Стоимость всех партий для менеджера

2

Фирма-поставщик

Количество заказов для фирмы

Стоимость заказов для фирмы

3

Код пряжи

Количество  партии  этой пряжи

Общий вес партии  этой пряжи

4

Наименование оборудования

Всего смен за  месяц на этом оборудовании

Всего изделий за месяц на этом оборудовании

5

Фирма - покупатель

Количество заказов для этой  для фирмы

Стоимость фурнитуры в заказах для фирмы

6

Артикул трикотажа

Количество заказов по этому артикулу

Количество изделий по артикулу

7

Название модели

Общее количество заказов по изделию

Общее количество  изделий

8

Код фасона обуви

Общее количество заказов по коду обуви

Общее количество пар обуви

9

Индекс ткани

Количество рулонов ткани на складе

Общий метраж ткани на складе

10

Артикул ткани

Количество рассчитанных изделий

Стоимость рассчитанных изделий

11

Состав пряжи

Количество мотков пряжи на складе

Стоимость пряжи на складе

12

Старший мастер в смене

Общее количество смен

Общее количество вывязанных изделий

13

Код станка

Общее количество станков в цеху

Стоимость  станков в цеху

14

Наименование фирмы-посредника

Количество заказов через эту фирму

Стоимость заказов через эту фирму

15

Наименование цвета пряжи

Количество заказов этой пряжи

Стоимость пряжи

16

Наименование кожи

Количество  партии кожи

Общая стоимость партии кожи

17

Код изделия

Количество заказов этого  изделия

Общая себестомость изделий

18

Контролер - браковщик

Количество отбракованого

Стоимость отбракованого

19

Название модели по каталогу

Количество  заказанных моделей

Общая стоимость заказанных моделей

20

Название красителя

Количество упаковок  заказанного красителя

Общий вес заказанного красителя

КОНТРОЛЬНЫЕ ВОПРОСЫ (пока не все!!!):

  1.  Как обрабатывается текст в EXCEL?
  2.  В каком формате следует хранить текстовые данные и почему?
  3.  Чем отличается текстовый формат от общего?
  4.  Какие символы являются признаками текстовых данных в EXCEL?
  5.  Чем отличается справочник от обычной таблицы?
  6.  Как присвоить группе ячеек имя? Зачем это нужно?
  7.  Как посмотреть уже существующие имена для диапазонов ячеек?
  8.  Как вставить в формулу или функцию имя диапазона?
  9.  Зачем нужны пользовательские форматы?
  10.  Для каких данных применяется пользовательский формат?
  11.  Какие коды используются в пользовательском формате?
  12.  Зачем нужна сортировка данных?
  13.  Как сравниваются текстовые данные в EXCEL при сравнении и при сортировке?
  14.  Различаются ли при сортировке заглавные и прописные английские буквы?
  15.  Различаются ли при сортировке заглавные и прописные русские буквы?
  16.  Зависит ли сортировка текстовых данных от выбранного шрифта, его начертания и жирности?
  17.  Как подсчитать длину строки текста в ячейке?
  18.  Как удалить из строки текста все ненужные пробелы?
  19.  Каким образом можно объединить несколько текстовых данных из разных ячеек в одну?
  20.  Можно ли перевести текст, записанный заглавными русскими буквами в прописные и наоборот?
  21.  Как вырезать и скопировать в другую ячейку первое слово из строки теста?
  22.  Как подсчитать количество вхождение в строку буквы ”Л”?
  23.  Как оставить от исходного слова одну первую букву и добавить к ней точку?
  24.  Зачем нужна защита ячеек?
  25.  Какие виды защиты (степени защиты) существуют в EXCEL?
  26.  Как защитить одну ячейку в таблице? Как защитить всю таблицу, весь лист, всю книгу?
  27.  А как потом снять эту защиту?
  28.  Зачем нужно скрывать формулы в таблице?
  29.  Каким образом можно спрятать формулы в таблице?
  30.  Как можно вернуть спрятанные формулы обратно?
  31.  Что можно сделать, если на книгу была наложена защита, а пароль был утерян?
  32.  Что такое логические функции?
  33.  Какие значения могут принимать логические функции?
  34.  Какие логические функции используются в EXCEL?
  35.  Как объединить два различных условия в одно, что бы работало хотя бы одно из них?
  36.  Как объединить два различных условия в одно, что бы проверялось выполнение сразу двух условий?
  37.  Как работает логическая функция ЕСЛИ?
  38.  Как быстро легко и правильно написать функцию ЕСЛИ, вложенную в другую функцию ЕСЛИ?
  39.  Зачем нужно связывать между собой данные из разных таблиц?
  40.  Каким образом связываются данные, находящиеся в различных таблицах?
  41.  Как вставить в ячейку исходные данные, расположенные в другой таблице. А в другой книге?
  42.  Зачем нужны функции ВПР и ГПР? Как они работают?
  43.  Зачем в функции ВПР нужен последний параметр – ложь или истина?
  44.  А что будет, если этот последний параметр в функции ВПР не указать?
  45.  Зачем нужны функции СУММЕСЛИ и СЧЕТЕСЛИ?
  46.  Как работают функции СУММЕСЛИ и СЧЕТЕСЛИ?
  47.  Зачем нужна опция формула в Условном форматировании?
  48.  Как при Условном форматировании сослаться на содержимое (значение) те той ячейки, на которую накладывается это условное форматирование, а на данные из другой ячейке (или нескольких ячеек)?
  49.  Как использовать средство Условное форматирование для выделения не одной ячейки, а нескольких ячеек в строке или целой строки в таблице?
  50.  Как скрыть столбец или строку? А как потом показать скрытый столбец или строку?
  51.  Как можно узнать, есть ли в таблице скрытые столбцы или строки?
  52.  Как на круговой диаграмме можно вывести текстовые данные, например фамилии сотрудников?
  53.  Как на круговой диаграмме можно вывести числовые данные?
  54.  Как на гистограмме можно подписать числовые данные над каждым столбцом?


 

А также другие работы, которые могут Вас заинтересовать

31410. Дослiдження потенцiалу i напруженностi поля у електричнiй ваннi 149.5 KB
  Мета: Вимiр потенцiалiв i напруженностi поля для заданної конфiгурацiї електродiв. План роботи Зiбрати макет з заданою конфiгурацiє електродiв згiдно малюнка варiанту завдання. Намалювати свою конфiгурацiю електродiв на графiку. Вставити виводи електродiв моделi у кришку згiдно малюнку завдання так щоб електроди опинились у вiдповiдних отворах кришки.
31412. Дослiдження потужностi у системi джерело-навантаження 112 KB
  Джерело живлення та лiнiя постачання моделюются ЕРС та опором RS що вiдповiдає спiльному опору джерела та лiнiї. Занотувати значення опору RS. Вимiряти ЕРС джерела живлення E падiння напруги на опорi джерела US напругу на навантаженнi споживача UL струм кола I для рiзних значень опору RL. Для кращого вiдтворення результатiв вимiрiв доцiльно провести вимiри для усього диапазону змiн опору навантаження вiд мiнiмального до максимального його значення з приблизно рiвномiрним шагом по опору приблизно 10 максимального значення опора RL.
31413. Дослiдження лічильника електроенергії 69 KB
  Визначити залежність швидкості обертання диска лічильника від потужності активного навантаження. Зичайний асінхронний двигун переважно працює в області малих значень коефіциента ковзання тобто в умовах коли швидкість обертання ротора близка до швидкості обертання магнітного поля. Для двополюсного двигуна масимальна швидкість обертання становить 3000 обертів на минуту для частоти мережі 50 Hz 5060=3000. На відміну від звичайного двигуна ротор лічильника працює в області великих значень ковзання тобто швидкість обертання ротора...
31414. Дослiдження схеми напiвпровiдникового випрямляча 83.5 KB
  Серед них найпоширiнiшi схеми: однонапiвпериодного випрямляча; мостового випрямляча; випрямляча з подвоєнням напруги. Пiд час вимiру опора слiд вибрати полярнiсть мультиметру таким чином щоб дiоди випрямляча не шунтировали опiр RL. Якщо вимiри виконуються за допомогою осцилографа замалювати епюри напруги на виходi випрямляча для максимального i середнього значення опорiв навантаження.
31415. Дослiдження елементiв кола змiнного струму – R, L, C за умов 125.5 KB
  Дослiдження RC ланки Зiбрати стенд для одного з варiантiв ємностi C згiдно завдання C=C1 C=C2 C=C1C2 Електрична схема Перемичкою X3 закорочено iндуктивнiсть L точки 23. Вимiряти напругу джерела живлення E точки 111 напругу на опорi UR точки 12 наругу на конденсаторi UC точки 511. Вимiряти напругу джерела живлення E точки 111 напругу на опорi UR точки 12 напругу на iндуктивностi UL точки 24. Вимiряти напругу джерела живлення E точки 111 напругу на опорi UR точки 12 напругу на iндуктивностi UL точки...
31416. Дослідження трансформатора 72.5 KB
  Варiант Первинна обмотка до джерела ЕРС Вторинна обмотка до навнтаження N1 N2 N1 Пермичка X1: точки 1011 або 1012 N2 Пермичка X2: точки 2124 або вiдсутня N2 N1 N2 Пермичка X2: точки 2021 або 2022 N1 Пермичка X1: точки 1114 або вiдсутня Далi по тексту завдання елементи схеми контрольнi точки вимирiв i точки пiдєднання перемичок наводиться для варiанту N1 N2 для варiанту N2 N1 у скобках....
31417. Дослiдження бiполярного транзистора 79.5 KB
  Для розрахунку параметрiв схем для рiзних включень транзiстору спiльний емiтер спiльний колектором спiльна база використовуються сукупностi ВАХ вхiдних та вихiдних характеристик: cукупнсть залежностей струму бази Ib вiд напруги базаемiтер Ube для рiзних значень напруги колектор емiтер Uce сукупнсть залежностей струму колектора Ic вiд напруги колектор емiтер Uce для рiзних значень струму бази Ib. Виставити потрiбний струм бази Ib i вимiряти залежнiсть струму колектора Ic вiд напруги колектор емiтер Uce для цього...
31418. Дослiдження поведiнки транзистора у поширених схемах включення зi спiльним емiтером i cпiльним колектором 70.5 KB
  Для зменшення обсягу вимiрiв та прискорення виконання роботи струми Ib Ic Ic вимiрються не безпосередньо а обчислюються за вiдомими значеннями опорiв Rb Rc Re i вимiряними значеннями падiння напруг URb URc URe. Для дослiжуємого бiполярного транзистору КТ961А npnтипу з коефiцiентом пiдсилення у дiапазонi 10100 доцiльно використовувати такi значення опорiв: Rb =20KΩ; Rc =1KΩ; Re =0 перемичка. Занотувати значення опорiв для дослiджуємої схеми Rb = ; Rc =...