1. Извлечение названий родов и видов организмов.
Для того чтобы извлечь нужные данные из ячеек столбца Organism Name, я решила использовать общую формулу, которая учитывала бы
все возможные условия и работала бы для любого названия организма. Сперва я перечислила условия, при выполнении которых в соответствующую ячейку столбца Genus проставлялся прочерк:
- Первый символ является кавычкой
ПСТР(G2;1;1)="'"
- Первый символ является скобкой
ПСТР(G2;1;1)="["
- Первый символ — не заглавная буква
НЕ(СОВПАД(ПСТР(G195;1;1);ПРОПИСН(ПСТР(G195;1;1)))
В случае же, когда у нас не было таких проблем, возможны два варианта: либо у нас обычное название, либо Candidatus, с которым нужно работать иначе.
Проверка на то, что первое слово — Candidatus:
ПСТР(G195;1;ПОИСК(" ";G195)-1)="Candidatus"
Если у нас было обычное название рода, то нам нужно взять его. Тогда нам нужно взять все символы строки, начиная с первого и заканчивая перед тем символом, который является первым вхождением пробела.
ПСТР(G195;1;ПОИСК(" ";G195)-1)
Candidatus же, по условию, не является названием рода, вместо него нужно взять второе слово в строке с названием организма. Тогда мы должны взять все символы строки с 12-ого (ибо длина слова Candidatus — 10 симолов) и до второго пробела.
ПСТР(G195;12;ПОИСК(" ";G195;ПОИСК(" ";G195)+1)-12)
Итак, род мы извлекли. Вид можно извлекать разными способами (и, наверное, более тривиальными, чем у меня), но я всё же опишу свой. Если род не был определен (стоял прочерк), то и в соответствующую ячейку
столбца Species записывается прочерк.
Если название рода было обычным (не Candidatus), то нам нужно было взять второе слово из названия организма. Таким образом, нам нужно было взять все символы, начиная с символа после первого вхождения пробела и заканчивая символом перед вторым его вхождением. Здесь необходимо учесть то, что,
вообще говоря, второго пробела может и не быть. Поэтому сконкатенируем строку названия организма с пробелом и будем искать в ней.
ПСТР(G2;ПОИСК(" ";G2)+1;ПОИСК(" ";G2&" ";ПОИСК(" ";G2)+1)-ПОИСК(" ";G2)-1)
В случае Candidatus требуется взять уже третье слово. Значит, мы должны взять все символы строки, начиная с символа после второго вхождения пробела и до третьего его вхождения. Как и в предыдущем случае,
третьего вхождения может и не быть, поэтому опять сконкатенируем строку названия с пробелом.
Начало поиска — второе вхождение пробела + 1:
ПОИСК(" ";G2;ПОИСК(" ";G2)+1)+1
Вторым аргументом ПСТР() должна быть длина подстроки. Она определяется как конец - начало + 1. Начало мы определили. Конец (третье вхождение пробела):
ПОИСК(" ";G2&" ";ПОИСК(" ";G2;ПОИСК(" ";G2)+1)+1)
Значит, длина подстроки (названия вида):
ПОИСК(" ";G2&" ";ПОИСК(" ";G2;ПОИСК(" ";G2)+1)+1)-ПОИСК(" ";G2;ПОИСК(" ";G2)+1)-1
В итоге:
ПСТР(G2;ПОИСК(" ";G2;ПОИСК(" ";G2)+1)+1;ПОИСК(" ";G2&" ";ПОИСК(" ";G2;ПОИСК(" ";G2)+1)+1)-ПОИСК(" ";G2;ПОИСК(" ";G2)+1)-1)
2. Создание сводной таблицы "genera".
Сперва я создала новый лист под названием "genera", после чего ипользовала Вставка->Сводная таблица. Источником информации для сводной таблицы
я выбрала таблицу с листа plasmids. Поля для сводной таблицы — ID и Genus. При этом я сделала именно Количество по полю ID, которое и является количеством плазмид в соответствущем роде из стоблца Genus.