Excel实现序列反向互补

最近有需求需要把很多excel里的引物序列反向互补,当然,任何一个编程语言都能解决,可是编个脚本需要一定的时间,而且,跨平台性也不足够好吧。Excel就不一样了,人人电脑里有,天天用,如果能做一些小工具也是很棒的事嘛。Excel也是“万能”的,哈哈。这里,我通过百度,挑战一下Excel的极限,用公式解决。首先声明,公式来源于网络,我只是把两个公式组合了一下。

1.找到了互补序列的生成方法

在下面注明的Excelhome论坛,有用户求助如何获得反向序列,有几个大牛分别拿出了几个公式,我用了其中的第二个,看着相对短点。

8个嵌套的SUBSTITUTE()看着人有点头大,然后每个SUBSTITUTE()分别的功能是把A,T,C,G分别替换为1234,然后再把1234替换为TAGC,好像也不是多复杂,就是有点长。

#第一个先互补
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C")

2.然后反向

反向就是多个可能了,要考虑字符串长度,用了几个IF()判断,后面MID()来取,这个就比较复杂了,我已经看不懂了,你可以吗?我只知道如果是更长的序列只要再后面添加&IF(LEN(D2)<n,””,MID(D2,LEN(D2)-(n-1),1)就行了(来自网站2)。

#第二个反向
=IF(LEN(D2)<1,"",MID(D2,LEN(D2),1))&IF(LEN(D2)<2,"",MID(D2,LEN(D2)-1,1))&IF(LEN(D2)<3,"",MID(D2,LEN(D2)-2,1))&IF(LEN(D2)<4,"",MID(D2,LEN(D2)-3,1))&IF(LEN(D2)<5,"",MID(D2,LEN(D2)-4,1)&IF(LEN(D2)<6,"",MID(D2,LEN(D2)-5,1)&IF(LEN(D2)<7,"",MID(D2,LEN(D2)-6,1)&IF(LEN(D2)<8,"",MID(D2,LEN(D2)-7,1)&IF(LEN(D2)<9,"",MID(D2,LEN(D2)-8,1))))))

3.挑战极限,合二为一

试试把那个单元格替换为第一个公式,下面是见证奇迹的时刻:

=IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))<1,"",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C")),1))&IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))<2,"",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))-1,1))&IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))<3,"",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))-2,1))&IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))<4,"",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))-3,1))&IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))<5,"",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))-4,1)&IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))<6,"",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))-5,1)&IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))<7,"",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))-6,1)&IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))<8,"",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))-7,1)&IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))<9,"",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"A",1),"T",2),"C",3),"G",4),1,"T"),2,"A"),3,"G"),4,"C"))-8,1))))))

欢迎无门槛测试呀!

最后,推荐一个网站Excelhome,第一个公式就是来自这个网站的用户论坛。学好excel,走遍天下都不怕。

参考网站:

1.http://club.excelhome.net/thread-828788-2-1.html

2.https://blog.csdn.net/stereohomology/article/details/34515555

发表评论