Упражнения
Данные для упражнений лежат здесь. Поддиректории названы номером упражнения. Можно брать любой из файлов, если их несколько.
1. Вычислительные формулы
(1) Вычислите сумму первых 20 чисел Фибоначчи: F0 = 0, F1 = 1, Fn = Fn–1 + Fn–2 при n > 1, представьте ее в экпоненциальном формате с одним знаком после запятой. Пример: 1.3E+4. (формула, распространение вниз, экспоненциальный формат)
(2) Постройте график функции Acos(2πnx + φ) на отрезке [0;10] с интервалом 0,01. Параметры A, n, φ должны лежать в отдельных ячейках так, чтобы их было удобно менять. n — целое, A и φ — любые действительные.
Объясните как меняется график при изменении параметров A, n, φ (формула, распространение вниз, график, задание параметров)
2. Работа с текстами
(1) Дан файл со списком студентов. Добавьте первую колонку с идентификаторами вида <Фамилия>_<Имя>.Пример из "иван" "Иванов" надо получить "Иванов_Иван". Следите за прописными и строчными! (начальная прописная, соединить текст)
(1) Дан файл с идентификаторами участков последовательностей белков (доменов — для тех, кто знает это слово). Создайте плоскую таблицу с четырьмя колонками: идентификатор всего белка (имеет вид XXXX_YYYYY), начало участка, конец участка, длина участка. (разбить колонку по разделителям, плоская таблица, длина ранга)
(3) Дан файл со списком штаммов бактерий. Добавьте колонку сокращенных названий видов. Формат видового названия: <Первая буква рода>_<вид>. Например, из "Escherihia coli K12" надо получить "E.coli" (левый символ, найти, длина - многоходовка)
3. Составление таблиц, адреса и ссылки
(1) Дан файл с последовательностью ДНК, записанной в один столбец. На новом листе составьте таблицу встречаемости нуклеотидов A, T, G, C в ней. Первая колонка – нуклеотид, вторая – число встреч, третья – процент от общего числа нуклеотидов. ( счёт если, процент от суммы)
- (2) Создайте файл Excel, в котором последовательность вашего белка записана в колонку сверху вниз. Название колонки: my_protein. (номер строки, выбор символа из текста - как минимум, двухходовка)
(1) Дан файл с последовательностью белка, записанной в один столбец (можно взять свой белок). Составьте таблицу встречаемости 20-и аминокислотных остатков – аналогично заданию 3a.
- (3) Дан файл с последовательностями нескольких белков; 1я колонка - идентификатор белка, вторая - аминокислотный остаток. На новом листе составьте таблицу встречаемости аминокислотных остатков в белках. 1я строка содержит коды 20-и аминокислот, 1й столбец - идентификаторы белков, в ячейке - число встреч аминокислотного остатка в белке. (удаление повторов, транспонирование строки, счёт если множественный).
(2)Дана таблица встречаемости аминокислотнах остатков в белках (можно взять результат предыдущего задания). Создайте таблицу с процентами встречаемости аминокислот в каждом белке. (суммы, проценты, распространение формул по таблице)
4. Статистические
- (1) Дан файл с набором длин последовательностей всех изоформ белков человека (с одного гена может экспрессироваться, т.е. производится, несколько изоформ белка в результате альтернативного сплайсинга). Рассчитайте среднюю длину, стандартное отклонение (среднее квадратичное отклонений данных от среднего значения), медиану, минимальное и максимальное значение.
(1) Дано несколько наблюдаемых значений величины. Пример: длины белков. Рассчитайте среднее значение и среднее квадратичное отклонение наблюдаемых значений от среднего по формулам. Отклонением величины x1 от среднего называется разность (x1 - E), где E - среднее арифметическое yнаблюдаетмых величин. Среднее квадратичное трех чисел x1, x2, x3 равно КОРЕНЬ( ( x12 + x22 + x32)/3 ). Сравните с теми же величинами, рассчитанными с помощью Excel, раздел - статистические функции, СРЗНАЧ и СТАНДОТКЛ.
- (2) Дана таблица частот (в процентах) встречаемости аминокислотных остатков в нескольких белках. Для каждого остатка рассчитайте среднюю частоту и стандартное отклонение от среднего.
5. Логические
(1) Даны координаты генов в геноме в формате: первая колонка – первый нуклеотид старт-кодона, вторая колонка – последний нуклеотид стоп-кодона. Добавьте колонку "ориентация гена": 1, если он закодирован на прямой цепи, –1, если на обратной. (если)
- (1) Дана та же таблица. Добавьте две колонки, в которых координаты гена указаны в порядке: меньшее значение, большее значение. (если)
- (3) Дана та же таблица. Добавьте колонку, в которой группы подряд идущих одинаково ориентированных генов отмечены одним номером. Номера групп идут подряд: 1, 2, 3 и т.д. Допускается группа из одного гена. Пример результата:
gene_start gene_end gene_ori group_no 1 100 +1 1 120 130 +1 1 140 128 -1 2 150 200 +1 3 260 210 -1 4 320 270 -1 4 ......
(если посложнее)
6. ВПР — вертикальный просмотр
- (2) Дана последовательность белка в трехбуквенном коде. Создайте последовательность в однобуквенном коде, пользуясь таблицей перекодировки.
Упражнения
Создайте файл XXXXXXX_pr14_ex.xlsx, XXXXXXX - ваша фамилия латинскими буквами. Каждое упражнение выполняйте на отдельной странице. Страницы переименуйте в ex1, ex2 и т.д.(ex - от exercise)
Для проверки следует поставить ссылку на этот файл с новой веб-страницы Excel.
На основе данных со страницы side_info ведомости создайте плоскую таблицу с колонками:
используйте команду vlookup (ВПР) для перенесения данных из второй таблицы на этой странице; см. 3й рисунок: яблоки заменить на пользовательское имя, цену - на число голосований и все получится!
(*) Файл enzymes.txt содержит сведения о ферментах из систем рестрикции-модификации в геномах бактерий и архей. Создайте сводную таблицу у которой строчки - штаммы бактерий, столбцы - типы ферментов, ячейки содержат число таких ферментов.
Способ 1: с помощью функции countifs()
В колонку A начиная от A2 поместите список штаммов без повторений!
меню Data => Remove duplicates
см. также http://www.planetaexcel.ru/techniques/14/103/
В строку 1, начиная с колонки B поместите список значений в колонке Enzyme
курсор - на ячейкe B1 => правая кнопка мыши => Paste special (специальная вставка) => поставьте галочку transpose (транспонировать) - в нижнем правом углу => Ok
Способ 2: с помощью мастера таблиц
нажмите на круг справа сверху, где сохранение и т.п. => параметры => надстройка => Управление: Надстройки Excel => Перейти => проверьте наличие галочки против Пакета анализа
Меню Вставка => Сводная таблица => На новый лист; появится окошко как здесь http://www.planetaexcel.ru/techniques/8/130/