2024
2023
2022
2021
2020
2019
2017
2016
Упражнения по Excel
В процессе подготовки
Данные для упражнений лежат здесь. Поддиректории названы номером упражнения. Можно брать любой из файлов, если их несколько.
1. Вычислительные формулы
- (1) В ячейке A1 лежит целое число x от 2 до 10. Колонку B назовите "номер строки". В ней, начиная с B2, должно содержаться x единиц, потом x двоек, потом x троек и т.д. вплоть до ячейки B100
- (1) В ячейке A1 лежит целое число x от 2 до 10. Например, x = 5. Колонку B назовите "номер столбца". В ней начиная с B2 должно содержаться 1, 2, 3, 4, 5 (если x = 5), потом опять 1, 2, 3, 4, 5 и т.д. вплоть до ячейки B100
(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 в ней. Первая колонка – нуклеотид, вторая – число встреч, третья – процент от общего числа нуклеотидов. (COUNTIF - СЧЁТЕСЛИ, процент от суммы)
- (2) Создайте файл Excel, в котором последовательность вашего белка записана в колонку сверху вниз. Название колонки: my_protein. (номер строки, выбор символа из текста)
(1) Дан файл с последовательностью белка, записанной в один столбец (можно взять свой белок). Составьте таблицу встречаемости 20-и аминокислотных остатков – аналогично заданию 3a.
(2 или 3) Дан файл с последовательностями нескольких белков, записанными в две колонки: первая колонка — идентификатор белка, вторая — аминокислотный остаток. На новом листе составьте таблицу встречаемости аминокислотных остатков в белках. 1-ая строка должна содержать коды 20-и аминокислот, 1-ый столбец — идентификаторы белков, в ячейке — число встреч аминокислотного остатка в белке. (удаление повторов, транспонирование строки, СЧЁТЕСЛИМН). Возьмите либо последовательности трех белков из отдельных файлов (на 2 балла), либо файл sequences.xls с последовательностями всех белков, выданных студентам вашего курса (на 3)
(2) Дана таблица встречаемости аминокислотных остатков в белках (можно взять результат предыдущего задания). Создайте таблицу с процентами встречаемости аминокислот в каждом белке. (суммы, проценты, распространение формул по таблице)
(1) Создайте таблицу хозяев вирусов (hosts) с колонками "хозяин" (или "host") и "число вирусов", для которых указан этот хозяин (или "count")
(1) Создайте таблицу семейств вирусов (subgroups) с колонками "семейство" (или "subgroup") и "число вирусов" в семействе (или "count")
(2) Создайте таблицу, в которой по строкам — семейства вирусов ("subgroup"), по столбцам — хозяева вирусов ("hosts"), а в ячейках указано количество вирусов из такого семейства и с таким хозяином.
(3) Создайте таблицу длин геномов вирусов (Size KB) с колонками "порог длины" (или "size threshold"),"диапазон" (или "range") и "число вирусов" с длиной в диапазоне (или "count"). Пороги выберите такие: 1 (т.е. 1 KB = 1000 нуклеотидов), 2, 4, 8, 16, ..., 2048. Диапазоны обозначьте так: "0-1","1-2", ..., "1024-2048" (Такие диапазоны, т.н. "логарифмическая шкала по Y", предлагается для того, чтобы на одном графике можно было бы изобразить значения, различающиеся на несколько порядков, от единиц до десятков тысяч).
4. Статистические
- (1) Дан файл с набором длин последовательностей всех изоформ белков человека (с одного гена может экспрессироваться несколько изоформ белка в результате альтернативного сплайсинга). Рассчитайте среднюю длину, стандартное отклонение (среднее квадратичное отклонений данных от среднего значения), медиану, минимальное и максимальное значение.
(1) Дано несколько наблюдаемых значений величины. Пример: длины белков. Рассчитайте среднее значение и среднее квадратичное отклонение наблюдаемых значений от среднего по формулам. Отклонением величины x1 от среднего называется разность (x1 - E), где E - среднее арифметическое yнаблюдаетмых величин. Среднее квадратичное трех чисел x1, x2, x3 равно КОРЕНЬ( ( x12 + x22 + x32)/3 ). Сравните с теми же величинами, рассчитанными с помощью Excel, раздел — статистические функции, СРЗНАЧ и СТАНДОТКЛ.
- (2) Дана таблица частот (в процентах) встречаемости аминокислотных остатков в нескольких белках. Для каждого остатка рассчитайте среднюю (по белкам) частоту и стандартное отклонение от среднего.
5. Логические
(1) Дана таблица координат генов в геноме. Первая колонка – первый нуклеотид старт-кодона, вторая колонка – последний нуклеотид стоп-кодона.
Добавьте три колонки: минимальная координата гена, максимальная координата, "ориентация гена": 1, если он закодирован на прямой цепи, –1, если на обратной.
b. (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) Дана последовательность белка в трехбуквенном коде. Создайте последовательность в однобуквенном коде, пользуясь таблицей перекодировки.