Учебная страница курса биоинформатики,
год поступления 2022

Семестры Студенты Преподаватели

• 2024
• 2023
• 2021
• 2020
• 2019
• 2018
• 2017
• 2016

• ...

Указания к заданию 8

В приводимых примерах написания функций везде предполагается, что разделитель целой и дробной частей чисел — запятая, а аргументы функций различаются точкой с запятой. В ваших таблицах могут быть другие настройки (соответственно, точка и запятая).

Функция COUNTIFS

Функция COUNTIFS считает строки, в которых значения в заданных столбцах удовлетворяют заданным условиям. Например:

=COUNTIFS(Sheet1!A:A;"Dog";Sheet1!B:B;"Cat")

выдаст число строк листа Sheet1, в которых в столбце A стоит "Dog", а в столбце B — "Cat".

В качестве условий можно писать равенства, неравенства и другие выражения, при этом всё выражение берётся в кавычки, например:

=COUNTIFS(Sheet1!A:A;"Dog";Sheet1!C:C;">=5")

В качестве условий можно указывать значения ячеек, например:

=COUNTIFS(Sheet1!A:A;"Dog";Sheet1!B:B;A4)

(в данном случае будет взято значение ячейки A4 с того листа, на котором написана формула).

Наконец, можно писать выражения, включающие значения ячеек. При этом имя ячейки выносится из кавычек, и перед ним ставится амперсанд "&":

=COUNTIFS(Sheet1!A:A;"Dog";Sheet1!C:C;">="&B4)

Функцию COUNTIFS удобно использовать для создания гистограмм.

Специальная вставка

Часто бывает, что вы посчитали что-то с помощью некоторых формул, и вам нужны результаты вычислений (например, на другом листе или даже в другой книге), а сами формулы уже не нужны. В этом случае стоит воспользоваться специальной вставкой: берёте в буфер так же (<Ctrl>+C), а выкладываете в новое место комбинацией <Ctrl>+<Shift>+V. Другой способ: щёлкнуть по нужному месту правой кнопкой мыши, выбрать "Paste special" и затем нужный вариант (чаще всего это "Values only").

Вставка диаграммы или графика

Выделите область, содержащую значения, которые хочется изобразить графически (например, значения затабулированной функции или числа, составляющие гистограмму). В меню выберите Insert → Diagram. Дальше действуйте по смыслу, в частности, стоит попытаться выбрать правильные подписи оси X. Не отчаивайтесь, если сразу не получается, рано или поздно получится.

Формула трапеций

Суть формулы трапеций в том, что на отрезке интегрирования (скажем, [a,b]) расставляются точки x₀=a, x₁, x₂,...,x_n=b и интеграл заменяется суммой площадей трапеций с вершинами (x_i,0), (x_i+1, 0), (x_i,y_i), (x_i+1, y_i+1), где y_i — значение функции в точке x_i (см., например, в Википедии). Если функция затабулирована и, например, в ячейках A2:A100 находятся (упорядоченные по возрастанию!) значения X, а в ячейках B2:B100 — значения функции от X, то для подсчёта интеграла нужно написать (например, в ячейке C2) формулу =(A3-A2)*(B2+B3)/2 и распространить её на ячейки C2 – C99. После этого надо сложить значения в этих ячейках (функция SUM).

Описательная статистика

Описательная статистика — это числовые характеристики большого массива чисел. К ним относятся:

• среднее — функция AVERAGE
• среднее квадратичное отклонение — функция STDEV.P
• медиана — MEDIAN
• минимум и максимум — MIN и MAX

и некоторые другие. Для зачёта нашего упражнения достаточно этих.

Рекомендуется расположить названия характеристик в столбце A, а рядом в столбце B формулы, считающие значения соответствующих характеристик.

Статистическая значимость

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

В случае сравнения двух чисел можно применить простейший из статистических критериев — критерий знаков. Вероятностной моделью служит "бросание монетки" столько раз, сколько имеется наблюдений. Для оценки статистической значимости считаем вероятность того, что при бросании монетки с равной вероятностями орла и решки мы получим такую же или бо́льшую разницу между двумя исходами, по сравнению с имеющимися числами. Такая вероятность считается по биномиальному распределению с вероятностью успеха 1/2, функция BINOM.DIST.

=2*BINOM.DIST(MIN(B1;B2);B1+B2;0,5;TRUE)

В данном примере предполагается, что одно число помещено (или получается формулой) в ячейке B1, а второе — в ячейке B2.

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

Вероятность, полученную функцией BINOM.DIST, необходимо умножить на два, поскольку заранее не известно, какое из чисел окажется больше.

Вертикальный просмотр

Пример функции VLOOKUP:

=VLOOKUP("Cat";Sheet1!A:D;4;FALSE)

В результате в столбце A листа "Sheet1" будет найдена ячейка, содержащая значение "Cat", а результатом функции будет значение ячейки, находящееся на листе Sheet1 в той же строке, что найденная ячейка, но в столбце D. Здесь:

• Sheet1!A:D — тот диапазон, в первом столбце которого следует искать "Cat".

• 4 — столбец, из которого брать результат (D — четвёртый, если считать от A). В предыдущем (втором) аргументе должен обязательно присутствовать тот столбец, номер которого указан в третьем аргументе!
• FALSE — аргумент, без которого функция будет (в большинстве ситуаций) выдавать неправильные значения. Не пропускайте его!

Если значение в столбце Sheet1!A найдено не будет, функция выдаст специальное значение ошибки, которое выглядит как #N/A.

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