На просторах интернета давно нашел вот такой вот замечательный генератор линейных комбинаций (код в файле примере). Работает быстро, результат отличный и все вроде хорошо. Но каждый раз приходиться делать две вещи, во-первых, в первой строке указывать количесвто заполненных ячеек в этом столбце, а во-вторых, каждый раз приходиться открывать модуль с кодом и в коде указывать эти значени в константах m и n, что конечно надоедает изрядно, т.к. макросом пользуюсь часто. Можно ли поправить код и отвязать от этих значений, наверное чтобы эти числа сами высчитывались?
Генератор линейных комбинаций
Функция поиска всех позиций вхождения подстроки в строку, с использованием регулярных выражений.
Изоэлектрическая точка
Как упорядочить порядок групп в двух надстройках на одной вкладке
Извлечь из исходной таблицы столбцы с заданными номерами
|
16.01.2025 13:43:59
Есть таблица, например 4*4, нужно извлечь из нее определенные столбцы, например 1 и 3. Есть функция SelectCols, которая прекрасно это делает, но делает это если ссылаться на ячейки, например так =SelectCols(A2:E6;1;3). Однако если записать таблицу в функцию двумерного массива =Пример_массива_значений() и использовать эту функцию в формуле =SelectCols(Пример_массива_значений();1;3) то ничего не получается. Если это возможно, не мог бы кто-нибудь поправить код функции SelectCols. Пример прилагаю.
|
|
|
|
|
Вернуть заданное число знаков из строки текста, начиная с нескольких заданных позиций, аналог встроенной функции ПСТР (мульти_ПСТР)
|
22.11.2024 10:58:20
Добрый день, уважаемые форумчане!
Не могли бы Вы написать небольшую пользовательскую функцию, аналогичную ПСТР, которая смогла бы возвращать заданное число знаков из строки текста, начиная с нескольких заданных позиций. Грубо говоря, если нужно получить символы из позиций 2,5,10,55,100,120-123, сейчас приходиться применять вот такую громоздкую конструкцию: =ПСТР(A2;2;1)&ПСТР(A2;5;1)&ПСТР(A2;10;1)&ПСТР(A2;55;1)&ПСТР(A2;100;1)&ПСТР(A2;120;3) а хотелось бы: =SelectPosition(A1;2,5,10,55,100,120-123) Или может можно это сделать как-то более просто штатными средствами, может я чего не понимаю. |
|
|
|
|
Перекрывающиеся паттерны в регулярных выражениях
|
07.11.2024 10:45:07
Сталкнулся сразу с двумя проблемами в регулярных выражениях и пока не пойму как мне быть. Возьмем например такую строку FFSLV и попробуем найти в ней паттерн [F]\w{2,3}[VL], то есть подстрока должна начинаться с F, потом два или три символа любые и заканчиваться на V или L. Таким образом, правильный отет должен быть три подстроки: FFSLV, FFSL и FSLV.
И тут начинаются сложности: 1. Если паттерн начинается с той же самой позиции (первого символа F), то регулярное выражение не может вернуть две подстроки с этого символа, т.е. не выходит FFSLV и FFSL, как я ни старался. Вроде в интернете пишут что такого они делать не умеют. 2.Думаю пойду дедовским способом, сначала найду [F]\w{2}[VL] а потом [F]\w{3}[VL] - опять засада, получаются только FFSL и FFSLV. |
|
|
|
|
Генерация случайных, заранее предопределенных букв
|
30.09.2024 15:25:28
Подскажите пожалуйста, как изменить код функции чтобы, чтобы она выбирала между конкретными буквами, например A, C, D, E, F.
|
|||
|
|
|
Как регулярным выражение удалить все после последней двойной косой черты
Импорт текста из интернет страницы в Excel, Парсинг сайта NCBI
|
15.09.2024 13:35:30
Добрый день! Проблема насущная наверное для многих, много информации есть в интернете, но для себя ничего не нашел. Единственное, что давно нашел и чем постоянно пользуюсь, это функция ТЕКСТИЗURL из надстройки VBA-Excel, но в данном случае она не работает. Проблема такая, на реальном примере: есть интернет страница, например с которой нужно получить всю информацию от слова LOCUS до двойной косой черты // и поместить это в одну ячейку экселя. Грубо говоря, я ссылки могу нагенерировать по ID номерам и хотелось бы функцией подобной ТЕКСТИЗURL импортировать данные из NCBI. Неужеле нет простого решения?
|
|
|
|
|
Индекс комплементарности кодонов
|
06.09.2024 19:09:39
Помогите автоматизировать процесс. Есть нуклеотидная последовательность в ячейке J2, нужно провести немного несложных расчетов, представленных в столбцах K, L, M и N, и в столбце N посчитать количество троек. Расчеты не сложные, но хотелось бы все расчеты сделать в одной ячейке, а не в четыре столбца. СверхФормула или пользовательская функция подойдет, но лучше первое - быстрее работает.
|
|
|
|
|
Из двумерного массива извлечь часть значений
|
05.08.2024 12:16:34
Всем привет!
Задача такая: есть двумерный массив, записанный в виде пользовательской функции ГородаИцифры. Как сделать так, чтобы извлечь только список городов, или только список цифр? Другими словами, во что "обернуть" функцию =ГородаИцифры() чтобы получить результаты представленные в ячейках F2 и F3. Возможно ли вообще такое сделать штатными средствами эксель? |
|
|
|
|
Пользовательскую функцию использовать внутри другой пользовательской функции
|
19.06.2024 12:45:40
Добрый день!
Сразу скажу, что в файле примере сразу понятно, что не получается. Но попробую тут объяснить, чтобы было по правилам. С горем пополам записал двумерный массив значений (цифры и буквы) в пользовательскую функцию - такая красота, набрал пару букв, вот тебе из модуля все числа с буквами подтянулись, в виде динамического массива. Далее попробовал эту функцию использовать внутри другой тоже пользовательской функции, но что-то не задалось. В VBA вообще ничего не понимаю, подскажите пожалуйста как сделать так, чтобы все работало. |
|
|
|
|
Альтернативная трасляция нуклеотидной последовательности
|
10.06.2024 10:22:10
Добрый день, всем знатокам и любителям эксель! Прошу помочь переделать пользовательскую функцию перевода нуклеотидной последовательности в аминокислотную (трансляция), добавив в неё пару аргументов.
Sequence - нуклеотидная последовательность. Остальные аргументы не обязательные. StartCodon - кодон с которого начинается трансляция EntryStartCodon - вхождение стартового кодона в последовательность (например 2-ой, 3-й и т.д.), т.е. с него будет начинаться трансляция. Трансляция должна заканчиваться стоп кодоном ("TAA", "TAG", "TGA"), который никаким символом не помечается. Пример прикрепляю, в нем код функции Translation. |
|
|
|
|
Подсчитать количество вхождений подстроки в массиве строк
Найти подстроку в массиве строк и вернуть номер найденной строки
|
03.05.2024 16:24:32
Коллеги, помогите написать формулу, задача такая:
Есть некий набор букв (подстрока) который нужно найти в ячейках-строках (соответственно содержащих больше букв-символов) и вернуть номер строки в которой найдена заданая для поиска подстрока. Формулу не могу придумать, часть формулы слепил (ячейка D10), на вторую часть (с массивом) мозгов не хватает. Помогите пожалуйста. Пример прикрепляю. |
|
|
|
|
ПРОСМОТРХ выдает #Н/Д
|
15.04.2024 15:28:05
Подскажите пожалуйста, как с этим справиться. Задача такая: каждому значению из столбца А нужно дать хеш число. Делаю так: функцией УНИК получаю список уникальных значений, им присваиваю хеш число, а потом ПРОСМОТРХ подтягиваю хеш число обратно к большой таблице. Но ПРОСМОТРХ почему-то нескольким тысячам значений возвращает значение, а остальным пишет #Н/Д. Если провалиться в ячейку и нажать Enter по будет значение (хеш сумма), но так же тысячи раз делать не будешь.
Ссылка на файл-пример: Может есть какое-нибудь средство от этого? Пробовал пересчет всего листа не помогает. |
|
|
|
|
"Сортировка от А до Я" не "работает", Точнее работает только по какому-то определенному количеству первых символов в ячейке
|
15.04.2024 13:19:13
Добрый день, гуру экселя и любители как я!
Столкнулся с такой проблемой - сортирую значения по столбцу А, а они не сортируются. Ущипнул себя, вроде не сплю. Опять сортирую - тоже самое. Файл прикладываю. Отсортированный диапозон должен выглядеть так, как если мы его отсортируем по столбцу С. Даже не знаю что спросить. И долго мне с этой фобией жить? У всех так или у меня эксель уникальный? |
|
|
|
|
Поиск эпитопов в белках
|
02.02.2024 15:10:28
Добрый день глубокоуважаемые гуру эксель, эксперты VBA. Очередной раз прошу Вас о помощи.
Задача такая: нужно найти в аминокислотной последовательности белка, который находится в одной ячейке, все эпитопы - девяти аминокислотные мотивы. В "памяти экселя", аминокислотная последовательность должна разбиваться на петиды по 9-ть аминокислот и каждый из пептидов получает некий Score по матрице. Например, в первой позиции пептида стоит аминокислота "S" имеет вес 2, во второй позиции "I" - 8, в третьей "H" - 0 и т.д. В результатах выводим пептид и через пробел его Score, если пептидов несколько, то через точку с запятой.В идеале, мне видится это в виде функции с тремя аргументами: Sequence - ссылка на ячейку с аминокислотной последовательностью; array - массив матрицы с числами, в данном случае B2:K21; Score - задается пользователем, ниже этого значения пептиды не возвращаются функцией. Это нужно для того, чтобы в геномах патогенов, или опухолевых клетках раковых больных, искать эпитопы и нацеливать на их уничтожение иммуную систему. |
|
|
|
|
Пример использования Excel для биоинформатики, Подготовка аминокислотных последовательностей для филогении
|
19.11.2023 11:59:10
Все привет! Для молекулярной биологии (биоинформатики) есть множество специализиванных программ, например есть такие мощные инструменты как Geneious Prime, CLC bio, DNASTAR и многие другие, пусть и с меньшим функционалом, предназначенные для работы с последовательностями - нуклеотидными или аминокислотными. Здесь, я хочу показать, как можно использовать программу Excel для работы с последовательностями, при этом эксель не только не уступает по мощности или user friendly интерфейсу упомянутым «гигантам», но и даже превосходит их. Отчасти это сравнение неправильное, так как эти программы конкурируют между собой и, по сути, выполняют одни и теже операции (работа с плазмидами, сборка NGS-данных, визуализация геномов и т.д.), в экселе же, можно выполнять те операции, которых в принципе нет в этих программах (работать с массивами, вытаскивание информации по единичным позициям, одновременное глобальное выравнивания множества групп последовательностей и т.д.), т.е. эксель не конкурирует с ними, а только дополняет их. На мой взгляд это как раз то, что не хватает мокрым биологам, которые абсолютно не то, что умеют программировать, а даже пользоваться многочисленными программами, лежащими на GitHub (кнопочек в них нет, да и устанавливаются они как правило на Linux). В качестве примера, попробуем построить филогенетическое дерево (родословную) золотистого стафилококка (Staphylococcus aureus) сиквенс типа 8 (ST8). Схематично этапы расчетов будут выглядеть так: 1) Получаем все гены этих штаммов, переносим их в эксель, удаляем (сортируем) идентичные последовательности. 2) Транслируем (переводим ДНК в белок, т.е. группы по три символа заменяем одним символом) и создаем список уникальных аминокислотных последовательностей. 3) Кластеризация: среди всех генов (пан-геном) необходимо найти только те гены, которые есть в каждом штамме (коровый геном). Кластеризуем последовательности, гомология которых не менее 80%. 4) Выравнивание. В итоге получаем более 600 коровых генов (т.е. 600 кластеров генов), различные по сиквенсу последовательности которых нужно выровнять между собой 5) Возвращаем каждому геному свой выровненный белок. 6) Mask Alignment – аминокислоты, которые одинаковые в каждой позиции одного и того же кластера белков, ненужны для построения филогении, поэтому их можно и нужно удалить, тем самым сократив количество информации в более чем 10 раз. 7) Строим филогенетическое дерево или сеть и наслаждаемся полученным результатом. Теперь немного подробнее каждый шаг: 1) Первый и последний шаг не относятся к экселю. В ГенБанке было скачано 1866 полных геномов золотистого стафилококка, определена принадлежность их к сиквенс-типам и выбрано 363 генома относящихся к ST8. Далее в эксель импортированы все гены каждого штамма (лист DataBase) (почти 973 тыс. генов). Чтобы не хранить весь объем данных и уменьшить его, идентичные последовательности были удалены. Для этого была получена хеш-сумма каждой последовательности, массив сортирован и дубли удалены. Штатная функция «Удалить дубликаты» почему-то не работает на нуклеотидных и аминокислотных последовательностях, может они длинноваты? 2) Из нуклеотидов получаем белковые последовательности. Можно воспользоваться функцией из сообщения 9 от БМВ, за что его отдельная благодарность: Аналогично нуклеотидам, чтобы не утяжелять файл, получаем хеш-сумму и удаляем дубли. Переносим уникальные сиквенсы на новый лист (хотя можно работать и на том же). 3) Кластеризация: среди всех генов (пан-геном) необходимо найти только те гены, которые есть в каждом штамме (коровый геном). Нужно кластеризовать (объединить в группу) последовательности, гомология которых не менее 80%. Самый сложный шаг. Пока пользуюсь онлайн программой: Результаты так себе, но быстро и просто. Как реализовать в экселе не знаю. VBA коды всех так называемых «Меры схожести строк» нашел в интернете. Пожалуй, один из самых быстрых и выдающих хорошее значение, здесь: Вероятно, наиболее близкий алгоритм здесь и кстати мера схожести строк тоже тут шустрая: 4) После кластеризации сиквенсов, оставляем только те кластеры, гены которых присутствуют в каждом геноме, т.е. не менее 363 раз (количество геномов). В итоге получаем более 600 коровых генов (т.е. 600 кластеров генов), различные по сиквенсу последовательности которых нужно выровнять между собой. Выравнивание лучше делать алгоритмом MAFF, код которой блестательно адоптировал doober, за что ему низкий поклон. Здесь ссылка на саму надстройку: А эта ссылка на программу, которую нужно скачать и распаковать на диск С, чтобы путь был такой c:\mafft-win: скачиваем файл mafft-7.520-win64-signed.zip (версия на 18.11.2023, но может быть и более новая версия). При вызове макроса, нужно выделить два столбца: правый столбец - сиквенсы которые нужно выровнять между собой, левый столбец содержит идентификатор, по которому MAFF объединяет последовательности для выравнивания. При этом данные выгружаются в соседний правый столбец относительно этих двух. 5) ВПР или ПРОСМОТРХ возвращаем каждому геному свой выровненный белок. 6) Mask Alignment – аминокислоты, которые одинаковые в каждой позиции одного и того же кластера белков, ненужны для построения филогении, поэтому их нужно удалить, тем самым сократив количество информации в более чем 10 раз. В данном примере если этого не сделать, то все в текстовый файл может не влезть, да и программам, которым в дальнейшем его обрабатывать будет намного сложнее. Великолепный макрос для этого написал Евгений Смирнов: 7) Строим филогенетическое дерево или сеть и наслаждаемся полученным результатом. Возьмем например бесплатную великолепную программу PHYLOViZ, она очень простая и интуитивно понятная: Все конечные файлы со всеми расчетами прикладываю. Файл для программы PHYLOViZ. Архив нужно распаковать, получится папка, после открыть программу PHYLOViZ, File – Open project – папка будет в виде 4-х сцепленных шестеренок – правой кнопкой мыши Load dataset. Исходные файлы для PHYLOViZ, полученные из Excel: Данные в фаста формате: Метаданные: И самое главное – файл Excel:
Если кто-то проделает все это от начала до конца, тот настоящий биоинформатик, можете смело получать диплом! Это лишь один из массы примеров, в котором показано что Excel способен решать задачи по биоинформатике. Придумать можно сколько угодно. Надеюсь, никто не будет смеяться нам моими примитивными пассами над экселем. И вообще, гуру экселя, для нормальных людей написано десятки тысяч макросов, функций и формул, можно что угодны вытворять в нём, даже в игры играть, ну, когда же вы уже наконец обратите внимание на мокрых биологов? Это же, не справедливо
Изменено: - 19.11.2023 12:00:31
|
|
|
|
|
Удалить все одинаковые символы, в каждой позиции в каждой ячейки по столбцу (Mask Alignment)
|
18.11.2023 11:56:16
Добрый день! Помогите пожалуйста выполнить следующее действие: в каждой ячейке одного столбца, поочередно сравнить каждый первый (второй, третий и тд.) символы и если во всех ячейках один и тот-же символ, то удалить его.
Например, имеем две ячейки: A1 "Мама мыла раму" A2 "Мама пила ром-" Остаться должно: A1 "мыау" A2 "пио-" Наверное лучше макрос, которым можно было бы выделить несколько столбцов и он бы по каждому столбцу выполнил аналогичные действия. Пример прикрепляю. Это нужно для уменьшения массива информации для построения филогенетических деревьев. Убираем все одинаковое, оставляем все отличающееся между последовательностями. "Да не оскудеет рука дающего". Евангелие от Матфея, глава 5, стих 7. |
|
|
|
|
Поиск консенсусной последовательности
|
13.11.2023 10:20:24
Добрый утро всем любителям эксель! Помогите решить следующую задачу: необходимо найти наиболее общую (консенсусную) последовательность, то есть нужно сравнить поочередно все буквы в каждой позиции нескольких ячеек и вернуть букву которая наиболее частов стречается в каждой позиции. Пример прилагаю. Если разных букв в какой-либо позиции одинаковое число, как в примере в самом конце, то можно оставить любую из них.
|
|
|
|
|
Наиболее длинная подстрока
Импорт нескольких текстовых файлов в эксель
|
20.10.2023 11:43:27
Добрый день! Помогите пожалуйста подправить макрос. Пытаюсь импортировать содержимое множества текстовых файлов на лист эксель. Нашел на этом сайте замечательный нижепредставленный макрос. Только есть пару проблем:
1. При импорте пропадает первая строка из текстового файла, это главная проблема. 2. Лист должен называться "Цель", можно ли сделать просто активный лист? Если возможно, можно объединить два макроса в один, было бы вообще замечательно.
Изменено: - 20.10.2023 12:05:41
|
|||
|
|
|
Выравнивание аминокислотных последовательностей
|
12.10.2023 16:32:01
Добрый день, форумчане! Помогите с решением проблемы, в которой мне нужно сделать много множественных выравниваний последовательностей (нуклеотидных или аминокислотных). Конечно, есть специализированные для этого программы, но для каждого выравнивания придется создавать свои файлы и каждый раз запускать процедуру выравнивания занова, это ручной способ, и он в данном случае не совсем подходит. В идеале хочется иметь пользовательскую функцию, которая выравнивает последовательности между собой и возвращает выровненные последовательности, как показано в примере в прикрепленном файле. Перерыл весь интернет, таких решений нет. Лучшее что смог найти – это старая надстройка XlibraryDisplay, код который открыт в редакторе Visual Basic. В ней есть какой-то макрос, закодированный на кнопке Align proteins, который впринципе это делает, но в конце его работы он зачем-то разбивает сиквенс по буквам в отдельные ячейки. Можно ли «достать» этот макрос надстройки и переделать под функцию с двумя аргументами: ячейки с последовательностями и метод выравнивания? Ссылка на яндекс диск с надстройкой XlibraryDisplay |
|
|
|
|
Цвет шрифта по словарю_2
|
28.09.2023 13:55:51
Доброго дня всем любителям Экселя! Проблема в следующем: имеется макрос "Цвет шрифта по словарю". Работает он так: на листе "Словарь" в столбец A выписываются нужные слова и раскрашиваются в необходимый пользователю цвет. Потом ячейки в которых нужно раскрасить эти слова выделяются и применяется макрос. Проблема в том, что раскрашивается полько первое вхождение буквы или слова. Не могли бы вы поменят макрос так, чтобы раскрашивались все вхождения?
|
|||
|
|
|
Содержимое каждой ячейки в отдельный txt-файл
|
04.09.2023 17:25:36
Добрый день, знатоки! Помогите настроить макрос под мои нужды. Есть код хорошего макроса, который каждую ячейку столбца сохраняет в отдельный txt-файл, а имя для файла берет с соседнего слева столбца. Пример прикрепляю.
1. Хотелось чтобы сохранялись только те файлы (ячейки), которые выделены, то есть выделяем какие-то (пускай соседние) ячеки столба А и столбца В.2. Можно ли сделать так, чтобы макрос выдавал путь для сохранения файла? В общем нужно сделать из хорошего макроса - отличный! |
|||
|
|
|
Радио в Эксель
|
28.06.2023 16:45:37
Оправдание: Николай Павлов предложил отличное решение, слушать радио из экселя. Некоторые люди писали, что мол зачем слушать радио в экселе, ведь в браузере можно открыть сайт радиостанции и слушать музыку сколько угодно. Так вот, я не совсем согласен, во-первых, сайт радиостанции всегда нужно искать, добавлять страницу в закладки или ещё куда-нибудь, во-вторых, иногда сайт радиостанции, или какой-нибудь интернет-страницы с множеством радиостанций не работает, в-третьих, и самое противное, вездесущая реклама. Включил интернет-радио, послушай сначала рекламу; нажал на паузу, опять плей - опять послушай рекламки. А в эксель радио как в машине - никакой рекламы! Проблема: Так или иначе открываешь эксель и слушай радио себе на здоровье. Но вот беда, точнее маленько раздражающее неудобство, открывать нужно именно файл с радио, так как макросы сохранены в коде страницы, а не в коде модуля. Если же их перенести в код модуля, они перестают работать Вопрос к знатокам: я обычно слушаю одну радиостанцию и нельзя ли сделать так, чтобы ссылку на "потоковое вещание" сразу заложить в код макроса и сделать два макроса - "радио старт" и "радио стоп". Нажал кнопку на ленте надстройки - уши радуются, нажал другую - гробовая тишина, птицы за окном поют. P.S. проигрыватель МедиаПлеер особо не нужен, его можно делит. Пример файла можно скачать отсюда: |
|
|
|
|
Оптимизация кодонов, Нужно написать пользовательскую функцию
|
21.06.2023 11:18:39
Здравствуйте!
Файлы ТехЗадания и конечно же экселя прикладываю. Знания в области биологии не обязательны, вдвоем и так разберемся. Бюджет, это самое сложное, да бог его знает. Если в 1500 уложимся будет здорово. Сразу прошу прощения если что-то не понятно, я в этом давно и мне все понятно. Видит бог я старался.
Изменено: - 21.06.2023 13:15:14
|
|
|
|
|
Подстчитать количество вхождений определенных символов
